November 21, 2011 at 3:38 pm
Hi Experts,
We have a query which uses nested loops and runs for hours and did not complete for when I gave INNER HASH JOIN, it runs withnin 10 seconds. I am using SQL Server 2008R2. Query is given below
Row count for every table:
Sales_N_Activity--4696060
WC_SBL_CONTACT --334732
WC_SBL_ADDR --747746
WC_CURRENT_DATE_T --1
The query with hint is given below which runs very quickly:
Since the query is generated by a third party tool, I cannot give hint. But I want this report to run.
Please suggest.
Regards,
Virendra
November 21, 2011 at 3:59 pm
Can you post the execution plan?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 21, 2011 at 4:04 pm
Plans are attached.
Regards,
Virendra
November 21, 2011 at 6:05 pm
kyadav (11/21/2011)
Plans are attached.Regards,
Virendra
I suspect SQLRNNR was hoping for the Actual Execution Plan rather than the Estimated Execution Plan. One shows what SQL Server "thinks" will happen when the query is executed, the other shows what actually happened. There is a lot of useful stuff in the Actual plan that will help identify issues such as incorrect statistics (which can lead to improper join operators being chosen), if you can generate and post the Actual plan that would be very helpful.
As an aside, a quick perusal of the estimated plans you posted shows a number of Scans occurring that return very large row counts; I suspect that better indexing could dramatically improve the performance of the query. Still, it's hard to know what the actual facts of the matter are without seeing the Actual plan.
I also noticed that the plan estimates that over 28,000 rows will be returned by the query; that sounds like an awful lot of rows to display in a report...
November 21, 2011 at 6:11 pm
Hey JonFox,
Query without hint never completes and I cannot send actual plan for that for other it is being attahced againg. Yes the reports is displaying 27910 rows to be exact.
Regards,
Virendra
November 21, 2011 at 6:28 pm
Change the query to go in the tables only once rather than 1 gazillion times and you should be fine.
You can pivot the data like this.
SELECT SUM(CASE WHEN Date BETWEEN 'start period' AND 'end period' THEN ColumnValue ELSE NULL END)
... repeat for all required periods, which turns this into a single scan instead of all of them.
November 21, 2011 at 6:30 pm
Query cannot be modified as it is being generated from third patry tool.
Regards,
Virendra
November 21, 2011 at 6:45 pm
kyadav (11/21/2011)
Query cannot be modified as it is being generated from third patry tool.Regards,
Virendra
Is it always generated the same way or does it change depending on user input (columns list, joins, etc)?
November 21, 2011 at 6:50 pm
Supposing that it is same, I have created plan guide and implemented it in database. User will come tomorrow test the report. I am not sure if takes any input. If it does than we have to think the other way. Currently my plan is using @TYPE=N'SQL'.
Regards,
Virendra
November 21, 2011 at 6:55 pm
kyadav (11/21/2011)
Supposing that it is same, I have created plan guide and implemented it in database. User will come tomorrow test the report. I am not sure if takes any input. If it does than we have to think the other way. Currently my plan is using @TYPE=N'SQL'.Regards,
Virendra
Let me put this into perspective, this would be almost a miracle to tune this without a query rewrite, if that's not even static then there's probably not much we can do.
On what server edition are you (standard, enterprise)?
November 21, 2011 at 7:01 pm
Standard Edition, will take a look tomorrow.
Thanks for your time.
Regards,
Virendra
November 21, 2011 at 7:05 pm
kyadav (11/21/2011)
Standard Edition, will take a look tomorrow.Thanks for your time.
Regards,
Virendra
That eliminates anything we could have done with indexed views.
Can you add clustered indexes on the tables, or any indexes for that matter?
Are the stats up to date? Indexed defragged?
November 22, 2011 at 2:24 pm
Indexes are already there on the table and stats are from yesterday.
We drop-create indexes daily as a batch cycle process. My question is it should pick the HASH JOIN instead of doing Index Seek and Nested loops because two inner select has row count of 2649 and 27994 respectively so if go fow Index Seek and Nested loops it is doing for 2649*27994 which hangs the report for ever and if I put a INNER HASH JOIN it comes in 9-10 seconds.
Regard,
Virendra.
November 22, 2011 at 2:27 pm
Can you add / edit indexes?
November 22, 2011 at 2:42 pm
Yes I can add indexes, but I am confused where to add them.
Regards,
Virendra
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply