The SQl server is still receiving the "Timed out" error message. Expired Pending

Question

I thought I had a sql error, deprived of the message here a little back ... (Error message: Timed out. The timeout period that elapsed before the operation or the server did not respond. ") I am trying to start this using the base tools data in visual studio ... not in the management studio, and not through the client code / ADO (for now). I rewrote a fairly simple request that uses a couple of user functions ... the functions, as well as parts of the request, were tested, and everything works ok, but the request is below time. This is done exactly the same as typed in Management Studio, and takes 4 minutes. As I mentioned in my other post, I changed the setting in Tools> Options> Designers> End Connection String Time to 120 seconds to match with this post, but ... it still expires after 30 seconds. Adding ISNULL in this latest version is the change it launches in the 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 return a subset of stock symbols that do not have a corresponding data point in my daily price table for at least 3 days. Any takers? Thanks to everyone.

Answer 1

Without taking into account your timeout;

Are you using the sql management console to run your query? If so, when connected to the database there is a parameter button that allows you to set timeouts.

Alternatively, if in the query window, right-click and select Query Options ....

0 means unlimited, I would check it. 4 minutes is a long time, maybe the request can be reorganized to speed up?

If you run this inside Visual Studio via C #, the default timeout is 30 seconds. Change it by setting the command timeout:

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

Answer 2

If the request takes a long time, then there is probably something wrong. I would declare a variable to store RecentTradingDateByNumber log. 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 execution in a management studio, go to the "Query / Include Actual Execution Plan". If you also want to see request traffic by select numbers, etc. You can also enable client statistics. "Client request / inclusion statistics"

If you want to know more about query execution research, see here .

Answer 3

It concerns me that your procedure takes 4 minutes. This seems like a fairly simple query, assuming that the functions do what they do, and with indexing and appropriate table design, it should return much faster than that.

You have reviewed the execution plan for this request:

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

Scalar functions can be performance problems when repeatedly called on a set with a large number of lines, as well as in case of corruption.