Large records insertion

Hi, I'm trying to add large no. of records into a Informix DB using odbc and RDO/RDC. I have to add a few hundred thousand record in a batch processing. My problem is, after adding a few thousand records the memory run's out. I have tried to close and open the RDC connection but it did not work. My coding looks like this

Do While FILELOOP < MaxLoop
If (TransLoop = 0) Then
End If
    TransLoop = TransLoop + 1

If (TransLoop = 1000) Then
      RDC_Real.SQL = "select * from table"
TransLoop = 0
End If
Is there any better way to do this and how to  "Flush" the memory so that it won't run out?        
Who is Participating?
mayhewConnect With a Mentor Commented:
You're committing your transactions every 1000 rows so that's probably not the problem.

I think that the problem is that you are reading the whole table in so that you can insert more rows into it.  The more that you insert, the larger that your recordset becomes.

Instead of reading in the whole table and doing an .addnew, why not just use an .execute command and use straight SQL to insert your data?

I realize that this will incur more network overhead, but that's got to be better than reading in your whole table.

Another alternative is to read in just one record to populate your recordset.  That way you're not reading in a continually growing table into your recordset, but rather always just the first one.  That should speed things up quite a bit.

Let me know if that works for you.

  The reason for this is that you are using transactions, which are stored locally. A possibly better way to do this would be to put all the data in a temporary table, and then SELECT INTO the real data table.

Ben Stiglitz
Ebel USA, Inc./KEScom
rafiq99Author Commented:
Thankx guy's for ur responses.
mayhew, I have tried .execute b4, but it was just to slow for me. You also said about
another alternative to read in just one record to populate your recordset.  Can u elaborate about it or give an example on how to use them?

ebelusa I'll try ur suggestion.

Thankx again......


In your example, after committing the transaction, you repopulate your recordset like:

RDC_Real.SQL = "select * from table"

This reads your entire table into the recordset including any records that have already been inserted by your code!

Instead, try just reading in the first record from the table like:

RDC_Real.SQL = "select * from table where ID = 1"

In this example, ID is a primary key so this query will only bring back one row.

This change should speed things up greatly!  :)

Let me know.

rafiq99Author Commented:
Hi,  mayhew
I have used execute (like u said) to store data into temp. db (as suggested by  ebelusa) and finaly used SELECT INTO to store the data using odbc into informix.
The result was a lot faster and used small number of memory + no memory leakage..

Thanx alot  guy's......
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.