A large number of UPDATE queries slowing down the page

Question

I read and check large text files with a fixed width (range from 10-50K lines) that are sent through our ASP.net website (encoded in VB.Net). I am doing an initial file scan to check for basic problems (line length, etc.). Then I import each row into the MS SQL table. Each DB row consists mainly of record_ID (Primary, auto-incrementing) and about 50 varchar fields.

After the insertion was completed, I launched the check function in the file, which checks each field in each line based on a set of criteria (cropped length, numerical value, range check, etc.). If it detects an error in any field, it inserts an entry into the error table, which has error_ID, record_ID and an error message. In addition, if the field does not work in a certain way, I must do a “reset” in this field. A reset can consist of blanking the entire field or simply replacing the value with another value (for example, replacing a string with a new one, in which all illegal characters are displayed).

I have a test file with 5000 lines. Loading, initial inspection and import takes about 5-6 seconds. Detailed error checking and inserting into the "Errors" table takes about 5-8 seconds (there are about 1200 errors in this file). However, the “reset” part takes about 40-45 seconds for 750 fields that should be reset. When I comment out the function resets (returns immediately without actually calling the stored procedure UPDATE), the process is very fast. When reset is enabled, pages return for 50 seconds.

My UPDATE stored proc uses some recommended code from http://sommarskog.se/dynamic_sql.html , in which it uses CASE instead of dynamic SQL:

UPDATE dbo.Records SET dbo.Records.file_ID = CASE @field_name WHEN 'file_ID' THEN @field_value ELSE file_ID END, . . (all 50 varchar field CASE statements here) . WHERE dbo.Records.record_ID = @record_ID 

Is there any way to help here. Can I somehow combine all these UPDATE calls into a single transaction? Do I have to rework the UPDATE query somehow? Or is it just a huge amount of 750+ UPDATE, and everything is just slow (this is a server with four processors with 8 GB of RAM).

Any suggestions appreciated.

Answer 1

Do not do this in sql; Correct the data in the code and then update.

If you have sql 2008, take a look at the table options. It allows you to pass the entire table as a parameter to s'proc. With their help, you have only one insert / update or merge statement

Answer 2

If you iterate over rows and do separate updates / inserts, it can be very expensive ... Think of it using SqlBulkCopy , which can speed up all your inserts. Similarly, you can create a DataSet , make updates to the data set and then send them with just one shot through the SqlDataAdapter .

Answer 3

I believe that you make 50 case statements with each update. It looks like it will be slow.

You can solve this problem with incremental code through parameterized queries and a string constant table.

A quick and dirty code example.

 string [] queryList = { "UPDATE records SET col1 = {val} WHERE ID={key}", "UPDATE records SET col2 = {val} WHERE ID={key}", "UPDATE records SET col3 = {val} WHERE ID={key}", ... "UPDATE records SET col50 = {val} WHERE ID={key}"} 

Then in your SQL call, you simply select the element in the array corresponding to the col you want to update, and set the value and key for the parameterized elements.

I assume that you will see a significant improvement ... let me know how this happens.

Answer 4

Um. Why are you inserting numerical data into VARCHAR fields and then trying to perform numerical checks? This is ominous.

Apply the correct data setting and restrictions to your table, do INSERT and see if it worked. SQL Server will be happy to report errors.

Answer 5

I would try changing the recovery model to a simple one and look at my indexes. Kimberly Tripp made a session demonstrating a script with improved performance using a bunch.