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!
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.
1 Like
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!
Nevermind! looked up sargable...thanks!!
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
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!