SqlCommand slow on INSERT

[John Bailo]

I have a c# program that loops through a table on a DB2 database.

On each iteration it assigns data to values in the SqlParameter
collection. The command text is an INSERT statement to a Sql Server
database, run with an .ExecuteQuery I enclosed the loop in a
SqlTransaction and commit it at the end.

I timed the program and it inserts about 70 records a second...which I
think is sort of slow...so I set up some Debug.WriteLine s to show where
the time was being spent.

The DataReader loop to the DB2 table is instantaneous. Almost 0s spent
getting each record. Same with assigning values.

The slow step is the actual execution of the SqlCommand. However, I
also ran a SQL Trace and monitored the execution of the statement on the
server. It took 0s to execute. The SqlCommand itself is adding an
extra 0.01s to 0.03s which can add up over the course of hundreds of
thousands of records.

So the only overhead is running .ExecuteQuery on the SqlCommand
object(!) Is there anyway to reduce or minimize this overhead, or a
setting that can affect performance.

I mean if my external source and target are running at 0s - my code
shouldn't be adding overhead to run a command!

[William \(Bill\) Vaughn]

John, I'm not talking about using INSERTs from your client. I'm talking
about using an intelligent INSERT statement within the context of a stored
procedure that runs on the server. It inserts rows from a "temporary" table
on the server (which was filled with BulkCopy) to a permanent table. This
permits the server-side business rules, triggers, RI and other logic to run.

--
_______________ _______________ ______
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
_______________ _______________ ____

"John Bailo" <ja*****@texeme .com> wrote in message
news:XY******** *************** *******@speakea sy.net...

Hmmm...well, there's no reason I can't do that as an intermediate way in
my code.

Do I have to create the temporary table on the same command that I run my
INSERTS on?

Or can I just create two SqlCommand's and run them on the same
SqlConnection? Will the table remain in memory if I do that?

And then I'll need to do the transfer from the temp table to the table
after that.

William (Bill) Vaughn wrote:

Basically, yes. We generally transfer to "temporary" tables in the
database (not to tempdb) and perform intelligent INSERT/UPDATE statements
from there. It's far faster.