SQL Update performance

[hrhoe]

Hi,
I created a C# program that update SQL Server table
row by row.
I referenced third party dll file to do the necessary
modification for each row.
And I used foreach loop to update data in the table.

The problem is the poor performance.
If I use the third party program, I can process 10,000
records within a minute, but if I try with my program,
it took almost 10 minutes for 10,000 records.
I tried to create index on the primary column before
update, but it didn't help much.
I know that cursor like update results slower performance,
but I need to process every single rows.
Does anyone know hot to improve performance in this
situation?

Thank you in advance.

[William Ryan]

It could be a few things..but the first that comes to mind is are you
leaving the connection open or are you closing it each time. Normally you
want to close a connectino immediately, but if you are iterating through a
loop 10,000 times, leaving it open until it's done processing( make sure you
have a close in the finally block though) may have a big performance
enhancing effect.

If you post the code, it may be easier to figure out.

HTH,

Bill
"hrhoe" <hr***@hotmail.com> wrote in message
news:01****************************@phx.gbl...

Hi,
I created a C# program that update SQL Server table
row by row.
I referenced third party dll file to do the necessary
modification for each row.
And I used foreach loop to update data in the table.

The problem is the poor performance.
If I use the third party program, I can process 10,000
records within a minute, but if I try with my program,
it took almost 10 minutes for 10,000 records.
I tried to create index on the primary column before
update, but it didn't help much.
I know that cursor like update results slower performance,
but I need to process every single rows.
Does anyone know hot to improve performance in this
situation?

Thank you in advance.