#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    5
    Rep Power
    0

    SQL Application Timeouts Advice


    I am a project manager who is being given what I perceive to be the run around. Here is the information:

    An app we have is running but is crashing as the SQL timeout value set in it (not the server) has been exceeded. (30 seconds).

    The advice I am being given is this is valid as nothing should take more than thirty seconds. Even when I run the select statement on its own it takes 52 seconds to bring back 500K records.

    My question is surely the developer needs to simply increase the timeout values? I cant understand his reluctance?

    Could you advise me why he might be reluctant or am I right?

    Any advice would be very helpfull.
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,126
    Rep Power
    9398
    I assuming you're talking about the query timeout, not the connection timeout.

    30 seconds is quite reasonable. In that time the server should have begun sending the data back. If not then, very likely, the database/tables aren't optimized as needed, and that's the problem that needs fixing.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    5
    Rep Power
    0
    Originally Posted by requinix
    I assuming you're talking about the query timeout, not the connection timeout.

    30 seconds is quite reasonable. In that time the server should have begun sending the data back. If not then, very likely, the database/tables aren't optimized as needed, and that's the problem that needs fixing.
    I am not talking about connection timeout. It is execution timeout. OK what is happening is everything is working OK, the query starts executing but does not finish in 30 seconds and the application then abends. The application isnt giving the SQL statement long enough to complete (even though it has started).

    Thats the issue.

    Thanks for the reply I hope my information is a little clearer now.

    Why would the app time out?

    Thanks
  6. #4
  7. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,126
    Rep Power
    9398
    Originally Posted by OneEyedJack
    The application isnt giving the SQL statement long enough to complete (even though it has started).
    You need to change your mentality. The problem is that the query is too complex and/or not optimized enough to execute in a reasonable amount of time.

    Use whatever tools you have available to profile the query, identify the problem(s), and fix them. Unless your query is absolutely ridiculous, even 500k rows should start coming back within seconds.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    5
    Rep Power
    0
    The query is a simple one and does start to execute immeadiately.

    The application though times out before it is finished.

    The query does start to execute very quickly but does not complete before the applicaion abends.

    Surely the app should be coded to allow time for the query to complete... not just start to execute...?? or am I still looking at this wrongly?The error thrown is:
    Error System.data.sqlclient.sqlexception: Timeout expired. The timeout period elapsed prior to the completion of the operation .

    Thanks, I appreaciate your help.
  10. #6
  11. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,126
    Rep Power
    9398
    What I'm saying is
    Originally Posted by OneEyedJack
    Surely the app should be coded to allow time for the query to complete...
    No. You've got it backwards: the query should be coded (and the database and its tables arranged) so that it executes within the reasonable amount of time that the app provides.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    5
    Rep Power
    0
    Originally Posted by requinix
    What I'm saying is

    No. You've got it backwards: the query should be coded (and the database and its tables arranged) so that it executes within the reasonable amount of time that the app provides.
    Right I understand. But I feel I need to clarify something.

    If the app runs a query the app will be OK IF the query starts sending records back within the execution timeout provided by the app even if all of the records dont come back in one go?

    If so I can understand what you say.

    Is the above right?

    Thanks
  14. #8
  15. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,126
    Rep Power
    9398
    Note: the timeout is judged by the client, not the server.

    I believe that it counts for all activity for the (established) connection. So that would be the combined time of sending the query and receiving all the results.
    If your database server is nearby (local or intranet) then 500k records should transfer very quickly, meaning the period until the first record is where most of the time could go. But if the server isn't close (internet) then even if the first result comes in 25 seconds you might still time out.

    Just try optimizing the query - it can only do good. Maybe you're missing a couple indices, or have too many subqueries, or something like that.
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    5
    Rep Power
    0
    Originally Posted by requinix
    Note: the timeout is judged by the client, not the server.

    I believe that it counts for all activity for the (established) connection. So that would be the combined time of sending the query and receiving all the results.
    If your database server is nearby (local or intranet) then 500k records should transfer very quickly, meaning the period until the first record is where most of the time could go. But if the server isn't close (internet) then even if the first result comes in 25 seconds you might still time out.

    Just try optimizing the query - it can only do good. Maybe you're missing a couple indices, or have too many subqueries, or something like that.
    Optimising did the trick a few more indexes allowed it to work.

    I do beleive that a query can take a long time in total to execute but as long as the records start coming back in a decent time the app doesnt crash.

    Thanks for your help

IMN logo majestic logo threadwatch logo seochat tools logo