Post reply

Query Timeout

  • October 8, 2004 at 6:03 pm

    #162387

    Hey Guru's

    I'm in the middle of creating a migration and have one quick question.  I have a query that takes about 20 minutes run.  I'm wanting to put the query into a stored proc.  When I do this and try to run it, after 10 minutes I get the following error:

    [OLE/DB provider returned message: Timeout expired]

    Is there anyway I can extend the timeout of the server?

    Thanks

  • October 9, 2004 at 10:21 am

    #526048

    Hi sstecher,

    Although a query timeout increase is not recommended, below is the link that explains how to do it:

    for local queries:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_5rfy.asp

    for remote queries:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_6soe.asp

    Regards,

    JP

  • October 9, 2004 at 10:44 am

    #526050

    Instead of increasing the timeout value, maybe you should look at how the query is written or break it down into smaller pieces.  Sometimes doing several smaller updates is much better than one large update.  For example, maybe a query will update 10 million rows.  If there is an identity column in the table, you could set up a loop with a counter to update 10,000 rows at at time based on the value of the identity column. 

     

     

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • October 11, 2004 at 6:22 am

    #526126

    Another thing I would look at is the estimated query plan prior to executing this again. There may be 1 or more indexes which can be added or improved upon prior to execution.

  • October 11, 2004 at 7:14 am

    #526138

    In EM go to server properties and under connections tab type in query time-out field 0, that means unlimited

  • October 11, 2004 at 7:17 am

    #526141

    I appreciate all of the help guys.  I ended up using the following the system stored proc

    sp_configure 'remote query timeout', 0

    go

    reconfigure with override

    go

    Thanks

  • October 11, 2004 at 12:53 pm

    #526238

    It's likely that your table design is inefficient. If the table is big do add index to the primary keys or whatever fields use JOIN.

  • October 11, 2004 at 8:51 pm

    #526275

    You might want to run the index tunning wizard against that table and pass all the queries running on that table. At the end of the wizard it will show you recommendations and how much performance will improve.

    I ran this wizard against one of my table for a query and it improved performance from 8 minutes to 18 seconds.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply