Results 1 to 26 of 26

Thread: [RESOLVED] Timeout expired. The timeout period elapsed prior to completion of the operation or

  1. #1

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Resolved [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!
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  2. #2
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    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.

  3. #3

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    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!
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  4. #4
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    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.

  5. #5

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    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.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  6. #6
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Timeout expired. The timeout period elapsed prior to completion of the operation

    Quote Originally Posted by digitalShaman View Post
    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!

  7. #7
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: Timeout expired. The timeout period elapsed prior to completion of the operation

    Quote Originally Posted by TysonLPrice View Post
    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.

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Timeout expired. The timeout period elapsed prior to completion of the operation

    Quote Originally Posted by dee-u View Post
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Timeout expired. The timeout period elapsed prior to completion of the operation

    Dows this look like it would work?
    sql Code:
    1. UPDATE  ERM
    2. set paidflag = K.paidflag
    3. from psExamResultMstr ERM
    4. inner join (
    5.     select FK_psExamResultMstr,
    6.       case
    7.         when (isnull(oramount, 0) + isnull(discount, 0) + isnull(gntramount, 0)) = ((isnull(renqty, 0) - isnull(retqty, 0)) * isnull(renprice, 0)) then 1
    8.         (isnull(oramount, 0) + isnull(discount, 0) + isnull(gntramount, 0)) < ((isnull(renqty, 0) - isnull(retqty, 0)) * isnull(renprice, 0)) then 0
    9.       end as paidflag
    10.     from pspatitem
    11.     where rendate between @from and @to
    12.       and FK_psExamResultMstr is not null
    13.   ) K on ERM.pk_psExamResultMstr = K.FK_psExamResultMstr
    14. where ERM.paidflag = 0

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    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.

  11. #11
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Timeout expired. The timeout period elapsed prior to completion of the operation

    Quote Originally Posted by TysonLPrice View Post
    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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  12. #12
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Timeout expired. The timeout period elapsed prior to completion of the operation

    Quote Originally Posted by digitalShaman View Post
    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!

  13. #13
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Timeout expired. The timeout period elapsed prior to completion of the operation

    This should be sargable:

    where rendate between @from and @to

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  14. #14
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Timeout expired. The timeout period elapsed prior to completion of the operation

    Quote Originally Posted by dee-u View Post
    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!

  15. #15
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  16. #16

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    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?
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  17. #17
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  18. #18
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: Timeout expired. The timeout period elapsed prior to completion of the operation

    Quote Originally Posted by TysonLPrice View Post
    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.

  19. #19
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Timeout expired. The timeout period elapsed prior to completion of the operation

    Quote Originally Posted by digitalShaman View 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.
    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!

  20. #20
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: Timeout expired. The timeout period elapsed prior to completion of the operation

    Quote Originally Posted by TysonLPrice View Post
    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...

  21. #21
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Timeout expired. The timeout period elapsed prior to completion of the operation

    Quote Originally Posted by digitalShaman View Post
    wow, speechless...
    Then my work is complete
    Please remember next time...elections matter!

  22. #22

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    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:

    Name:  executionplan.jpg
Views: 17035
Size:  10.0 KB

    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.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  23. #23
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    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.

  24. #24
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Timeout expired. The timeout period elapsed prior to completion of the operation

    Quote Originally Posted by szlamany View Post
    ...
    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!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  25. #25

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    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.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  26. #26
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    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
  •  



Click Here to Expand Forum to Full Width