SQL Server Performance Forum – Threads Archive

Query Optimize – timeout issue

Hi Group, I have the following query which takes 3 seconds to run in the Query Analyser. It returns 437 records. However, when running this query from my ASP page, it times out. On the query plan, for the Select statement, the subtree cost is 54.2. Is this too high? What is the subtree cost referring to? If this is too high, how can I optimize my query? SELECT USERID2.dbo.tblBranch.brnBranchName, left(ExportData.dbo.tblWeeklySales.wsrCusNum,5) AS AccountNo, ExportData.dbo.tblWeeklySales.wsrCusName as Client , SUM(ExportData.dbo.tblWeeklySales.wsrSales) AS Turnover , SUM(ExportData.dbo.tblWeeklySales.wsrProfit)AS Profit FROM UserID2.dbo.tblRegion INNER JOIN UserID2.dbo.tblArea ON UserID2.dbo.tblRegion.regRegionID = UserID2.dbo.tblArea.areRegionID INNER JOIN UserID2.dbo.tblBranch ON UserID2.dbo.tblArea.areAreaID = UserID2.dbo.tblBranch.brnAreaID INNER JOIN ExportData.dbo.tblWeeklySales ON RIGHT(USERID2.dbo.tblBranch.brnBranchNumber, 3) = RIGHT(ExportData.dbo.tblWeeklySales.wsrCusBrn, 3) WHERE (UserID2.dbo.tblArea.areAreaID = 1<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> AND (DATEPART(yy,ExportData.dbo.tblWeeklySales.wsrWERun) = 2003) AND (Datepart(m,ExportData.dbo.tblWeeklySales.wsrWERun) >= 1 AND Datepart(m,ExportData.dbo.tblWeeklySales.wsrWERun) <= 1)GROUP BY USERID2.dbo.tblBranch.brnBranchName,left(ExportData.dbo.tblWeeklySales.wsrCusNum,5), ExportData.dbo.tblWeeklySales.wsrCusName ORDER BY USERID2.dbo.tblBranch.brnBranchName asc TIA Pisces

If it always runs correctly in Query Analyzer in about 3 seconds, but times out in your ASP page, the most likely it is not a query-related performance issue, and instead is an issue with the ASP code and/or how the web server is setup and configured. That is where I would spend my time looking for the problem. Also, 3 seconds is even a long time to return 437 records. Subtree cost is not really important to watch, unless you can compare it to something else. You need to determine if your query is properly using indexes, and if not, perhaps add the necessary indexes. See the articles on this website on how to read and understand execution plans to learn more. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

This is not going to make it fly, but your where clause has this AND (Datepart(m,ExportData.dbo.tblWeeklySales.wsrWERun) >= 1
AND Datepart(m,ExportData.dbo.tblWeeklySales.wsrWERun) <= 1)
The only thing that can be >= 1 and <= 1 is 1, so why not a single statement of AND (Datepart(m,ExportData.dbo.tblWeeklySales.wsrWERun) = 1 ) To help speed up the rest, I’d need to know indexes.
For example, you’re selecting
UserID2.dbo.tblArea.areAreaID = 18
and this is also your join field to the branch table. So depending on indexing, you might speed it up to include this in your join INNER JOIN UserID2.dbo.tblBranch ON UserID2.dbo.tblArea.areAreaID = UserID2.dbo.tblBranch.brnAreaID and UserID2.dbo.tblBranch.brnAreaID = 18 Just some suggestions for getting the 3 seconds down.

Thanks for your reply Chris.
The query is parameter driven where I’ve got
AND (Datepart(m,ExportData.dbo.tblWeeklySales.wsrWERun) >= 1
AND Datepart(m,ExportData.dbo.tblWeeklySales.wsrWERun) <= 1)
… the user has selected from January to January. I will try indexing columns as suggested… Thank you!!

quote:Originally posted by ChrisFretwell This is not going to make it fly, but your where clause has this AND (Datepart(m,ExportData.dbo.tblWeeklySales.wsrWERun) >= 1
AND Datepart(m,ExportData.dbo.tblWeeklySales.wsrWERun) <= 1)
The only thing that can be >= 1 and <= 1 is 1, so why not a single statement of AND (Datepart(m,ExportData.dbo.tblWeeklySales.wsrWERun) = 1 ) To help speed up the rest, I’d need to know indexes.
For example, you’re selecting
UserID2.dbo.tblArea.areAreaID = 18
and this is also your join field to the branch table. So depending on indexing, you might speed it up to include this in your join INNER JOIN UserID2.dbo.tblBranch ON UserID2.dbo.tblArea.areAreaID = UserID2.dbo.tblBranch.brnAreaID and UserID2.dbo.tblBranch.brnAreaID = 18 Just some suggestions for getting the 3 seconds down.

Hello Brad,
Could another database on the same Server affect performance? This database has a lot of updates during the day and has a lot of redundant tables/inconsistencies etc. When I select a table to index in this database via the Index Wizard, the wizard says the table doesn’t exist even though I can see it visibly!!
Running DBCC SHOW_STATISTICS reports an error on the name of the table etc…
The database was moved form a SQL Server 6.5 to SQL Server 7.0 Could this have a knock on affect on the database I’m working on i.e the constant hammering of updates/interaction on this database? Thanks for your comments.

quote:Originally posted by bradmcgehee If it always runs correctly in Query Analyzer in about 3 seconds, but times out in your ASP page, the most likely it is not a query-related performance issue, and instead is an issue with the ASP code and/or how the web server is setup and configured. That is where I would spend my time looking for the problem. Also, 3 seconds is even a long time to return 437 records. Subtree cost is not really important to watch, unless you can compare it to something else. You need to determine if your query is properly using indexes, and if not, perhaps add the necessary indexes. See the articles on this website on how to read and understand execution plans to learn more. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

Do you see high processor / memory utilization on the server? Also I would like you to compare the execution plans for the query. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Pisces, run DBCC CHECKDB on the database and see if you get any errors. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

]]>