insert command sql server 2005

Iwan Petrow

Hi,

I do this - take some data with sqldataadaptor (at this moment
2000rows) in fill datatable.

For each two rows do some calculations and save data to the database
with insert command.
(the data which I insert are 5 int values for each two rows - so
2000*1999 insert commands)
In this way it is very slow. I think this is because of the invoking so
many times of the insert command (because without insert command it
takes about 10 min, but with it may be it will work 1-2 days (I run the
program in debug mode)).

Do you know other ways to do this (faster)?
(may be with sqldataadaptor).

Thanks.

Nov 3 '06 #1

Marc Gravell

So each crossed combination (except to self) needs a row? In this scenario,
I would probably find a way of saving the 2000 rows into a staging table on
the sql-server (2000 inserts), and then running an SP (or other SQL command)
that does the cross join and calculations locally to save round trips. Of
course, first I would verify the need for the cross-join at all, as this is
(obviously) not linearly scalable.

Another option (for more complex calculations) would be to do the cross-join
in the C# (as now), but write the data as csv / tsv to a text file, then
bulk insert the text file. Less round trips, but still transfers more data
over the network.

Marc

Nov 3 '06 #2