Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Large records insertion

Posted on 1999-07-28
5
Medium Priority
?
266 Views
Last Modified: 2013-12-25
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
    RDC_Real.BeginTrans
End If
    TransLoop = TransLoop + 1
       
RDC_Real.Resultset.AddNew
…..
RDC_Real.Resultset.update

If (TransLoop = 1000) Then
     RDC_Real.CommitTrans
      RDC_Real.Resultset.Close
      RDC_Real.Refresh
      RDC_Real.SQL = "select * from table"
      RDC_Real.Refresh
TransLoop = 0
End If
       
Loop
Is there any better way to do this and how to  "Flush" the memory so that it won't run out?        
 
0
Comment
Question by:rafiq99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 

Expert Comment

by:ebelusa
ID: 1507238
rafiq99:
  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
0
 
LVL 5

Accepted Solution

by:
mayhew earned 600 total points
ID: 1507239
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.


0
 

Author Comment

by:rafiq99
ID: 1507240
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......

0
 
LVL 5

Expert Comment

by:mayhew
ID: 1507241
Sure,

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

RDC_Real.SQL = "select * from table"
RDC_Real.Refresh


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"
RDC_Real.Refresh


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.

0
 

Author Comment

by:rafiq99
ID: 1507242
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......
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question