Bulk Imports in pervasive db "refresh files"

Posted on 2006-04-12
Last Modified: 2013-12-25
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?
Question by:tags266
    LVL 18

    Expert Comment

    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.  
    LVL 28

    Expert Comment

    by:Bill Bach
    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.

    Author Comment

    hey mirtheil...the answers to your questions:

    1) SQL/ADO
    2) 8.6
    3) fixed format text file

    Author Comment

    I like your answer so far BillBach.  I'm going to try a couple things out and get back..thanks.
    LVL 28

    Accepted Solution

    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.
    LVL 18

    Assisted Solution

    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.  

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now