Large update SQL command works on old slow servers but times out on super fast one!

[steve]

Hi all,

I have a problem in that I have written a .Net 2 application for a
customer that performs significant amounts of manipulation - 250,000
records manipulated and updated with combination of update and select
into commands.

The issue is that it works fine on my old slow development 2003 server
with SQL 2005, and also on my home 2003 SBS with SQL 2005 too. Yet on
the customer PC (the important one obviously) whilst other pieces of
the appliation are significantly quicker (it is a new quad processor
high spec machine), the key update routine times out when updating
large qtys of records which my slow servers munch through.

I've checked the IIS settings and timeouts but no difference. THe
customer has even rebuilt the server after we could find no difference
- yet same issue. It is a killer when the project is technically
finished but it just won't run on the customer server!

Any help much appreciated.,
Steve

 

[Mary Chipman [MSFT]]

Hi all,

I have a problem in that I have written a .Net 2 application for a
customer that performs significant amounts of manipulation - 250,000
records manipulated and updated with combination of update and select
into commands.

The issue is that it works fine on my old slow development 2003 server
with SQL 2005, and also on my home 2003 SBS with SQL 2005 too. Yet on
the customer PC (the important one obviously) whilst other pieces of
the appliation are significantly quicker (it is a new quad processor
high spec machine), the key update routine times out when updating
large qtys of records which my slow servers munch through.

I've checked the IIS settings and timeouts but no difference. THe
customer has even rebuilt the server after we could find no difference
- yet same issue. It is a killer when the project is technically
finished but it just won't run on the customer server!

Any help much appreciated.,
Steve

 

[Mary Chipman [MSFT]]

it's probably your code that's slow, although it's hard to tell
without actually seeing it. It probably wasn't noticeable when you
were working on your machine in single user mode. Have you run a
Profiler trace to examine the traffic? Bear in mind that using the
default isolation level of Read Committed that SQLS cannot select rows
that are in the middle of a transaction before the transaction
commits. So you are going to have concurrency/performance headaches if
you have many users who are selecting and updating large amounts of
data at the same time.

-Mary

 

[Cor Ligthert[MVP]]

Steve,

It is probably not your code that is slow. Performance has mostly more to do
with the environment round the workstations and database servers than with
the code if the measurement on different computers is not equal. That are
often not the computers involved (however can be as the load on a database
server for other processes is high).

Do you know the load on your clients database server? Are you sure that the
way the data is transported is as quick as on your own computer.

If it is a Lan it can be the type of Lan, the bandwidth, the load of the
Lan, if there are no switches used (you see than a lot of collissions) or
whatever.

If it is Internet it is almost the same, although than mostly more obvious.

Cor

 

[Cor Ligthert[MVP]]

I forgot to tell, it can as be an antene from a car which is hitting the
garage and therefore give an electric reaction or something. I remember me
that Bill once has written something like that here.



Cor