Huge load in SQL Server 2000

[capooti]

Hi All

I have the following trouble: I wrote a procedure (VB6, ADO) to load a denormalized huge text file in a normalized SQL Server database. From over 1.5 million records in the text file needs to be created almost 25 millions records in SQL Server. Unluckly we exstimated that the procedure would require, in order to be completed, about 15 days!!! And we need to run it every month!!!
Anyone can kindly give me some opinion about reducing loading time? Consider that the update queries in the VB procedure are optimized. I wouldn't need the log, but is there a way not to use it? I think this would improve performance. Thanks in advance

 

[tlbroadbent]

Chack out the following threads.

thread183-234101
thread183-202349

In them, I explained why you can't totally turn off logging and also provide a recommendation for speeding update processes and preventing a huge transaction log.

Usually load processes in VB are slow because they insert one record at a time. This may not be the case for your process. Wherever possible run bulk loads and inserts when loading data. At the very least, send a batch with mutiple inserts to SQL server. If you can create a SQL Statemetn with 10 or more inserts, you'll significantly speed the process because you reduce round trips to the server.

Did you look into the possibility of using DTS for the transformation and load? It is quite powerful and can make use of VB components. Check faq183-962 for links to some DTS resources. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[capooti]

Thank you Terry for your excellent reply, You gave me precious suggestions and links.
My VB process insert ONE record at a time. What I will do now in order to improve performance is (like you suggest me):
1) try with bytch update
2) eventually try DTS (unluckly I am quite new to it)

Best regards

 

[capooti]

I solved with something that already knew but I would'nt have thought is so effective.
With ADO, update operations can be very slow, the best is to code them in stored procedure and call them from the ADO client.
What impressed me is that this way the perfomance are now 15 times better!
Now I am going to learn deeply DTS, maybe it will be even better!