April 6, 2004 at 5:53 am
Hi,
I have written an application using VB with a SQL 2000 database.
This application is used to import data into various Table in the database. In total 10 Tables are involved. 9 out of these 10 imports works OK as the number of records involved is minimal as so is the associated processing.
However, I have one large import which deals with Orders. The number of Orders involved is 165,000. Each Order needs a fair amount of work done on it to ensure data is valid etc. The data is held in a text file. I have used the DTS Wizard to get the raw data into a Working Table. I then read a row at a time into a VB Class, perform my data validation etc and then Insert into the Orders Table. The process starts off at a good speed but starts to tail off after about 30%. Its looking like it will take 4 hours in total to process the 165,000 records.
Can anyone give me some ideas as to why the process is slowing down? The Orders table links to other tables, all relationships have indexes. The main Orders Table has a clustered index set. The Indexes are set to check for data integrity on load. Would these be slowing things down.
Is there a performance tracking tool that I can use to monitor performance and see if there is a bottleneck somewhere?
For each Order I do three other Selects and a couple of deletions. It might be that the sheer amount of processing it is doing is causing the delay. I don't really know what speed it should be running at. Maybe it is OK?
Any help or tips would be much appreciated.
Thanks.
April 6, 2004 at 7:04 am
If you are attempting to insert data into a table with a clustered index you have to bear in mind that the data has to be moved about a lot to maintain the integrity of the index. I would recommend dropping the clustered index before the insert and then creating it again afterwards, you should see a noticeable improvement in the performance.
April 6, 2004 at 8:54 am
Why are you processing this row-by-row within VB?
Much better to perform the validation in SQL, using set-oriented queries. The performance should be massively better.
When validation is complete, one insert statement will put the validated data into your destination table.
What would you rather do - 165,000 operations or 1 operation? Seems like a simple choice to me.
Rob
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply