MSSQL query taking too long
-
A query that used to take milliseconds is now taking 3 seconds to run The table only has 7000 records and I rebuilt the one index that it uses I am thinking if I drop/create the table the problem may get fixed, but the table has several dependent views, etc. Will each dependent item need to be created again?
Thanx, >>>-----> MikeO
-
A query that used to take milliseconds is now taking 3 seconds to run The table only has 7000 records and I rebuilt the one index that it uses I am thinking if I drop/create the table the problem may get fixed, but the table has several dependent views, etc. Will each dependent item need to be created again?
Thanx, >>>-----> MikeO
Well, I suggest you to check this article and probably will help you. http://www.serverwatch.com/tutorials/article.php/2175621/How-to-Optimize-Queries-Theory-an-Practice.htm[^]
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com
-
A query that used to take milliseconds is now taking 3 seconds to run The table only has 7000 records and I rebuilt the one index that it uses I am thinking if I drop/create the table the problem may get fixed, but the table has several dependent views, etc. Will each dependent item need to be created again?
Thanx, >>>-----> MikeO
In MS SQL Server Management Studio, 1) create a new query 2) paste the query in the query window 3) under the Query menu select the option to "Include Actual Execution Plan" 4) Execute the query. Check out the execution plan results and you want to avoid "scans", this means that it is reading the table or index sequentially. Also under the Query menu, choose the "Include Client Statistics" and check those values for anything that may seem out-of-whack. Pleasse reply to this posting so that I know if this was helpful. david
-
In MS SQL Server Management Studio, 1) create a new query 2) paste the query in the query window 3) under the Query menu select the option to "Include Actual Execution Plan" 4) Execute the query. Check out the execution plan results and you want to avoid "scans", this means that it is reading the table or index sequentially. Also under the Query menu, choose the "Include Client Statistics" and check those values for anything that may seem out-of-whack. Pleasse reply to this posting so that I know if this was helpful. david
I followed the steps in your reply. Nothing looks to be wrong with the query. It even appears to execute normally in Management Studio. When I execute it in the code on the client though it takes about three seconds. The client app has been running successfully for about four years. Now it is getting bogged down by two queries. Both do field sums. Any ideas what could change on a server or connection that would slow down a SUM operation? This is a close approximation of the query that is taking so long: SELECT SUM(WorkSecs) as WorkTime, SUM(RunSecs) as RunTime FROM tblData WHERE Machine = 1 and Operator = 1
Thanx, >>>-----> MikeO