Re: Q: SQL Load vs. Embedded SQL (Pro C) for Updating Large Table
Date: 1996/03/12
Message-ID: <4i334e$cpe_at_news.nyc.pipeline.com>#1/1
David Di Biaggio <dibiaggio_at_iquest.net> wrote:
>Terry Felton wrote:
>>
>> We have a need to move about 1 million records from an unindexed
>> table into another table each day. The second table contains 15 - 30
>> million records. We have had to drop the indexes from the first table in
>> order to keep up loading it using a Pro C application, but know that
>> we've just shifted the index delay to when we move the the data into the
>> indexed table.
>>
>> What are the advantages of SQL Load in this situation? Does it
>> completely lock the receiving table during the load? Is the reindexing
>> significently faster that using embedded SQL? What are the pitfalls of
>> using SQL Load? Might there be other options to consider?
>Have you tried using the 'direct' path of loading? To do so, use
>direct=true on your sqlload command line. This method is considerably
>faster than non-direct. There are some pitfalls, if the data you're
>loading is guaranteed to match all your constraints you should be fine.
>If it doesn't the direct path is not for you....direct path sort of
>ignores all constraints and just "dumps" the data into your table. If
>constraints are violated, I've see the load lock the table up .... good
>luck!
When I try to load a million recs from file into table using direct load, I get READBUFFERS error (forgot the error number but something weird mentioning 2-task, index etc when I have no such things on the table). The essense of the error, I took it as insufficient read buffers. Default is 4. I kept on increasing the number and made it as high as 256, the error persists.
Note that if I split the million rec file into 4 files of 250,000 each, the same program works.
If performance wouldn't have been so good with direct load, I would have stopped using it. But it's just too fast. So have to use it.
Sameer.
utrankar_at_nyc.pipline.com (Sameer Utrankar) All of us can't be heros. Someone has to sit by the curb and clap as they go by. Received on Tue Mar 12 1996 - 00:00:00 CET