-
Aug 23rd, 2014, 03:17 AM
#1
[RESOLVED] Timeout expired. The timeout period elapsed prior to completion of the operation or
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
It is not consistent but based on the observations of this particular using using a module of our app (commercial one and I don't have its source code to debug) the exception is usually in the afternoon and I would have thought it maybe because there are already more records in the afternoon but there is just less than 50 records to retrieve. I've tried running "exec sp_updatestats" but there is no difference.
Upon running a trace the following sql has a duration of 24,591 (the longest in the trace) which I think is 24.5seconds:
Code:
exec sp_executesql N'
UPDATE psExamResultMstr
set paidflag = 1
where paidflag = 0
and pk_psExamResultMstr in
(
select FK_psExamResultMstr from pspatitem
where rendate between @from and @to
and FK_psExamResultMstr is not null
and (isnull(oramount, 0) + isnull(discount, 0) + isnull(gntramount, 0)) = ((isnull(renqty, 0) - isnull(retqty, 0)) * isnull(renprice, 0))
)
UPDATE psExamResultMstr
set paidflag = 0
where paidflag = 1
and pk_psExamResultMstr in
(
select FK_psExamResultMstr from pspatitem
where rendate between @from and @to
and FK_psExamResultMstr is not null
and (isnull(oramount, 0) + isnull(discount, 0) + isnull(gntramount, 0)) < ((isnull(renqty, 0) - isnull(retqty, 0)) * isnull(renprice, 0))
)
',N'@from datetime,@to datetime',@from='2014-08-23 00:00:00',@to='2014-08-24 00:00:00'
Do you have recommendations on how can I find the culprit? If it is the SQL above then is there anything I can do to improve its speed?
Thanks!
-
Aug 23rd, 2014, 07:23 AM
#2
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
the default timeout is 30 seconds so with 24.5 you are on the edge and sometimes (maybe if the database is more busy in the afternoon) you run into a timeout.
even though if there are only 50 records that are affected by the sql (i understand you saying this) the subquery may take long.
i'd suggest you run this subpart in query analyzer to see if access to pspatitem is the bottleneck or if its the update on psExamResultMstr:
Code:
select FK_psExamResultMstr from pspatitem
where rendate between @from and @to
and FK_psExamResultMstr is not null
and (isnull(oramount, 0) + isnull(discount, 0) + isnull(gntramount, 0)) = ((isnull(renqty, 0) - isnull(retqty, 0)) * isnull(renprice, 0))
could you share the number of records in psExamResultMstr and pspatitem as well as the number of records returned by the two subqueries? the actual execution plan may also help.
is it possible to tweak the sql? you mention its a commercial app thats why i'm asking.
-
Aug 23rd, 2014, 07:39 AM
#3
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
I could probably tweak the SP if it is in an SP but it looks like it is not, they seem to be issuing exec from the app but I am not sure yet. I will have a look at the number of records and execution plan when I get back to it. Thanks for looking!
-
Aug 23rd, 2014, 07:47 AM
#4
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
yes, its embedded sql in the executable no SP on the server, so if you cannot recompile the exe with a modified SQL then i guess adding an index is your only choice to make this query faster. there should also be a way to increase the default timeout but i would be very careful with that and consider how this will develop further on, i.e. how long was this running without timeouts, how did the data increase over time etc. it could very well be that by increasing the timeout you first only postpone the problem as it will come back once it runs more that e.g. 60 seconds and secondly you may block other sql statements that run at the same time and suddenly get timeouts there.
-
Aug 23rd, 2014, 09:02 AM
#5
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
The timeout error has started occurring this week only and I ma baffled since there is a very few records retrieved in the module.
-
Aug 24th, 2014, 06:08 AM
#6
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
Originally Posted by digitalShaman
yes, its embedded sql in the executable no SP on the server, so if you cannot recompile the exe with a modified SQL then i guess adding an index is your only choice to make this query faster. there should also be a way to increase the default timeout but i would be very careful with that and consider how this will develop further on, i.e. how long was this running without timeouts, how did the data increase over time etc. it could very well be that by increasing the timeout you first only postpone the problem as it will come back once it runs more that e.g. 60 seconds and secondly you may block other sql statements that run at the same time and suddenly get timeouts there.
I first glance I don't think indexes will help because of the way the query is written. It looks non SARGABLE to me. There are where conditions that are calculated and the ranges between variables. Looking at the execution plan will tell you for sure.
Please remember next time...elections matter!
-
Aug 24th, 2014, 06:23 AM
#7
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
Originally Posted by TysonLPrice
I first glance I don't think indexes will help because of the way the query is written. It looks non SARGABLE to me. There are where conditions that are calculated and the ranges between variables. Looking at the execution plan will tell you for sure.
well, psExamResultMstr.paidflag, psExamResultMstr.pk_psExamResultMstr, pspatitem.rendate and pspatitem.FK_psExamResultMstr are certainly possibilities don't you think? but some of those might already be indexed. if the subquery is the slow one because there are many records in pspatitem with different rendate and no index on rendate, then this would be my first attempt. second would be psExamResultMstr.pk_psExamResultMstr.
-
Aug 24th, 2014, 10:54 AM
#8
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
Originally Posted by dee-u
The timeout error has started occurring this week only and I ma baffled since there is a very few records retrieved in the module.
It doesn't matter about the number of records retrieved... I've had queries that take a couple of minutes to return 50 records. Sounds poor performing? On the surface, until you learn that it's going through nearly 100 Million records and doing data extractions and aggregation. Still, it was a poor-performer, and through manipulation I was able to get it down to closer 60-second range. But I was also using an SProc, so I could do optimizations right on the server.
I think I have an idea for the query... let me tinker with it...
-tg
-
Aug 24th, 2014, 11:02 AM
#9
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
Dows this look like it would work?
sql Code:
UPDATE ERM set paidflag = K.paidflag from psExamResultMstr ERM inner join ( select FK_psExamResultMstr, case when (isnull(oramount, 0) + isnull(discount, 0) + isnull(gntramount, 0)) = ((isnull(renqty, 0) - isnull(retqty, 0)) * isnull(renprice, 0)) then 1 (isnull(oramount, 0) + isnull(discount, 0) + isnull(gntramount, 0)) < ((isnull(renqty, 0) - isnull(retqty, 0)) * isnull(renprice, 0)) then 0 end as paidflag from pspatitem where rendate between @from and @to and FK_psExamResultMstr is not null ) K on ERM.pk_psExamResultMstr = K.FK_psExamResultMstr where ERM.paidflag = 0
-tg
-
Aug 24th, 2014, 01:54 PM
#10
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
Dows this look like it would work?
almost. it does not change the flag from 1 to 0 as the original does because of the WHERE ERM.paidflag = 0 and i think the AND FK_psExamResultMstr IS NOT NULL is redundant (they would not join up if its NULL).
i was thinking of something similar:
Code:
UPDATE ERM
SET paidflag = (CASE WHEN Value1=Value2 THEN 1 ELSE 0 END)
FROM psExamResultMstr ERM
INNER JOIN (
SELECT FK_psExamResultMstr,
(isnull(oramount, 0) + isnull(discount, 0) + isnull(gntramount, 0)) As Value1
,(isnull(renqty, 0) - isnull(retqty, 0)) * isnull(renprice, 0) as Value2
FROM pspatitem
WHERE rendate BETWEEN @FROM AND @TO
) K ON ERM.pk_psExamResultMstr = K.FK_psExamResultMstr AND Value1<=Value2
anyhow, from the description i guess modifying the sql will not be possible.
-
Aug 24th, 2014, 03:41 PM
#11
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
Originally Posted by TysonLPrice
I first glance I don't think indexes will help because of the way the query is written. It looks non SARGABLE to me. There are where conditions that are calculated and the ranges between variables. Looking at the execution plan will tell you for sure.
Good point!
Wiki actually has a great link on "sargable" (although brief).
http://en.wikipedia.org/wiki/Sargable
-
Aug 24th, 2014, 05:21 PM
#12
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
Originally Posted by digitalShaman
well, psExamResultMstr.paidflag, psExamResultMstr.pk_psExamResultMstr, pspatitem.rendate and pspatitem.FK_psExamResultMstr are certainly possibilities don't you think? but some of those might already be indexed. if the subquery is the slow one because there are many records in pspatitem with different rendate and no index on rendate, then this would be my first attempt. second would be psExamResultMstr.pk_psExamResultMstr.
A table having indexes and a query being sargable are two different things. So what do you mean saying, ?well, psExamResultMstr.paidflag, psExamResultMstr.pk_psExamResultMstr, pspatitem.rendate and pspatitem.FK_psExamResultMstr are certainly possibilities don't you think?"
When I get to work tomorrow I'll do some more research and look closer but as I mentioned, at first glance:
This doesn’t seem sargable:
where rendate between @from and @to
nor this
(isnull(oramount, 0) + isnull(discount, 0) + isnull(gntramount, 0)) = ((isnull(renqty, 0) - isnull(retqty, 0)) * isnull(renprice, 0))
I'll ask one of our DBAs his opinion. I thought if the compiler has no way of knowing how to use the search arguments against an index you can end up with a table scan regardless of the indexes.
As I mentioned my thoughts were at first glance. Your post makes me wonder if you know what sargable means. I don’t mean that to be offensive, your question doesn't seem to have anything to do with my post.
.
Please remember next time...elections matter!
-
Aug 24th, 2014, 05:22 PM
#13
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
This should be sargable:
where rendate between @from and @to
-
Aug 24th, 2014, 05:51 PM
#14
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
Originally Posted by dee-u
It is not consistent but based on the observations of this particular using using a module of our app (commercial one and I don't have its source code to debug) the exception is usually in the afternoon and I would have thought it maybe because there are already more records in the afternoon but there is just less than 50 records to retrieve. I've tried running "exec sp_updatestats" but there is no difference.
Upon running a trace the following sql has a duration of 24,591 (the longest in the trace) which I think is 24.5seconds:
Code:
exec sp_executesql N'
UPDATE psExamResultMstr
set paidflag = 1
where paidflag = 0
and pk_psExamResultMstr in
(
select FK_psExamResultMstr from pspatitem
where rendate between @from and @to
and FK_psExamResultMstr is not null
and (isnull(oramount, 0) + isnull(discount, 0) + isnull(gntramount, 0)) = ((isnull(renqty, 0) - isnull(retqty, 0)) * isnull(renprice, 0))
)
UPDATE psExamResultMstr
set paidflag = 0
where paidflag = 1
and pk_psExamResultMstr in
(
select FK_psExamResultMstr from pspatitem
where rendate between @from and @to
and FK_psExamResultMstr is not null
and (isnull(oramount, 0) + isnull(discount, 0) + isnull(gntramount, 0)) < ((isnull(renqty, 0) - isnull(retqty, 0)) * isnull(renprice, 0))
)
',N'@from datetime,@to datetime',@from='2014-08-23 00:00:00',@to='2014-08-24 00:00:00'
Do you have recommendations on how can I find the culprit? If it is the SQL above then is there anything I can do to improve its speed?
Thanks!
We have some stored procedures that sometimes get really slow and recompiling them speeds them back up. We even have a job set up to compile certain SPROCs a couple times a day. The idea is to get around to tuning them and out of that job but it seems like they never make it off the list.
At any rate next time it gets really slow recompile it and see if it speeds it back up. If it does you still have the issue but also a clunky workaround.
I don't know if the execution plan gets negated, data is cached, or what. I do know it has turned locked up\slow SPs around.
Please remember next time...elections matter!
-
Aug 24th, 2014, 05:54 PM
#15
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
I have that same behavior in a couple of sprocs that start misbehaving around certain times of the accounting cycle. Once I tried to break down the slow logic in one into steps - fixed it for a long time and now it's back to bad again.
Re-compile always fixes it - just like the sprocs you mention...
-
Aug 24th, 2014, 07:09 PM
#16
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
Thanks guys, I will get back to it later today and look in applying your suggestions. What I fear is if it is not an SP then is there anything I can do? The sql did not mention any SP, from the looks of it the application seems to be issuing the 'exec' command as string?
-
Aug 24th, 2014, 07:22 PM
#17
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
Do not us IN with the SUB-QUERY
Instead try a FROM (DERIVED-QUERY) and in the DERIVED-QUERY put just the:
where rendate between @from and @to
Only that part of the WHERE clause goes into the DERIVED-QUERY...
In the SELECT LIST of this DERIVED QUERY put all the other fields that are part of your ORIGINAL where clause.
Now in the SELECT FROM (DERIVED-QUERY) WHERE {conditions} clause (yes the WHERE of the outer clause) you put the other WHERE business from your original where clause.
The idea is to get a QUICK RESULTSET from the QUERY OPTIMIZE based on a WHERE of an INDEX (assuming RENDATE is an index!).
Then when you post-process that FIRST QUICK RESULTSET (from the new DERIVED-QUERY) you just select off those other conditions.
-
Aug 25th, 2014, 04:34 AM
#18
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
Originally Posted by TysonLPrice
A table having indexes and a query being sargable are two different things. So what do you mean saying, ?well, psExamResultMstr.paidflag, psExamResultMstr.pk_psExamResultMstr, pspatitem.rendate and pspatitem.FK_psExamResultMstr are certainly possibilities don't you think?"
When I get to work tomorrow I'll do some more research and look closer but as I mentioned, at first glance:
This doesn’t seem sargable:
where rendate between @from and @to
nor this
(isnull(oramount, 0) + isnull(discount, 0) + isnull(gntramount, 0)) = ((isnull(renqty, 0) - isnull(retqty, 0)) * isnull(renprice, 0))
I'll ask one of our DBAs his opinion. I thought if the compiler has no way of knowing how to use the search arguments against an index you can end up with a table scan regardless of the indexes.
As I mentioned my thoughts were at first glance. Your post makes me wonder if you know what sargable means. I don’t mean that to be offensive, your question doesn't seem to have anything to do with my post.
.
well, i dont care much about terms and names actually. in fact i have seen that very simple and obvious things or algorithms that 25yrs ago nobody thought they would need a name infact got named and discussed in academic circles 15yrs later. i dont say this is bad or wrong, i just dont care.
you said that indexes wont help in the situation, i do not agree. if changing the sql is not an option (which, again, my understanding is as the given sql was logged in a trace and is not within a SP) then an index on rendate will help if there currently is none and there are many records in pspatitem. it will help sql server to determine on what rows it needs to do the oramount, discount, etc. calculation and on which ones it does not have to do this. once it has done this and came up with the list of FK_psExamResultMstr it will need to determine the rows of psExamResultMstr which have a pk_psExamResultMstr that exists in this list. pk_psExamResultMstr, as i see now is obviously the primary key of psExamResultMstr, so this is already indexed - sorry if i was confusing anyone with my suggestion of indexing this column. and as a very last ressort an index on paidflag would also help in determining the affected rows of psExamResultMstr. However as paidflag seems to be a binary flag (0/1) the benefit of an index will not be very high and neither would i add an index here without desperate need and potent success (i.e. if nothing else works and adding the index in DEV shows huge performance increase) nor did i suggest this.
you might see now, that there is some sargability, as some might call it, in this query. however, not much more can i say in regards to this topic as long as we have not seen any execution plan or got information on recordcounts etc. so i will shut up for now.
-
Aug 25th, 2014, 05:20 AM
#19
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
Originally Posted by digitalShaman
well, i dont care much about terms and names actually. in fact i have seen that very simple and obvious things or algorithms that 25yrs ago nobody thought they would need a name infact got named and discussed in academic circles 15yrs later. i dont say this is bad or wrong, i just dont care.
you said that indexes wont help in the situation, i do not agree. if changing the sql is not an option (which, again, my understanding is as the given sql was logged in a trace and is not within a SP) then an index on rendate will help if there currently is none and there are many records in pspatitem. it will help sql server to determine on what rows it needs to do the oramount, discount, etc. calculation and on which ones it does not have to do this. once it has done this and came up with the list of FK_psExamResultMstr it will need to determine the rows of psExamResultMstr which have a pk_psExamResultMstr that exists in this list. pk_psExamResultMstr, as i see now is obviously the primary key of psExamResultMstr, so this is already indexed - sorry if i was confusing anyone with my suggestion of indexing this column. and as a very last ressort an index on paidflag would also help in determining the affected rows of psExamResultMstr. However as paidflag seems to be a binary flag (0/1) the benefit of an index will not be very high and neither would i add an index here without desperate need and potent success (i.e. if nothing else works and adding the index in DEV shows huge performance increase) nor did i suggest this.
you might see now, that there is some sargability, as some might call it, in this query. however, not much more can i say in regards to this topic as long as we have not seen any execution plan or got information on recordcounts etc. so i will shut up for now.
I never once said "that indexes wont help in the situation". That just shows you don't understand what I am saying. You need to look again and try and understand the terms I'm using and what I am posting. I also suggest you read a little about sargable and non-sargable searches. One link has already been provided and there are quite a few articles written on it. The concept is fundemental to tuning queries.
"well, i dont care much about terms and names actually. in fact i have seen that very simple and obvious things or algorithms that 25yrs ago nobody thought they would need a name infact got named and discussed in academic circles 15yrs later. i dont say this is bad or wrong, i just dont care."
Your post is an obvious example of why you should care. You are going on and on about indexes and what I am posting is how indexes can be ignored. The very best, well thought out indexes in the world don't mean squat if the query is using them incorrectly (or the use is non-sargable).
Please remember next time...elections matter!
-
Aug 25th, 2014, 05:26 AM
#20
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
Originally Posted by TysonLPrice
I never once said "that indexes wont help in the situation". That just shows you don't understand what I am saying. You need to look again and try and understand the terms I'm using and what I am posting. I also suggest you read a little about sargable and non-sargable searches. One link has already been provided and there are quite a few articles written on it. The concept is fundemental to tuning queries.
"well, i dont care much about terms and names actually. in fact i have seen that very simple and obvious things or algorithms that 25yrs ago nobody thought they would need a name infact got named and discussed in academic circles 15yrs later. i dont say this is bad or wrong, i just dont care."
Your post is an obvious example of why you should care. You are going on and on about indexes and what I am posting is how indexes can be ignored. The very best, well thought out indexes in the world don't mean squat if the query is using them incorrectly (or the use is non-sargable).
wow, speechless...
-
Aug 25th, 2014, 06:17 AM
#21
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
Originally Posted by digitalShaman
wow, speechless...
Then my work is complete
Please remember next time...elections matter!
-
Sep 5th, 2014, 12:58 AM
#22
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
There are 69,584 records in psExamResultMstr and 109,919 records in pspatitem. And part of the actual execution plan is as follows:
It is talking about adding indexes, could you guys determine what will be the correct index to add? It is not an SP so I really cannot tweak the SQL.
EDIT:
It is suggesting adding the following index:
Code:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[psPatitem] ([FK_psExamResultMstr],[rendate])
INCLUDE ([renqty],[renprice],[retqty],[discount],[gntramount],[oramount])
GO
EDIT AGAIN:
Adding the suggested index solved the problem.
Code:
CREATE NONCLUSTERED INDEX IX_ExamResult_Renddate
ON [dbo].[psPatitem] ([FK_psExamResultMstr],[rendate])
GO
Last edited by dee-u; Sep 5th, 2014 at 01:47 AM.
-
Sep 5th, 2014, 01:51 AM
#23
Re: [RESOLVED] Timeout expired. The timeout period elapsed prior to completion of th
yes, its the index on rendate that helped. i guess only very few of the 109,919 records in pspatitem qualified as beeing within the time range. the index speeded up the subquery
Code:
select FK_psExamResultMstr from pspatitem
where rendate between @from and @to
and FK_psExamResultMstr is not null
and (isnull(oramount, 0) + isnull(discount, 0) + isnull(gntramount, 0)) = ((isnull(renqty, 0) - isnull(retqty, 0)) * isnull(renprice, 0))
as i already suspected in my very first post.
-
Sep 5th, 2014, 04:40 AM
#24
Re: Timeout expired. The timeout period elapsed prior to completion of the operation
Originally Posted by szlamany
...
based on a WHERE of an INDEX (assuming RENDATE is an index!).
Yes - RENDATE needs to be in an INDEX - as I mentioned here myself!
-
Sep 5th, 2014, 06:41 AM
#25
Re: [RESOLVED] Timeout expired. The timeout period elapsed prior to completion of th
What will be the difference of not including the INCLUDE? I did not include it as you can see in post #22 but still it worked.
-
Sep 6th, 2014, 03:26 AM
#26
Re: [RESOLVED] Timeout expired. The timeout period elapsed prior to completion of th
from what i know there will not be that much difference. if the fields are include in the index the db engine does not have to perform another read on the actual record once it identified it to qualify for the result set because all required data is already present in the index table. so including the fields will have some benefit for that specific query but as soon as you address another field in a quite similar query this benefit is gone.
the index you created does also include FK_psExamResultMstr which i think is also not the main driver in the performance gain. i think a
Code:
CREATE NONCLUSTERED INDEX Whatever
ON [dbo].[psPatitem] ([rendate])
GO
would have almost the same effect as a full
Code:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[psPatitem] ([FK_psExamResultMstr],[rendate])
INCLUDE ([renqty],[renprice],[retqty],[discount],[gntramount],[oramount])
GO
as suggested by ssms.
however with all these and also query optimizing by rephrasing a query the outcome is often hard to predict as the internal query optimizer may execute the query in a complete different way than it was written. the best approach is to restore a bak of the prod database to a dev environment and try different things there. for example some hard rules that are often given like 'do not use where x in (select..)' sometimes do turn out to have no effect at all as the optimizer changes this part into a join or similar. i read an interesting article on that some time ago where the guy in detail compared execution plans of in, join and exists and i remember they were the same or only very little difference.
sometimes index fragmentation and non upto date statistics is also an issue and you may find a query performing bad in prod but once you restore the bak to dev the same query performs well.
these are my experiences from mssql. other db engines are surely different here. bottom line: there are almost no strict rules (at least for me).
Last edited by digitalShaman; Sep 6th, 2014 at 03:43 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|