I have a stored procedure that populates the temp table #employee_benefits with a list of Ids. This table ends up being roughly 10,000 rows long. The query below then selects from a table called EmployeeBenefitData that has around 4 million rows.

SELECT  ebd.EmployeeBenefitDataId, ebd.EmployeeBenefitId, ebd.[DataDefinitionId]
FROM    #employee_benefits eb
INNER JOIN EmployeeBenefitData ebd ON eb.EmployeeBenefitId = ebd.EmployeeBenefitId

The bottle neck was the index scan on the EmployeeBenefitData table. It did the index scan first and then joined it to the temp table. The temp table was acting as a filter meaning that scanning all the data before the join was very inefficient. I added the following code to change the scan to a seek and dramatically cut down the amount of reads needed.

DECLARE @MinEmpBenId INT, @MaxEmpBenId INT

SELECT @MinEmpBenId = MIN(EmployeeBenefitId), @MaxEmpBenId = MAX(EmployeeBenefitId)
FROM #employee_benefits

SELECT  ebd.EmployeeBenefitDataId, ebd.EmployeeBenefitId, ebd.[DataDefinitionId],
        dd.TypeId, dd.DataDefinitionId, dd.Name, ebd.[Value], ebd.[Date], ebd.[Text]
FROM    #employee_benefits eb
INNER JOIN EmployeeBenefitData ebd ON eb.EmployeeBenefitId = ebd.EmployeeBenefitId
INNER JOIN DataDefinition dd ON ebd.DataDefinitionId = dd.DataDefinitionId
WHERE   ebd.EmployeeBenefitId >= @MinEmpBenId AND ebd.EmployeeBenefitId <= @MaxEmpBenId

It makes a massive difference in the client stats

Total execution time 74, 1794
Wait time on server replies 11, 11

My question is: Is this good practice? And why doesn't the optimiser do this?

UPDATE I should have mentioned that the temp table has a clustered index on EmployeeBenefitID

  • Does the optimizer have up-to-date stats? If you generate an actual, post-execution query plan, do the estimated and actual rows match for every operator? – Aaron Bertrand Oct 29 '14 at 17:32
  • Good point I'll take a quick look. I do know the reads were much smaller – James Anderson Oct 29 '14 at 17:34
  • 1
    Is there an index on your temp table? If you have more than a couple thousand rows it's probably worth it to index a JOIN key. – JNK Oct 29 '14 at 20:05
  • Sorry for the delay. The cardinality of the original query looks good with actual rows slightly lower than estimated on each operator. Tot Exec time has gone from 1451 to 73 when adding the max and min. It just doesn't seem right to me because I thought SQLServer would do this it's self. – James Anderson Oct 30 '14 at 9:01
  • 1
    It would be interesting to know if any RDBMSs actually do this. Seems like a quite simple optimisation in the event that the joining column is indexed and thus the relevant range can be found cheaply. a similar observation was found here but I couldn't find any discussion on costs and benefits on Connect or academic literature. Seems in the same kind of vein as "sideways information passing" though. – Martin Smith Nov 5 '14 at 0:11

Is this good practice?

In this circumstance I would say yes. I'd probably also add an OPTION (RECOMPILE) to let it "sniff" the variable values. The optimal plan will likely vary dependant on the proportion of rows in the larger table that match this range.

It provides a potentially useful extra path to the optimiser and it is not something that the query optimiser ever does by itself as far as I know. The closest thing to it is that with a merge join it will stop processing an input when either one is finished. Thus meaning that it potentially avoids a full scan.

The only downside that springs to mind would be if the calculation of the min/max range values itself might be expensive (but this should be very cheap if the table you are using as a filter is indexed on that column).

I created two test tables

CREATE TABLE EmployeeBenefitData(EmployeeID INT PRIMARY KEY);

CREATE TABLE FilteredEmployee(EmployeeID INT PRIMARY KEY);

And loaded EmployeeBenefitData with integers from 1 to 4,000,000 (6,456 pages)

And FilteredEmployee with integers from 2,000,000 AND 2,010,000 (19 pages)

