Bulk Imports in pervasive db "refresh files"

Hi. I have a table in a pervasive database that contains 800,000 records. Every so often a "refresh" file will come in. We have to wipe out the table and load it with the new 800,000 records from the file. In Microsoft SQL (which I am more familiar with) I would do this pretty easily in a DTS package. What is the best way to do this in pervasive? I wrote a quick VB app that reads the file and inserts the record one by one, but it is going to take days to go through the whole file. Is there some sort of bulk import available?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

A few things:
1. What interface are you using in VB?  If it's SQL/ODBC, make sure you use parameterized queries.
2. What version of PSQL are you using?  
3. What format is the "refresh" file in?

You might also look into the Pervasive Integration products.  
Bill BachPresident and Btrieve GuruCommented:
The quickest way to wipe out the table is to recreate it in-place.  You can do this from the OS layer (by copying in an empty file created with BUTIL -CLONE) or from the Btrieve API layer with a CREATE call.

For inserting the records, a Btrieve application running ON THE SERVER will provide the best performance if you are inserting one record at a time.  This eliminates the network overhead for each record, and it runs many times faster.

You can gain some performance by trying two additional techniques:

1) Use InsertExtended calls instead of Insert calls.  InsertExtended allows you to build a large buffer (up to around 60K at a time) of records and insert them all at the same time, with one call. Internally, the database has to do the same amount of work, but this eliminates some additional calling overhead.  If you cannot run the import process on the server, this is the next best thing.

2) When you create the new file, create it with NO keys.  Then, do your bulk insert as mentioned above.  When done, go create the keys.  While this CAN be a problem if you need to filter duplicate records, if the data is known to be "good", then the inserts with no keys will be MUCH faster, and adding the keys after the fact will save a lot of time.
tags266Author Commented:
hey mirtheil...the answers to your questions:

2) 8.6
3) fixed format text file
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tags266Author Commented:
I like your answer so far BillBach.  I'm going to try a couple things out and get back..thanks.
Bill BachPresident and Btrieve GuruCommented:
If you are accessing the database through ADO, then I'm not sure you can leverage the InsertExtended call, so this option may be unavailable to you.  I checked, and I also believe that the ActiveX controls do NOT support InsertExtended.  Since codiung direct to the Btrieve API is more complicated than ADO, you might be limited in what you can accomplish.  Please be aware that native access is MANY TIMES faster than SQL access via ADO.  The time invested in writing a native API app will be well worthwhile.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The only way to approximate an InsertExtended with ADO would be parameterized queries.  It's not the same and won't give the same performance as a Btrieve API program.  Bill's right, to get the best performance, you're going to want to write a Btrieve API program.  
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

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.