Sql-server – How to improve this query performance by indexing properly

index-tuningsql-server-2008t-sql

I have following select query, and I need to improve the performance of this query. Can anyone suggest me how to convert the clustered index scan to index seek?

DECLARE @now DateTime;        
DECLARE @currentweekstart DateTime;        
DECLARE @currentweekend DateTime;     
SET @now = getDate();        
SET @currentweekstart = DATEADD(        
                 DD,        
                 -(DATEPART(DW, @now - 7) - 1),        
                 @now - 7);         
SET @currentweekend = DATEADD(        
               DD,        
               7 - (DATEPART(DW, @now - 7)),        
               @now - 7);     

  SELECT  CHINFO.CHILDID,COUNT(*) AS CURRENTWEEKPOTTYBREAKS        
          FROM  BKA.CHILDINFORMATION CHINFO         
  JOIN  BKA.CHILDEVENTS CHE         
         ON CHE.CHILDID = CHINFO.CHILDID          
  WHERE TYPE ='POTTYBREAK'        
          AND CHE.ADDDATE BETWEEN @currentweekstart AND @currentweekend         
           GROUP BY CHINFO.CHILDID

enter image description here

Primary Keys:

TableName –> ColumnName

CHILDINFORMATION–>ChildId

CHILDEVENTS–>EventId

Foreign Keys:

CHILDEVENTS–>Here ChildId is the foreign key reference of
CHILDINFORMATION table

NonClustered Indexes:

CHILDINFORMATION–>No nonclustered indexes

ChildEvents–>IX_ChildEvents_Type–>Type include EventId

ChildEvents–>IX_ChildEvents_Type_AddDate–>Type,AddDate

ChildEvents–>IX_ChildEvents_Adddate_Type–>Adddate,Type include
ChildId,EventId

Best Answer

Since you have a foreign key from CHILDEVENTS to CHILDINFORMATION and you don't select any column from the child information table, you can skip that table. One less join will certainly not hurt:

SELECT CHE.CHILDID, COUNT(*) AS CURRENTWEEKPOTTYBREAKS        
FROM  BKA.CHILDEVENTS AS CHE           
WHERE CHE.TYPE ='POTTYBREAK'        
  AND CHE.ADDDATE BETWEEN @currentweekstart AND @currentweekend         
GROUP BY CHE.CHILDID ;

As for indexing, the existing index on (Adddate,Type) INCLUDE (ChildId, EventId) does not look very good for this query. I would try replacing it (in fact removing both those AddDate-Type indexes) with an index on:

(Type, AddDate) INCLUDE (ChildId)

Related Solutions

Sql-server – Query Performance Issue

You say

Above plan is for subid = 11 or 7 in @t table variable

I think you may be under a misapprehension here. SQL Server does not look at the contents of the table variable and choose a plan based upon the values it contains.

The statement is compiled before the table variable contains any rows at all and you will get the same plan (that assumes a single row) regardless of whether it eventually contains 2 (and would match 95.5% of the rows) or 1 (and would match only 0.0008%).

The table variable may of course also contain multiple rows but SQL Server will not take account of that except if you use the OPTION (RECOMPILE) hint and even then there are no statistics on table variables so it cannot take any account of actual values.

Some alternate plans are below

plan 1

plan 2

These require finding all matching rows and sorting them.

Because NCx_1 is not declared as a unique index the include(QueueItemID) is ignored (as explained in More About Nonclustered Index Keys) and QueueItemID gets added as an index key column instead. This means that SQL Server can seek on IsProcessed, QCode and the matching rows will be ordered by QueueItemID.

The plan in your question therefore avoids a sort operation but performance is entirely reliant upon how many rows in practice need to be evaluated before the first one matching the SubID IN (SELECT SubID FROM @t) predicate is found and the range seek can stop.

Of course this can vary wildly depending on how common the SubID values contained in @t are and whether there is any skew in the distribution of these values with respect to QueueItemID (You say that both around 350k rows match the seek predicate and that around 350k end up being seeked so for SubID = 7 it sounds like these are all at the end or perhaps no rows match at all - which would be the worse case for this plan).

It would be interesting to know what the estimated number of rows coming out of the seek is. Presumably this is much less than 350,000 and thus SQL Server chooses the plan you see based on this estimated cost.

If the table variable will always just have few rows you might find this rewrite works better for you.

SELECT TOP 1 QueueItemID
FROM   @t
       CROSS APPLY (SELECT TOP 1 t.QueueItemID
                    FROM   QueueTable t
                    WHERE  t.IsProcessed = 0
                           AND t.QCode = 'USA'
                           AND SubID = [@t].SubID
                    ORDER  BY t.QueueItemID) CA 
ORDER BY QueueItemID

For me it gives the plan below where it seeks into the index on subid,isprocessed,qcode,queueitemid as many times as you have rows in the table variable. It is similar to the first plan shown but may be slightly more efficient as each seek stops after the first row is returned.

plan

Sql-server – filtered index null and > 0 disparity — need explanation

There are a bunch of questions hidden in here.

Let's start with, why the logically equivalent >0 yields a scan instead of a seek.

The index is filtered on IS NOT NULL, so SQL Server knows that no value is NULL in this index. However, it does not know that all values are >0 because an IDENTITY property is not a constraint. So, to return all rows >0 SQL Server has to find the first row that actually is >0 (using a seek operation) and then scan the rest of the rows from there on. If that scan is covering most of the table, spending the additional reads for the first seek might be more expensive than just scanning the full table. Both approaches will lead to the same result in all cases. Which one sql server picks is based on statistics.

So, why is the scan faster?

SQL Server has three physical join operators to choose from: The Nested Loops Join, the Hash Join and the Merge Join. The Merge Join is by far the fastest, however it requires that both inputs are sorted the same way. For more details on the join operators check out my JOIN series here: http://sqlity.net/en/1146/a-join-a-day-introduction/ The seek at the bottom of the Nested Loop Join operator get's executed for each row of the top input. That is a lot of seeks and they can become costly quickly. SQL Server usually figures out where the so called "tipping point" lies at which a full table scan is cheaper then a repeated seek. However, that decision is based on statistics again and often off by a few hundred rows.

If the Merge join is faster and the data is already sorted, why does SQL Server not use it for both queries?

That is a good question... Usually bad decisions by the optimizer are cause by stale statistics (on any table involved in the query). However, the optimizer has limitations and dealing with advanced features like filtered indexes might force it out of its comfort zone. If updating statistics does not help we need to help the optimizer. There are several ways that can be done. You could flat out provide a MERGE JOIN hint. For this query that might even be safe as the query will always have to look at all rows in the child table. However, in general, hints should be considered an absolute last resort as they prevent the optimizer from adapting to changing data. You could also try to rewrite the query in a way that gets the optimizer started on a different path leading to a better result. That is kind of comparable to hinting, only hidden - usually even worse a strategy. However, if you think back to pre 2005 times, it was very common to utilize query rewrites just to get the optimizer to end up with a better plan. The advantage of this approach is that the next version of the optimizer might recognize the rewritten query to be equivalent to the original so that this technique does not have longterm effects. (That could also be bad...)

An other approach altogether is to provide additional information to the optimizer. If you have complex queries the detail of the statistics might just not be fine-grained enough for the optimizer to make a good choice. In that case it often helps to create a filtered index or statistics object. In your case we are looking at a filtered index already. What might be missing is the information that all rows are actually >0. So it might help to add both conditions to the index filter.

While this all does not give you a real solution, I hope it helps understanding what is going on here. To solve this, I would start with adding that second condition to the same index and see where that leads you.

On a different note: You mention a 50-50 comparison of two execution plans. I assume you are referring to the "Query cost (relative to batch)". While SQL Server displays this information in a very suggestive way, you can never rely on it. This number is helpful to sql server internally when comparing different execution plans for the same query. However it is absolutely useless when comparing two nonidentical queries. It is best to just ignore this value when talking about performance. Use information you can get back from STATISTICS IO and STATISTICS TIME instead.

UPDATE to address your questions:

1) To retrieve the rows for any query, SQL Server can always execute a scan. In the right circumstances a seek can be much faster. I was describing what SQL Server had to do to execute a seek on the audit table to get the rows >0. If SQL Server thinks that would result in more reads then a scan (as in this case), it is opting for the scan.

2)To make the merge join hint work you need to also tell the optimizer that there actually can't be a value <0 in the table. In your example you could do that like this:

ALTER TABLE sometable ADD CONSTRAINT sometable_id_upzero CHECK  (id>0);

Because of the existing foreign key this constraint applies to both tables. If you now execute

SELECT TOP 50000
        a.id sometableid,
        a.value,
        b.id auditid,
        b.auditvalue
FROM    sometable a
INNER MERGE JOIN audit b
        ON a.id = b.sometable_id;

SQL Server will use this execution plan:

enter image description here

That is the one we are aiming for. It is still using the filter_null_audits index, but allowing for the merge join now. Not sure why this constraint would change if the MERGE join could be used; it seems to be an optimizer limitation. Again, filtered indexes are a fairly advanced option for the optimizer and the implementation might not be complete yet.

3) The advice about not trusting the relative query cost is general in nature and did not imply that the numbers are actually wrong for this particular query. I can easily construct two queries where one shows a relative cost of 99% but the other one actually runs a hundred times slower. If you have an example at hand with correct relative costs, that just means you got lucky. :)

Hope that clarified things.

Best Answer

Related Solutions

Sql-server – Query Performance Issue

Sql-server – filtered index null and > 0 disparity — need explanation

Related Question