Increase Connection Timeout


Author
Message
Bill Cunnien
Bill Cunnien
StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)
Group: Forum Members
Posts: 785, Visits: 3.6K
I have a query that is taking about 90 seconds.  The window times out before any data is displayed.  How do I increase the connection timeout for this query?

Thanks,
Bill

Reply
Peter Jones
Peter Jones
Advanced StrataFrame User (718 reputation)Advanced StrataFrame User (718 reputation)Advanced StrataFrame User (718 reputation)Advanced StrataFrame User (718 reputation)Advanced StrataFrame User (718 reputation)Advanced StrataFrame User (718 reputation)Advanced StrataFrame User (718 reputation)Advanced StrataFrame User (718 reputation)Advanced StrataFrame User (718 reputation)
Group: Forum Members
Posts: 386, Visits: 2.1K
Hi Bill,

You could try putting:

Option (Recompile)

as the last line in your proc. This will cause SQL to recalculate its execution plan each time the proc runs. While this, in itself is an overhead, it is better than using an old and entirely inappropriate plan if you have a wide range of options that SQL should be considering on each execution although SQL is supposed to be pretty smart in knowing when to recalculate an execution plan.

Bottom line is that 90 seconds is a looooong time and the issue is probably in the area of your proc using full table scans rather than index seeks. Just in case you don't know - the best way to sort these issues is to put the proc's code is Query Analyzer and have a look at the Execution Plan

Cheers, Peter

GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...




Threaded View
Threaded View
Bill Cunnien - 17 Years Ago
Bill Cunnien - 17 Years Ago
Bill Cunnien - 17 Years Ago
Edhy Rijo - 17 Years Ago
Paul Chase - 17 Years Ago
                         Bill, I thought that I had found where to set it on a per query...
Paul Chase - 17 Years Ago
                             Yeah, Paulnailed it on the last post. The most common timeout that is...
Trent L. Taylor - 17 Years Ago
                         Thanks Paul and Edhy!! I am still using SQL Server 2000...I will see...
Bill Cunnien - 17 Years Ago
                             [quote]If the command timeout can be set, how do I do that from the...
Trent L. Taylor - 17 Years Ago
                                 Thanks. Took me a minute to make the right connection from the command...
Bill Cunnien - 17 Years Ago
Kenneth Langley - 17 Years Ago
Greg McGuffey - 17 Years Ago
Kenneth Langley - 17 Years Ago
Greg McGuffey - 17 Years Ago
Kenneth Langley - 17 Years Ago
Peter Jones - 17 Years Ago
Peter Jones - 17 Years Ago
Larry Caylor - 17 Years Ago
Trent L. Taylor - 16 Years Ago
Edhy Rijo - 16 Years Ago
                     Good point. Yes, they should.
Trent L. Taylor - 16 Years Ago
Kenneth Langley - 16 Years Ago
Trent L. Taylor - 16 Years Ago
Kenneth Langley - 16 Years Ago
Trent L. Taylor - 16 Years Ago

Similar Topics

Reading This Topic

Login

Explore
Messages
Mentions
Search