September 25, 2020 at 3:00 am
Hi,
I've attached plan for this query. This query returns around 70M rows. I see the most expensive operator is at clustered index scan Dim_Invoice. There is a filter on inovoice date column from factinvoice line table. Will declaring non clustered index on invoice date on factinvoiceline speed up the query? Currently this query takes close to 2 hours to run.
Any idea would be appreciated.
September 25, 2020 at 12:07 pm
The plan didn't upload, so it's going to be really hard to make a suggestion without the plan or the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 25, 2020 at 1:15 pm
Interesting. It works fine on my machine. When I double click on it it pulls up on my SSMS. It was saved using Plan Exploer if that helps.
I'm attaching the xml version of it. Not sure if this will be much helpful though.
Thanks for your help.
September 25, 2020 at 1:35 pm
Interesting. It works fine on my machine. When I double click on it it pulls up on my SSMS. It was saved using Plan Exploer if that helps.
I'm attaching the xml version of it. Not sure if this will be much helpful though.
Thanks for your help.
Really not much of a help because it's an Estimated Execution Plan and not an actual. It's hard to believe the huge amount of rows it's estimated to be returned (although that may be an indication of what's wrong in the code... lack of proper criteria). Also, you'd need to attach the code because the code is truncated in the execution plan.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2020 at 1:45 pm
It calls a view. We'd need to see the query for that and, again, it would be great to see an "Actual Execution" plan instead of the "Estimated Execution Plan" you've provided.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2020 at 1:46 pm
I can't see the full SQL query text. Can you paste the full query in this thread?
Also, how many rows are on EDW.dbo.DimInvoice?
September 25, 2020 at 1:56 pm
view attached. It takes 2 hours to run. I'll try to run when there is not much activity in the server.
September 25, 2020 at 2:16 pm
and if that view uses other views post the definition of those also.. recursively
September 26, 2020 at 2:51 am
DimInvoice has 33925277 rows. I am not able to finish the query in SSMS at all. Running all sorts of issues. I believe it pulls the data but to deliver the data over the network takes forever as I see Asyn_Network wait type on there.
I have attached the code for view.
Any advise you can give by looking at estimated execution plan?
September 26, 2020 at 9:02 am
that has another few views within it - need those also
when you are querying this view are you applying any filtering or just doing a select * from view without any where clause?
and is this part of an extract onto another system/table or are you just querying it on SSMS - if SSMS that amount of rows will kill you and would definitely cause it to run for hours before it fails with memory error.
September 26, 2020 at 8:23 pm
Actually, I don't need to see anymore.
First of all is the fact that this report returns 70 MILLION rows. To what end? Who is going to read a 70 MILLION row report???
Yeah... I might be making a mistake in calling it a "report". Perhaps it's not. But why would you need to generate 70 MILLION rows from a data warehouse (indicated by use of the word "DIM_")? Is it supposed to be delivered as a file somewhere?
The main issues with performance on this query are likely the fact that it's an "All-in-one" query (or close to it) that uses a bunch of views that, like the one recently posted, are also pretty big "All-in-one" queries themselves and they have (like the one most recently posted), several possible challenges in the areas of SARGability in the JOINs.
My recommendation would be to use "Divide'n'Conquer" methods to build the 70 MILLION rows and fix the JOINs and other criteria in the views. It may even be worthwhile to convert the views to "parameterized views" in the form of iTVFs (inline Table Valued Functions) and see if any of the other functions mentioned are Scalar UDFs or mTVFs (multi-statement Table Valued Functions) that could also be replace with simple inline code or iTVFs.
The first thing to do is find out what's taking the most time in this proc and fix it. Wash, rinse, repeat, until it runs fast enough to please. Without knowing what part of this proc are causing problems, we're not actually going to be able to fix this puppy. We need to peel one potato at a time on this one.
Oddly enough, doing that type of troubleshooting will create the "Divide'n'Conquer" method as you work on finding what the high points are, so save all the pieces. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 28, 2020 at 11:20 am
The only way to make a query without any filtering run faster is by purchasing bigger, faster, hardware. There's no magic that can be done when you move everything. Jeff's very correct. Understanding better what this query satisfies, why and how it's used, and mechanisms for breaking it down to return smaller data sets is your best bet.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 29, 2020 at 7:35 am
They say - the fastest internet data channel is a truck full of hard drives.
there might be a good reason to have a good data replication channel, so those 70 mil records will be at the reporting Server in time for the report, do aggregations can be done locally.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply