2

After taking an advanced T-SQL performance/query tuning class, something that I thought I remembered hearing was that you can speed up some queries just a little bit if you put your date(time) filters first.

Ex:

WHERE
    RunDate = '12/1/2015' AND
    OtherFilters = etc...

But does this really only count if I have indexes in place on these columns I filter on for this table?

So to add to this just a little, should I be building my filters off of the indexes on any tables referenced in the query? Such that my first filters of the query are based on my indexes?

Ex:

WHERE
    ID > 1000 AND
    RunDate <= '1/1/206' AND
    OtherFilters = etc...

Where ID and RunDate are part of my indexes/primary key.

gh0st
  • 1,653
  • 3
  • 27
  • 59
  • 3
    [Does order of where clauses matter in SQL](http://stackoverflow.com/questions/11436469/does-order-of-where-clauses-matter-in-sql) – Alex K. Jun 08 '16 at 16:31
  • 1
    Hopefully you mis remembered rather than someone actually teaching that. – Martin Smith Jun 08 '16 at 16:36
  • 1
    Additional homework: Study up on [covering index](http://www.dbadiaries.com/sql-server-covering-index-and-key-lookup/). Note that SQL Server 2005 and later versions support [included columns](http://msdn.microsoft.com/en-us/library/ms190806.aspx) as well as composite indexes. – HABO Jun 08 '16 at 17:21
  • If you are interested in additional study there are several related topics. Once a database engine builds a query plan to execute a query, it may cache it for quick reuse. _Plan caching_ can become a bad thing. If the query included parameters, the cached plan may suffer from _parameter sniffing_, causing it to be far from optimal for different parameter values. Similarly, _index statistics_ tend to change over time and a cached plan may become inefficient. Schema changes, e.g. adding an index, may not provide a benefit without updating the query plan. And so on. [Ref](http://xkcd.com/1691/). – HABO Jun 08 '16 at 20:01

2 Answers2

2

TSQL is just a logical representation

The query optimizer will set the actual execution order that is most efficient
It messes up some times but for the most part it is spot on

If you have a clustered PK on ID then this will typically be done first

Appears even the OP is confused about the question
Can only answer the stated question

But does this really only count if I have indexes in place on these columns I filter on for this table?

  • The order in the where does not matter for columns with indexes

  • The order in the where does not matter for columns without indexes

  • The order in the where does not matter

paparazzo
  • 44,497
  • 23
  • 105
  • 176
2

The order of filters in WHERE clause does not matter. As long as you have index on the fields, SQL Server knows how to use your filters.

Assume you have index on (ID, RunDt) and you have both ID and RunDt in your WHERE clause. SQL Server first filters the data on ID and then from that subset rows, will filter on RunDt.

This scenario may change if you have other indexes depends on selectivity of your data.

Also if you have clustered index on RunDt, SQL will first filter on RunDt and then ID.

You don't need to worry about the order of your filters in WHERE clause, as long as you have the right order of columns in your index definition.

FLICKER
  • 6,439
  • 4
  • 45
  • 75
  • Filter is NOT limited to indexes. The optimizer will also optimize columns without indexes. – paparazzo Jun 08 '16 at 16:34
  • 1
    @Paparazzi I do not think it was FLICKER's intention to imply that filters are limited to indexes. I think FLICKER was saying that the SQL Server knows how to optimize your filters, even if there is an index on the columns referenced and the filters are in a different order. – Trisped Jun 08 '16 at 16:37
  • @Paparazzi, I don't get what you mean. are you talking about statistics? – FLICKER Jun 08 '16 at 16:39
  • @Paparazzi but asking the optimizer to do this adds processing time, right? So the most efficient way would be to create indexes that touch most (if not all) the columns I filter on. – gh0st Jun 08 '16 at 16:40
  • "As long as you have index on the fields, SQL Server knows how to use your filters." SQL has to know how to filter without indexes and it also optimize those filters. – paparazzo Jun 08 '16 at 16:40
  • @gh0st Apples and oranges. The optimizer will take time to create a plan period - indexes or not. Indexes is not the same as more efficient. Indexes take space and slow down insert and update. – paparazzo Jun 08 '16 at 16:45