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?

Thanks.

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.
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Austin: Pervasive Service & Support Class - 05/2006 ***
*** Chicago: Pervasive Service & Support Class - 07/2006 ***

mbosco51 (AT) hotmail (DOT) com wrote:

There is one other option that is available and can be used with
either / both options Bill Bach already mentioned.

Throught the Btrieve API you can open a file in "accelerated mode".
In accelerated mode the database engine does not do any transaction
logging on the file. Since you are building the file from scratch if
something were to go wrong the process could just be started over.

In fact even if you do your inserts via SQL you may get large gains by
using the Pervasive Function Executor to open the file in accelerated
mode ahead of time and hold it open. For this to work the accelerated
open call must be the first open call made for the specific file.

That in conjunction with creating a file without any keys/indexes
before loading the data and putting the keys/indexes back on the file
after the data is loaded can speed things up tremendously.

Leonard

On 12 Apr 2006 13:29:58 -0700, mbosco51 (AT) hotmail (DOT) com wrote: