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]

Re: SQL Update performance

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" <hrhoe@hotmail. com> wrote in message
news:011701c3b8 30$80e52df0$a30 1280a@phx.gbl.. .[color=blue]
> 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.[/color]