SQLTeam.com | Weblogs | Forums

Huge Performance Difference - 2 WHERE methods


#1

Hello!

New to SQL. Quick question. Querying fact_revenue table. Only want 2016 data. We have a "year_month" dimension.

If if use:

WHERE LEFT(year_month,4) = 2016

its 10x slower than using:

WHERE year_month >= 201601

Can anyone explain why? There are 840 M records in the table.

Thanks!


#2

When you have a function call on the left side of the operator (you have LEFT(....) = ..., the query is not SARGable, which means that SQL will not use any indexes. I suppose you have an index on the year_month column which is why the second query is much faster.


#3

Thanks @gbritton! What does it mean to have an index on the column? I can google if you dont want to explain. I appreciate your initial response!


#4

Nevermind! looked up sargable...thanks!!


#5

Btw, those two conditions are not exactly equivalent, since the second could pick up 2017.

The sargable equivalent of:
WHERE LEFT(year_month,4) = 2016
is:
WHERE year_month >= 201601 AND year_month < 201701


#6

Hey @ScottPletcher thanks for the response! Somehow when I run that second query I am still getting 2016 data. Perhaps its how our database is set up!