CODEWITHSUNDEEP
sqlsybase
Svip
Svip

Reputation: 3128

Is dateadd() slow in on-conditions compared to where-clauses?

I have a situation in a select, where it gets faster if I move dateadd() from the on-condition to the where-clause.

But sometimes it may not be possible to move it from the on-condition and to where-clause. My solution was to move the dateadd() from the on-condition to a temporary table instead, and that sped up the entire stored procedure.

But I am left wondering; can it really be true that dateadd() is slower in an on-condition than elsewhere?

Upvotes: 1

Views: 875

Answers (1)

gbn
gbn

Reputation: 432271

I'll answer with some references for SQL Server unless I can find exact Sybase references but all Query Optimisers work similarly

To start, a DATEADD function on a predicate invalidates index usage (see number 2 here).

The ON clause is form of predicate of course (think of the old implicit-JOIN-in-WHERE syntax) so the same applies.

Now, queries honour a logical processing step (if not actual, that's why "Query Optimisers" are called so). ON before WHERE is one of them.

With DATEADD in the WHERE clause, it is a residual filter because the main work has been done in the ON clause to restrict rows. If the DATEADD is in the ON clause, it gets processed "sooner" than the WHERE clause.

This is as per Sybase JOIN docs state

...the optimizer can only consider indexes on column names. Any type of operator or expression in combination with the column name means that the optimizer does not evaluate using an index on the column as a possible access method. If the columns in the join are of incompatible datatypes, the optimizer can consider an index on only one of the columns.

The query processing order is hinted at in this Sybase doc

The same thing applies to outer table filters in LEFT JOINS, really: in this case the WHERE clause is too late after the LEFT JOIN. Or why NOT EXISTS almost always beats LEFT JOIN .. IS NULL. See this for Sybase OUTER JOINS

DATEADD itself isn't the problem: it is the logical query processing order that makes it appear one

Upvotes: 3

Related Questions