Query slow after adding additional where clause
We are running SQL Server 2005.
I have a query that runs in around 18 seconds. After adding an additional clause to the where statement it takes close to 2 minutes. The additional where clause is:
sc.ChargeAmount IS NOT NULL
Also the shape of the query changes totally.
I've attached two query plans from SQL Sentry. One with and one without the extra where clause.
Notice the thick lines in the above image. How do I get rid of them?
Any help would be appreceited.
There are too many issues to fix properly in this setting, but the simple cause of the performance regression when adding the new filter is that the optimizer chooses a nested loops join on the basis of too-low cardinality estimation, and perhaps a data skew or ascending-key statistics problem.
The brutal 'fix' above is to disallow nested loops join, giving the optimizer only hash and merge to choose from. This was possible in this case because the optimizer was already choosing hash/merge for all but the problematic join.
Even so, this is very much a last resort, as I said. Database designs with complex nested views and non-inline functions can be problematic at best.
- If sc.ChargeAmount is a computed column, consider making it persisted and indexing it. This allows you to pay the cost of computation at insert/update time, instead of at query time, and will allow you to eliminate the non-null values easier. I would suggest a filtered index, but it seems from the query plans that you are on 2005. Something to consider if you plan to move to a supported version of SQL Server.
- Add Description as an include column to the ServiceType index on ispServiceType (or add a clustered index to that table). Either of these should eliminate a costly RID lookup that appears in one version of the plan.
- Make sure that your stats on all relevant tables are up to date, and try to run both queries again with an explicit statement-level RECOMPILE.
- Your case expression should change to this, especially if those datetime columns are indexed (and you may consider calculating "now" into a variable ahead of time):
... , Age = CASE WHEN dt < DATEADD(DAY, -120, GETDATE()) THEN '121+' WHEN dt < DATEADD(DAY, -90, GETDATE()) THEN '091-120' WHEN dt < DATEADD(DAY, -60, GETDATE()) THEN '061-090' WHEN dt < DATEADD(DAY, -30, GETDATE()) THEN '031-060' WHEN dt < GETDATE() THEN '000-030' ELSE '???' END , Description -- remove prefixes from these columns ... from ( -- put the entire current FROM as a subquery, except the WHERE at the end SELECT *, -- list out actual columns you need perhaps dt = CASE s.[Status] WHEN 'r' THEN s.CreateDateTime ELSE wr.WRCreateDateTime END FROM prism72.dbo.ispService s ... ) owner on wrs.WorkRequestId = owner.WorkRequestId ) wr on s.TopLevelServiceKey = wr.ServiceKey ) AS x where ( (Status = 'R') -- remove prefixes here too or (ServiceKey is not null) -- and here ) ...