need to reduce cpu cost on query runs quickly but gets called often
looking to see how I can reduce the cost of the query. Also confuse don why the cost of tbl_Fare_Detail is so high
So I think a few things are in order:
- Estimates are off for several operations, like estimate of 1 and actual of 7K+. To solve this I would consider using an appropriately-indexed #temp table instead of a table variable.
- I'll repeat my advice to try out my dynamic SQL model for optional parameters – this query is potentially going to be very vulnerable to suboptimal operations based on a plan being chosen for different parameters. Dynamic SQL isn't the most maintainable thing but if you combine this with Optimize for Ad Hoc Workloads you will get plans that are much more appropriate for any given set of provided parameters (for parameter sniffing issues, you can also add OPTION (RECOMPILE) within the dynamic SQL).
- I would consider not having that big ugly set of substring comparisons – why are there seven independent pieces of information encoded into a single string? This alone destroys any chance of a seek on FCL.
- What is the purpose of OPTION (FORCE ORDER) in this plan? What specific benefit does it provide in this case? Have you tried without it?
- Have you considered read committed snapshot instead of littering your query with READUNCOMMITTED hints? That may or may not improve performance, depending on current tempdb load, but it increases your odds of having correct results.
Thanks for the awesome answerand time much appreciated.
- I am using the declared tables to try and prevent a recompile because of how much this sp gets called. I have tried to use temp table with out much of a change but will look into further.
- I want to try and prevent the recompile but I will experiment also
- I have started to change the structure so that I am not using the function
- I have added this in to help with the declared table and force the order of the joins. This helped to deal with the estimate of 1 row in the declared table
- I will look into this. I am not very worried about the dirty reads here vs the locking. I have code further down the process to make sure the data is in check. I found the pay off was worth the chance of a small change between.
Thanks again for all your help