sqlcommand timeout in field

  • Thread starter Thread starter Adam Goetz
  • Start date Start date
A

Adam Goetz

Ok, got a SQL timeout occuring in the field that we cannot reproduce in the
development environment. Development system run time for this query is
sub-second. App is .NET 2, C#, using the
SqlConnection/SqlCommand/SqlDataReader classes. As stated below, the query
runs fine inside Query Analyzer at the client site.

select top 50 sum(orderedquantity) as qty, Pickingdetail.itemnumber,
description, count(distinct Pickingorder.ordernumber) as orders from
Pickingorder
inner join Pickingdetail on PickingOrder.Site = PickingDetail.Site AND
Pickingorder.ordernumber=Pickingdetail.ordernumber
inner join item on Pickingdetail.itemnumber=Item.itemnumber where (wave is
null or wave = '') and status=0
and PickingOrder.SITE = 'WH' and SUBSTRING(PickingOrder.OrderNumber, 1, 1) =
'a' and Pickingorder.ordernumber
in (select Pickingorder.ordernumber from Pickingorder inner join
Pickingdetail on
Pickingorder.ordernumber=Pickingdetail.ordernumber where (wave is null or
wave = '') and status=0
and PickingOrder.SITE = 'WH' and SUBSTRING(PickingOrder.OrderNumber, 1, 1) =
'a' group by Pickingorder.ordernumber having count(*)=1)
group by Pickingdetail.itemnumber, description order by count(distinct
Pickingorder.ordernumber) desc

As background,
there are approximately 100,000 rows in the Pickingorder table, 150,000 rows
in the Pickingdetail table and 25,000 rows in the Item table
the application is not run from the same machine as the DB server
When the query is run using SQL query analyzer it completes in less than 2
seconds in the field (so same query, but not inside the app where it
timesout).
The filters, status=0 and (wave is null or wave = '') both reduce the number
of rows returned by over 99%
The same query but without the sub-select - "and Pickingorder.ordernumber in
(select Pickingorder.ordernumber from Pickingorder inner join Pickingdetail
on
Pickingorder.ordernumber=Pickingdetail.ordernumber where (wave is null or
wave = '') and status=0 and PickingOrder.SITE = 'WH' and
SUBSTRING(PickingOrder.OrderNumber, 1, 1) = 'a' group by
Pickingorder.ordernumber having count(*)=1)" - runs in less than a second
in the same Windows app
 
Are you sure that the timeout is in the execution of the query itself and
not in the connection.Open() that (presumably) you are doing right before
executing your SqlCommand? If this were the development environment you
could find out with the debugger, but if this is happening in the field,
maybe the error message doesn't detail exactly where it happened.

I am mentioning this because, in case the error is in the connection.open,
it could be due to the pool of connections being exhausted. This happens if
you are missing a connection.Close elsewhere in the program. Every time you
open a connection and forget to close it, you consume a connection from the
pool. When no more connections are available, the next Open() waits for one
to be closed and, since this does not happen, it eventually fails with a
timeout error.

This is sometimes detected only in the field, because during testing in the
development environment you don't execute often enough the _other_ part of
the program which is wasting the connections at the same time that you are
testing the one that seems to time out.

Other than that, there is no reason why the query should take longer when
run from inside your program than when run from Query Analyzer, if it is
indeed exactly the same query. If you have administrative access to the
production server, you can use Sql Server Profiler to capture the precise
query that is being sent to the server from the program, and see if there is
some difference from the one you are examining as explained below. the
Profiler can also report the time spent executing the query, and you can see
if it is different from what you get from Query Analyzer.
 
Ok, got a SQL timeout occuring in the field that we cannot reproduce in the
development environment. Development system run time for this query is
sub-second. App is .NET 2, C#, using the
SqlConnection/SqlCommand/SqlDataReader classes. As stated below, the query
runs fine inside Query Analyzer at the client site.

select top 50 sum(orderedquantity) as qty, Pickingdetail.itemnumber,
description, count(distinct Pickingorder.ordernumber) as orders from
Pickingorder
inner join Pickingdetail on PickingOrder.Site = PickingDetail.Site AND
Pickingorder.ordernumber=Pickingdetail.ordernumber
inner join item on Pickingdetail.itemnumber=Item.itemnumber where (wave is
null or wave = '') and status=0
and PickingOrder.SITE = 'WH' and SUBSTRING(PickingOrder.OrderNumber, 1, 1) =
'a' and Pickingorder.ordernumber
in (select Pickingorder.ordernumber from Pickingorder inner join
Pickingdetail on
Pickingorder.ordernumber=Pickingdetail.ordernumber where (wave is null or
wave = '') and status=0
and PickingOrder.SITE = 'WH' and SUBSTRING(PickingOrder.OrderNumber, 1, 1) =
'a' group by Pickingorder.ordernumber having count(*)=1)
group by Pickingdetail.itemnumber, description order by count(distinct
Pickingorder.ordernumber) desc

As background,
there are approximately 100,000 rows in the Pickingorder table, 150,000 rows
in the Pickingdetail table and 25,000 rows in the Item table
the application is not run from the same machine as the DB server
When the query is run using SQL query analyzer it completes in less than 2
seconds in the field (so same query, but not inside the app where it
timesout).
The filters, status=0 and (wave is null or wave = '') both reduce the number
of rows returned by over 99%
The same query but without the sub-select - "and Pickingorder.ordernumber in
(select Pickingorder.ordernumber from Pickingorder inner join Pickingdetail
on
Pickingorder.ordernumber=Pickingdetail.ordernumber where (wave is null or
wave = '') and status=0 and PickingOrder.SITE = 'WH' and
SUBSTRING(PickingOrder.OrderNumber, 1, 1) = 'a' group by
Pickingorder.ordernumber having count(*)=1)" - runs in less than a second
in the same Windows app
Click to expand...
Hi Adam,
Purely a gut reaction, are you sure there isn't a threading deadlock?
Suggest you apply a lock to any section of the DAL that accesses these
tables and see what happens.

Also you could try making a typed dataset and do a dataadapter fill
instead of using the datareader then unload the dataset rows into your
current datastructure. It seems counter intuitive but I have had
increases in efficiency using this technique with ugly queries.

Do you have the option of putting the query into a sproc?

Good luck.
Bob
 
Hi Adam,
Purely a gut reaction, are you sure there isn't a threading deadlock?
Suggest you apply a lock to any section of the DAL that accesses these
tables and see what happens.

Also you could try making a typed dataset and do a dataadapter fill
instead of using the datareader then unload the dataset rows into your
current datastructure. It seems counter intuitive but I have had
increases in efficiency using this technique with ugly queries.

Do you have the option of putting the query into a sproc?

Good luck.
Bob
Click to expand...
Re: the dataset idea. It doesn't have to be a typed dataset.
(Hit the send button first and think afterwards as usual.)
 
Still no idea of cause. But what we have found (mostly through trial and
error) :

query analyser with hard coded values = 1-2 seconds
sqlcommand string + paramaters = timeout.
sqlcommand string + hard coded values = 10-15 second
change to stored procedure + paramaters = <1 second
 
Back
Top