1

I am working on a desktop application in VB.Net (4.0) There will a lot of SQL scripts which contains a lot of insert queries . Currenlty we are running the scripts using SMO (http://msdn.microsoft.com/en-us/library/ms162557.aspx)

One way

For performance, we have planned to change the sql scripts to xml file ( instead of insert queries, it will have xml data with a proper xsd defined ). Loads xml to dataSet and Using SQL Bulk Copy we are trying to insert to SQL Server.

Second way

Loads xml to dataSet Creates user defined table type based on the datatable in dataset (creted from xml file) and create a stored procedure which has parameter as this table type . In that sp, it will contains insert into tablename select * from tablevalueparameter.

So please suggest me to best and suitable way. Thanks in advance.

Coderr
  • 67
  • 3
  • 8
  • possible duplicate of [Performance of bcp/BULK INSERT vs. Table-Valued Parameters](http://stackoverflow.com/questions/2149897/performance-of-bcp-bulk-insert-vs-table-valued-parameters) or http://stackoverflow.com/q/8618261/27535 or – gbn Jan 03 '12 at 14:35

1 Answers1

1

Unscientific answer...

  • SQLBulkCopy uses a different API that is optimised
  • A table valued parameter acts like "normal" SQL

The TVP approach would the same order of magnitude performance as running individual INSERT statements, which I'd expect slower than SQLBulkCopy.

However, I'd expect each technique depends on how many rows you have: for 1000s or 10k+ per batch I'd use SQLBulkCopy without thinking because I don't need a stored procedure.

gbn
  • 394,550
  • 75
  • 549
  • 647