Best way to write DataSet to database table

[cokelite]

Referring to my previous thread i would be interested to know your opinion about what's the best, most efficient approach for writing a method that would accept three parameters:

1) database connection
2) DataSet
3) name of a table in database

and write the content of the DataSet's Table[0] to an empty, structurally identical database table?

The passed DataSet-parameter would be filled with loads of data. With the method i have described in my previous post, using DataAdapter.Update, it takes about 1 minute to insert 10 000 rows and that's just way too slow.

[TheCPUWizard]

If you are doing more than about 50 rows at a time, AND if you are using SQL Server 2005 or later (this may or may not apply to other DMBS engines).....

1) Write the Rows to an XML Stream
2) Send the Entire Stream to the Database as a Sotred Proc PArameter
3) Convert the XML into a Table VARIABLE
4) Intert the TableVariable into the Real Table.


btw: 0.006 Seconds per row is actually quite good....

I am very curoius as to what type of application would be posting data of this nature (I have worked on systems with well over 10M transactions per hr, and usually find that the pattern you describe is the result of some poor architectural decisions....

Even the idea that you want to pass a connection around raises concerns.....

[crackersixx]

Regardless of your reasons, or whether or not you "should" be doing things as you are, there is a faster method for bulk importing of massive amounts of data.

SqlBulkCopy Class in System.Data.SqlClient namespace is something you should look into.

[cjard]

Youve gotten something in your data access code or database design, wrong I think.. Last time I used a TableAdapter to Update() a dataset with 100,000 rows, it inserted 800rows/second (and a trigger executes somewhere in the region of 25 string replacements/case manipulations per row)

Actually, the string replacements dont seem to knock the performance much, because using Oracle's client instead of the microsoft one, and employing a transfer array of 500 rows buffer saw the rate rise to over 3000 rows/sec..

You can check to see if the adapter is performing a select immediately after the insert, to check for values caluclated by the database.. I dont know where to find such an option on a DA, as I work nearly exclusively with tableadapters and its a tickbox in the TA wizard. Perhaps someone else will know

[TheCPUWizard]

Youve gotten something in your data access code or database design, wrong I think.. Last time I used a TableAdapter to Update() a dataset with 100,000 rows, it inserted 800rows/second (and a trigger executes somewhere in the region of 25 string replacements/case manipulations per row),

Thas 1.25 mS per row. This is well within the range of variance that could be caused by the machine (CPU, Memory, etc), data file (fragmentation, etc) or other normal variances....