general question on query performance
-
Hi All,
This is a generic question regarding query optimization.The query is working fine yesterday i.e. it was running for more than 5 mins. After looking at the actual plan todo I see the statistics are off once again. When I try to run the same query again, it takes more time. Basically, I wanted to know why is it so? I see estimated and actual no of rows are off. I want ed when does statistics go off ? What are the reasons for it.
Could anyone please shed some light on this. Anyone observed such kind of patterns and why it happens in that way? what things need to be watch out for ?
Here specific scenario where I am facing issues dealing with statistics. Please find attached screenshot.
Basically, I found a table nonclustered index where Estimated number of rows and Actual number of rows are not matching. So, I dropped and re-created the index and re-ran the query still I see the same. I even tried to update the statistics of that particular table with FullScan, still it didn't help. WHY? How can I keep the statistics up to date so that Estimated and Actual number of rows match?
Any suggestions please.
Thanks,
Sam
- vsamantha35 - Tuesday, August 29, 2017 3:21 AMHi All,
This is a generic question regarding query optimization.The query is working fine yesterday i.e. it was running for more than 5 mins. After looking at the actual plan todo I see the statistics are off once again. When I try to run the same query again, it takes more time. Basically, I wanted to know why is it so? I see estimated and actual no of rows are off. I want ed when does statistics go off ? What are the reasons for it.
Could anyone please shed some light on this. Anyone observed such kind of patterns and why it happens in that way? what things need to be watch out for ?
Here specific scenario where I am facing issues dealing with statistics. Please find attached screenshot.
Basically, I found a table nonclustered index where Estimated number of rows and Actual number of rows are not matching. So, I dropped and re-created the index and re-ran the query still I see the same. I even tried to update the statistics of that particular table with FullScan, still it didn't help. WHY? How can I keep the statistics up to date so that Estimated and Actual number of rows match?
Any suggestions please.
Thanks,
Sam
Screenshots are of little use, Sam. Can you attach an actual execution plan please? Execution plans provide us with a huge amount of information in addition to what you've posted.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Pl find attach plan. Sort it by actual number of rows in desc order. its an actual execution plan generated in SQL Sentry plan explorer.
- vsamantha35 - Tuesday, August 29, 2017 11:16 AM
Excellent, thanks. Here's how I'd tackle your query.
Firstly, there are two main chunks, T0 and T1. T0 is an aggregate of T1, otherwise they are identical and you could use a CTE for both T0 and T1. However, why run the same expensive query twice? Run the results of T1 into a #temp table and reference it in the main query directly, and for T0, reference the same temp table and aggregate it. For the purpose of tuning I'd recommend that you begin with the code in T1: chop it out and work with it directly in SSMS. You can easily reassemble the full query when you are done.
Down to some details.
You have messy date arithmetic for which you have already been given more conventional code - use it.
Table [name_to_all_acct] has no clustered index, identify as best you can a suitable clustered index and create it.
There's a crazy WHERE clause, change it:WHERE CASEWHEN NOT "TAX_NAME_CROSS_APPLICATION"."NAME_LINE" IS NULL THEN 'Y' ELSE 'N' END = 'Y'
is the same as　　
WHERE "TAX_NAME_CROSS_APPLICATION"."NAME_LINE" IS NOT NULL
This is enough to get you started, there's still a long way to go.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply