dcsimg
Results 1 to 9 of 9

Thread: SQL Timeout revisited

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2018
    Posts
    5

    Post SQL Timeout revisited

    Hi all,

    I've seen quite a few posts on fixing a timeout from a VB.NET program. Usually, add a couple of seconds to the timeout setting of the connection, or add an index to make the query go faster.
    But I can't help thinking (and in fact I am encountering those right now) that there will always be queries who just take longer, due to their complexity, the amount of data to process, etc.
    Isn't it possible and even -in some cases- normal that there are queries that run for a minute and a half?


    Hope you can shed some light on this, thanks in advance!
    Martin

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,341

    Re: SQL Timeout revisited

    Welcome to VBForums

    Some database queries (but not many) will always run for a long time, but there are usually things that can be done to improve queries.

    If you show us a slow query, and give us a clue as to the amount of rows in each table (and preferably data types and index info), we can probably come up with improvements.

  3. #3
    Fanatic Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    873

    Re: SQL Timeout revisited

    Quote Originally Posted by MartinDH View Post
    Isn't it possible and even -in some cases- normal that there are queries that run for a minute and a half?
    That would be pretty normal for reporting queries, esp. multi-year aggregations.

    Anyway, when doing synchronous queries it always makes sense to put an upper limit to the execution time, otherwise the user might decide your application is "broken" while waiting for it to finish for a minute or two.

    But then when your db requests are async and the UI shows a looping progress indicator with a nice Cancel button then there is no reason to put timeouts to your queries, just let them run until the user decides they can no longer tolerate the delay or in case of long reports just expect it to take longer and relax.

    So, either do cancel button or (configurable) timeouts.

    cheers,
    </wqw>

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,586

    Re: SQL Timeout revisited

    Quote Originally Posted by MartinDH View Post
    add a couple of seconds to the timeout setting of the connection
    Nope. the connection timeout is only about how long it takes to connect tot he database. For long-running queries, you need to increase the timeout on the command.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,787

    Re: SQL Timeout revisited

    Quote Originally Posted by MartinDH View Post
    Hi all,

    I've seen quite a few posts on fixing a timeout from a VB.NET program. Usually, add a couple of seconds to the timeout setting of the connection, or add an index to make the query go faster.
    But I can't help thinking (and in fact I am encountering those right now) that there will always be queries who just take longer, due to their complexity, the amount of data to process, etc.
    Isn't it possible and even -in some cases- normal that there are queries that run for a minute and a half?


    Hope you can shed some light on this, thanks in advance!
    Martin
    I use a technique in my applications that usually have me setting the TIMEOUT to 0 (NO timeout).

    For instance, in my payroll application the actual calculation of the payroll is done in a stored procedure - and this calc can take some time (1000+ employees not uncommon). Records get loaded into PayHistory and PayCheck tables (and several other deduction and distribution tables).

    As a part of a payroll application there are lots of reports that need to be run - Deduction Registers, Distribution Registers, Detail Registers.

    All of these reports are also done in SPROCS, and each of these SPROCS first checks to see if the payroll calculation has been committed (records appearing in the PayHistory table) and if it finds it has not, the calculation SPROC is called with a BEGIN TRAN transaction. Then the query is run for the report output and then the calculation is ROLLBACK'ed.

    This means that all my payroll reports can be run in a PRE-CHECK mode - reports and totals can be verified and reconciled against other reports and what not.

    Then when done, the calculation SPROC is run and COMMITTED - and all those reports re-printed and filed away for that payroll cycle.

    I've used this same technique in SO many other places - scheduling students into classes in a high school, assigning student damage charges for college dorm housing.

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

  6. #6

    Thread Starter
    New Member
    Join Date
    Oct 2018
    Posts
    5

    Re: SQL Timeout revisited

    Quote Originally Posted by si_the_geek View Post
    Welcome to VBForums
    Thanks, I appreciate it!

  7. #7

    Thread Starter
    New Member
    Join Date
    Oct 2018
    Posts
    5

    Re: SQL Timeout revisited

    Quote Originally Posted by jmcilhinney View Post
    Nope. the connection timeout is only about how long it takes to connect tot he database. For long-running queries, you need to increase the timeout on the command.
    Sorry, I did mean command

  8. #8

    Thread Starter
    New Member
    Join Date
    Oct 2018
    Posts
    5

    Re: SQL Timeout revisited

    Quote Originally Posted by szlamany View Post
    I use a technique in my applications that usually have me setting the TIMEOUT to 0 (NO timeout).

    Thanks, that's worth a try, that seems like a real easy modification.

  9. #9

    Thread Starter
    New Member
    Join Date
    Oct 2018
    Posts
    5

    Re: SQL Timeout revisited

    Quote Originally Posted by wqweto View Post
    That would be pretty normal for reporting queries, esp. multi-year aggregations.

    But then when your db requests are async and the UI shows a looping progress indicator with a nice Cancel button then there is no reason to put timeouts to your queries, just let them run until the user decides they can no longer tolerate the delay or in case of long reports just expect it to take longer and relax.
    Async, seems to me like the most structured solution. I have -briefly- looked into that some time ago, but found it hard because we're using a 3-tier architecture, with DO's and BO's. But I better look into that again.
    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width