CODEWITHSUNDEEP
.netsql-serverado.net
kd.
kd.

Reputation: 292

long running database operations in .net

I have a delimited file that I use to insert/update records in a sql server table via a .net application. The file has about 80000 records and is processed daily. My question: is it safe or even sensible to keep the connection to the db open while i spin through each of the 80000 rows or should I be closing the connection and reopening with each iteration of the loop? That sounds cumbersome in itself. However, I am concerned about holding an open connection for a long time, holding locks and using up memory unnecessarily. What would be a more scalable, safe and sensible way to do this?

Upvotes: 2

Views: 293

Answers (2)

granadaCoder
granadaCoder

Reputation: 27862

I once had a need to import data. But I had to run some mini business rules on it. Also in my requirement was to import as many rows as possible, but if anything failed, log it (but don't fail the entire import).

I wrote the sample below.

http://granadacoder.wordpress.com/2009/01/27/bulk-insert-example-using-an-idatareader-to-strong-dataset-to-sql-server-xml/

I pass down N number of records (N = 1000 for example) as ~xml to a stored procedure.

N should be configurable, to find a "sweet spot". But one at a time is too slow, and 80,000 at one time seems to many. 1,000 (rows) x 80 "runs".... is a good starting point, IMHO.

So if your import is "dumb", then the previously suggested "SQLBulkCopy" may be the best way. But if you have any checking or validation, then my sample might be a good suggestion.

.......

Another option:

http://msdn.microsoft.com/en-us/library/ms162802.aspx bcp.exe

But that isn't really "dot net code".

Upvotes: 2

RBarryYoung
RBarryYoung

Reputation: 56735

First, no you should not open/close the connection every row. For 80,000 rows, that will take forever and will just add to the overhead. You could consider batching the rows (reset the connection say every 10-500 rows). Fortunately, there is a better option:

Secondly, the proper way to insert/update that many rows into a database from a .Net application, is to use the SQLBulkCopy methods, and not the INSERT or UPDATE commands. You should use SQLBulkCopy to load the data rows into a holding/staging table, and then use a SQL Stored Procedure to do the Insert/Update to the actual table(s), en-mass.

If you are concerned about the sustained load of the SQLBulkCopy, it has batching options built-in.

Using this technique, the initial upload of data should be at least 5x faster, and the actual table Insert/Updates should only be a matter of seconds.

Upvotes: 4

Related Questions