How to avoid spill data to tempdb

avatar image By dk2015evm 0 asked Sep 24, 2015 at 06:12 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

Working from the anonymized query text:

 SELECT DISTINCT
         ( Column1 ) AS Column2 ,
         Function1(Column3) AS Column4
 FROM    Object1 Object2
         INNER JOIN Object3 Object4 ON Object2.Column5 = Column6
 WHERE   Column7 = ?
         AND LEFT(Column8, ?) = ? 
        GROUP BY Column1

...it's tricky to work out which column belongs to which table, but assuming the tables in question look something like this:

 CREATE TABLE #Object1
 (
     Column1 integer NULL,
     Column5 integer NULL,
     Column7 integer NULL,
     Column8 varchar(100) NULL,
 );
 
 CREATE TABLE #Object3
 (
     Column3 integer NULL,
     Column6 integer NULL
 );

...you can avoid the Sort completely by creating this index:

 CREATE INDEX i
 ON #Object1 (Column7, Column1)
 INCLUDE (Column5, Column8);

This index allows a seek on the equality predicate on Column7, producing rows in Column1 order as required by the Stream Aggregate for the grouping operation. This is the main optimization. It seems like a suitable index for seeking already exists on the second table, something like:

 CREATE INDEX i
 ON #Object3 (Column6)
 INCLUDE (Column3);

Given these indexes, the plan changes to:

New plan shape

From a style point of view:

  1. The DISTINCT is redundant here, given the GROUP BY clause

  2. You should use aliases on all column references in the query

  3. The predicate using LEFT should be rewritten as a LIKE

sp.png (21.7 kB)
avatar image By SQLkiwi ♦ 6.6k answered Sep 26, 2015 at 11:30 AM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x166
x6
x1

asked: Sep 24, 2015 at 06:12 PM

Seen: 76 times

Last Updated: Sep 26, 2015 at 11:30 AM