I have a user running query which is sometimes under 10 seconds while other time going over 30 minutes.
This is not a store proc , he is doing it manually from his machine to get data and later after extraction just push it to other application for data visualization
Below is the Query i tried to create which is similar to my user query, logic wise. But since i am still figuring out how to run ETL package on WideWorldImporter database to get it up to date, cant really repro the run time.
For my env: Fact table is around 6 TB. What i have noticed is around 10K rows fetched is varying between 10-30secs while anything over 60K rows goes over 10 mins
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; Select * from [Fact].[Transaction] f INNER JOIN [Dimension].[Customer] dc on dc.[Customer Key] =f.[Customer Key] INNER JOIN [Dimension].[Date] dd on dd.[Date] =f.[Date Key] INNER JOIN [Dimension].[Supplier] ds on ds.[Supplier Key] =f.[Supplier Key] INNER JOIN [Dimension].[Transaction Type] dt on dt.[Transaction Type Key] =f.[Transaction Type Key] INNER JOIN [Dimension].[Payment Method] pm on pm.[Payment Method Key] =f.[Payment Method Key] WHERE f.[Date Key]>= convert(varchar,getdate(),112) and f.[Lineage Key] between DATEDIFF(SECOND,CONVERT(DATE,GETDATE()),CURRENT_TIMESTAMP) -300 AND DATEDIFF(SECOND,CONVERT(DATE,GETDATE()),CURRENT_TIMESTAMP) ORDER BY [Transaction Key]
Actual query has inner join to almost 10 more tables.
Lineage_key is INT NOT NULL and we have NC index on this column as the only leading key
Is there something i can do with Query tuning as index look good?
Plan:- I can paste the part of plan as captured, rest it cant fit due to 30 odd inner joins.
I tried to look at the plan which is too complicated chain as there are 30 odd inner joins. So would try my best to paste. But i found something interesting. I am seeing that compile time is around 8 secs in total of 14 sec run. So i think problem seems more on very high compile time. Is there a way we can avoid compilation time when query has joins with so many tables?
Ran wait stats for 10 min interval when same query ran 8 mins and other time 22 secs. Both queries i ran from SSMS.