And then ran 6 queries of the following form

        @E2 INT

SELECT @E1 = FE.EmployeeID,
       @E2 = EBD.EmployeeID
FROM   FilteredEmployee FE
       INNER LOOP JOIN EmployeeBenefitData EBD
         ON FE.EmployeeID = EBD.EmployeeID

The 6 permutations were made up by inverting the order of the two tables and trying all three join types LOOP, MERGE, HASH.

Results were as follows

| Left Table | Right Table | Join  | EBD Scan Count | EBD reads | FE Scan Count | FE reads |
| FE         | EBD         | Loop  |              0 |     30637 |             1 |       19 |
| EBD        | FE          | Loop  |              1 |      6456 |             0 |  8250009 |
| FE         | EBD         | Merge |              1 |      3257 |             1 |       19 |
| EBD        | FE          | Merge |              1 |      3257 |             1 |       19 |
| FE         | EBD         | Hash  |              1 |      6456 |             1 |       19 |
| EBD        | FE          | Hash  |              1 |      6456 |             1 |       19 |

The above figures illustrate the point about merge join as it "only" scans just over half the larger table. It still read all the rows from 1 to 1,999,999 first though and discarded them.

Repeating the experiment with a WHERE EBD.EmployeeID BETWEEN 2000000 AND 2010000 gave the following.

| Left Table | Right Table | Join  | EBD Scan Count | EBD reads | FE Scan Count | FE reads |
| FE         | EBD         | Loop  |              0 |     30637 |             1 |       19 |
| EBD        | FE          | Loop  |              1 |        21 |             0 |    20636 |
| FE         | EBD         | Merge |              1 |        21 |             1 |       19 |
| EBD        | FE          | Merge |              1 |        21 |             1 |       19 |
| FE         | EBD         | Hash  |              1 |        21 |             1 |       19 |
| EBD        | FE          | Hash  |              1 |        21 |             1 |       19 |

The only query that didn't benefit from the additional range predicate is the one where the larger table was on the inside of a nested loops join.

This is of course not surprising as that plan (plan 1 below) is driven by repeated index seeks using the values from FilteredEmployee.

enter image description here

Plan 1 was also the one chosen "naturally" by the optimiser without the range predicate. With the range predicate in place it chose a different merge join plan seeking into the relevant index range without scanning unnecessary rows and costed that as significantly cheaper (plan 2)

  • Any idea how this can be achieved if my table is not indexed on the column and adding the index is not an option? – Aditi Nov 13 '14 at 9:54
  • @Aditi - Not really sure what you are asking. If the small table isn't indexed on the joining column then you can still find the max and min values first, albeit, at greater cost which may make it not worth doing (especially if the range would be large). If the bigger table isn't indexed on the joining column then knowing the min and max values doesn't help as you have no way of efficiently seeking to just that range anyway. – Martin Smith Nov 14 '14 at 14:45
  • 1
    Just to note: Adding a CHECK constraint to the table will result in the desired sort of sideways logic being applied as well. – Paul White Nov 18 '14 at 8:18

I've seen the query planner do similar things with index-less temporary tables, even sometimes when the number of rows in the temporary table is small.

Try adding an index that covers the columns you are joining and filtering on, to see if the planner uses the stats from these to notice it can achieve the goal a more efficient way.

Additionally: if you do have appropriate indexes on all the tables being joined in, you could add an index hint (adding WITH(INDEX(<index_name>)) after the from clause) to make the query planner go a certain way around, though that does make your views/procs dependant on index names and forces they query planner to go a particular route when it might have chosen a better one later as your data grows/changes.

  • The temp table has a clustered index on the employeeid column that I'm joining on. – James Anderson Oct 30 '14 at 14:16
  • 1
    You should add detail like that to your question. Diagnosing query performance questions is pretty "guessworky" when you don't have full details of the table and key/index stricture. An option that might be cleaner than your extra code is to add an index hint (adding WITH(INDEX(<index_name>))` after the from clause) though that does make your views/procs dependant on index names and *forces * they query planner to go a particular route when it might have chosen a better one later as your data grows. – David Spillett Oct 30 '14 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.