25 07 2009

So, (apparently starting a sentence with “so” is rather weird and is a characteristic of M$ employees!) when you want to READ data from an SQL database, you can just as well write your own SQL query string in plain-text on the C# side and then just ExecuteReader. It creates a reader object that can retrieve data for you sequentially in one direction – forward (which is mostly what you want anyway… ). This is rather fast too.

BUT, lets say you want to PUT data into the database. The standard way of doing this is to use INSERT statements. Since SQL 2008, the insert statement supports multiple inserts in a single statement

i.e. you can say INSERT()()()(); and it will put in multiple records. But internally, apparently it still splits it up. But hey, its one query!

Another way to do this is to use SqlBulkCopy (search for class description etc on msdn…) Basically what you do is that you frame your own DataTable with all the data you want to put in and then type-cast it into whatever BulkCopy wants and then write to database. Now, SqlBulkCopy also splits up the entire operation into chunks. There is a parameter that you can set which will do this for you. One way to copy really really large chunks of data is to populate your datatable in chunks that will get written by sqlbulkcopy…

Speed increase over single insert statements is really huge. For some 80,000 entries, the time reduced from something like 5 minutes to under 20 seconds.





Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: