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

Question

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.

Answer 1

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.

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?

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;

Answer 2

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

Answer 3

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.