SQL loader performance

From: Neal Nachtigall <neal.nachtigall_at_lodgenet.com>
Date: 1998/04/02
Message-ID: <6g0ego$rfg$1@server2.dakota.net>#1/1

We have a situation with one of our instances in which nightly loads are taking an extremely long time. The cause of the long times is obvious in that we are loading (SQL loader/conventional path) .5M records/night into a table that already has 35M+ rows, and to extend the time we also must maintain the uniqueness constraint to avoid duplicate records, BUT we would still like to improve the performance as much as possible.

My questions are:

    What are some various ways in improving this? what would be the best?

     Various ways we have thought off include:
           1)  SQLLoader/conventional path into populated table


                  - this is what we are doing


            2) SQLLoader/conventional path into empty table then copy to
populated table
                 - this would speed the load time but the appending to the
populated table
                    will still probably take forever since we still need to
check for records already
                     existing (with the constraint)

         I have further questions about the next options:
             3) SQLLoader/direct path into populated table


                  - this would be quicker but if we understand direct path

correctly the constraints
                    are enforced once the load is complete and not during
the load thus would
                     the performance gain be nullified by the time it took
to validate the uniqueness
                     constraint and remove dupes????

              4) SQLLoader/direct path into empty table then copy to
populated table


                  -   same comments as 2


               5) Home grown tool


                  - Using ProC or something (but why reinvent the wheel)


                6) Other tools such as Smart DB or something else


                  - we don't currently own


        Obviously this is not a new problem, DWs must deal with this all the

time. Any suggestions would be greatly appreciated.

Neal L. Nachtigall
Lead Database Analyst

--
LodgeNet Entertainment Inc.                       phn (605) 988 - 1642
3900 W. Innovation St.
neal.nachtigall_at_lodgenet.com
Sioux Falls, SD  57107-7002                        fax (605) 988 - 1428

Received on Thu Apr 02 1998 - 00:00:00 CST