Slow running query – help needed
Many thanks for your help, much appreciated!
The data types are as follows:-
• CRF1.Sys_Product is NvarChar (10)
• CAT1.Record_key was NvarChar(250) now changed to NvarChar(10)
I found some CRF1.Sys_Product records with ‘blank' values and I have now updated these with the appropriate CRF1.Sys_Product values.
I have added the missing index and it runs slightly quicker.
Is there any more that can be done?
link text
The new plan still has a warning: PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(int,[CRF1].[Sys_Product],0)" so this would be worth looking at to begin with.
I'm wondering if the index seek on [Insight_DBU_11202].[dbo].[FACT_SYS_RE_CRF_GL].[IDX_KAMTUNE01].(NonClustered) could be tweaked to assist the subsequent sort operation, I'm wondering if adding the Surrogate_Customer_PK as a key value instead of a non-key value (include) would assist or remove the sort?
See how that goes for now 🙂
Mart
I've resolved the “Convert_Implicit” issue; it's funny how sometimes you can't see the obvious when it's staring you in the face! I was joining on two fields that had different data types.
I have made the change that you suggested to the index and it's helped!.
I've also added a few more indexes and now the expensive operations are Hash Match, Merge Join and an Index Seek.
Is there anything else that could be looked at?
Thant's good news, especially about the index! Can you post up the new plan so we can see what's left?
Mart
Have you had a go at applying some filters to the indexes? As you're using constants in your query you could add one or two filtered indexes (the filters have to be simple so you may need two but have a play) to see if that can satisfy the query. This could potentially be a big hit on data movements
With regard to the joins being a high percentage, the entire plan has to be 100% so there will always be costs. Whether or not these joins are worth looking into futher is down to the new execution time of the query – what was it to satrt with an what is it now…what's the percentage improvement so far?
On a word of caution, when popping indexes in to aid a query remember they have a cost and the overall workload should be consisdered. Take a look at the index, how many queries are using it along with the type of work load on the box ect. to ensure the the benefit is worth the cost.
Someone else may be able to help with greater theory behind the joins and if they should be delvied into or if the optimizer is doing the right thing.
Cheers for now
Mart
http://sqlblog.com/blogs/rob_farley/archive/2011/03/22/probe-residual-when-you-have-a-hash-match-a-hidden-cost-in-execution-plans.aspx