CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
StatsViaCsh
StatsViaCsh

Reputation: 2640

SQl Server still getting the error of "Timeout expired. The timeout period elapsed"

I thought I had a sql error licked in a post here just a bit ago... (Error message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.") I'm trying to run this with the database tools in visual studio... not management studio, and not via client code/ ADO (yet). I rewrote a fairly simple query that uses a couple of custom functions... the functions as well as the parts of the query have been tested and all are running well, but the query below times out.. this does run exactly as typed in Management Studio, and takes about 4 minutes. As I mentioned in my other post, I changed the setting under Tools>Options>Designers>"Override connection string time- out value" to 120 seconds as per this posting, but... it still times out after 30 seconds. Adding the ISNULL in this latest version is the change that has it running in management studio.

SELECT Symbol, LatestDate
FROM (SELECT Symbol, ISNULL(dbo.LatestDateInDailyPricingVolBySymbol(Symbol), '1/1/1900') AS LatestDate FROM tblSymbolsMain) AS T2
WHERE (LatestDate < dbo.RecentTradingDateByNumber(3))

The general idea is to get back a subset of stock symbols that don't have a corresponding data point in my daily pricing table for at least 3 days. Any takers? Thanks all.

Upvotes: 8

Views: 93955

Answers (4)

Rameesh Puthukkudy
Rameesh Puthukkudy

Reputation: 127

Some time I get time out. but some time it execute within seconds. it is not advisable one whenever get time out error I had been changing the data type.

ex. if your query have criteria datetime. Change data type to just date. Other Option is Date data type to datetime data type. I know it is not right way. but It is working to me

Upvotes: 0

Arion
Arion

Reputation: 31239

If a query takes that long of time then it is probably something wrong. I would declare a variable to store the RecentTradingDateByNumber. So it looks like this:

DECLARE @RecentTrandingDateByNumber DATETIME
SET @RecentTrandingDateByNumber=dbo.RecentTradingDateByNumber(3)

SELECT 
    tblSymbolsMain.Symbol, 
    MAX(tblSymbolsMain.TradeDate)
FROM 
    tblSymbolsMain
GROUP BY 
    Symbol
HAVING 
    MAX(TradeDate) < @RecentTrandingDateByNumber

To see the execution in management studio go to "Query/Include Actual Execution Plan". If you also want to see the traffic of the query the numbers of select etc. You can also include the client statistics. "Query/Include client statistics"

If you want to know more information about examining the queries execution see here

Upvotes: 2

Jon Raynor
Jon Raynor

Reputation: 3892

Without regards to your timeout;

Are you using the sql management console to run your query? If so, when connecting to the database there is an options button that allows one to set the timeouts.

Connection Options

Also, if in the query window, right click and choose Query Options....

0, means unlimited, I would check these. 4 minutes is a long time, maybe the query can be refactored to run faster?

enter image description here

If you are running this inside of Visual Studio via C# the default command timeout is 30 seconds. Alter it by setting the command time out:

SqlCommand comm= new SqlCommand();
comm.CommandTimeout = 300;

Upvotes: 12

Cade Roux
Cade Roux

Reputation: 89671

It concerns me that your routine takes 4 minutes to start with. That seems like a pretty simple query assuming the functions do what they seem to do, and with indexing and appropriate table design, it should return much more quickly than that.

Have you looked at the execution plan for this query:

SELECT Symbol, MAX(TradeDate)
FROM tblSymbolsMain
GROUP BY Symbol
HAVING MAX(TradeDate) < dbo.RecentTradingDateByNumber(3)

Scalar functions can be performance problems when called repeatedly on sets with a large number of rows, and also hurt sargability.

Upvotes: 1

Related Questions