How can I efficiently manipulate 500k records in SQL Server 2005?

Question

I am getting a large text file of updated information from a customer that contains updates for 500,000 users. However, as I am processing this file, I often am running into SQL Server timeout errors.

Here's the process I follow in my VB application that processes the data (in general):

1. Delete all records from temporary table (to remove last month's data) (eg. DELETE * FROM tempTable)
2. Rip text file into the temp table
3. Fill in extra information into the temp table, such as their organization_id, their user_id, group_code, etc.
4. Update the data in the real tables based on the data computed in the temp table

The problem is that I often run commands like UPDATE tempTable SET user_id = (SELECT user_id FROM myUsers WHERE external_id = tempTable.external_id) and these commands frequently time out. I have tried bumping the timeouts up to as far as 10 minutes, but they still fail. Now, I realize that 500k rows is no small number of rows to manipulate, but I would think that a database purported to be able to handle millions and millions of rows should be able to cope with 500k pretty easily. Am I doing something wrong with how I am going about processing this data?

Please help. Any and all suggestions welcome.

Answer 1

There are more efficient ways of importing large blocks of data. Look in SQL Books Online under BCP (Bulk Copy Protocol.)

Answer 2

Needs more information. I am manipulating 3-4 million rows in a 150 million row table regularly and I am NOT thinking this is a lot of data. I have a "products" table that contains about 8 million entries - includign full text search. No problems either.

Can you just elaborte on your hardware? I assume "normal desktop PC" or "low end server", both with absolutely non-optimal disc layout, and thus tons of IO problems - on updates.

Answer 3

Are you indexing your temp table after importing the data?

temp_table.external_id should definitely have an index since it is in the where clause.

Answer 4

subqueries like the one you give us in the question:

UPDATE tempTable SET user_id = (SELECT user_id FROM myUsers WHERE external_id = tempTable.external_id) 

are only good on one row at a time, so you must be looping. Think set based:

UPDATE t
    SET user_id = u.user_id
    FROM tempTable          t
        inner join myUsers  u ON t.external_id=u.external_id

and remove your loops, this will update all rows in one statement and be significantly faster!

Answer 5

Make sure you have indexes on your tables that you are doing the selects from. In your example UPDATE command, you select the user_id from the myUsers table. Do you have an index with the user_id column on the myUsers table? The downside of indexes is that they increase time for inserts/updates. Make sure you don't have indexes on the tables you are trying to update. If the tables you are trying to update do have indexes, consider dropping them and then rebuilding them after your import.

Finally, run your queries in SQL Server Management Studio and have a look at the execution plan to see how the query is being executed. Look for things like table scans to see where you might be able to optimize.

Answer 6

Look at the KM's answer and don't forget about indexes and primary keys.