One more reason to to understand query plans, not directly performance related

re: One more reason to to understand query plans, not directly performance related

SQLChap — Wed, 23 Jan 2013 12:49:51 GMT

I've seen that issue with dateadd happening even when you have the where clause in place. The same reason in that it was completing the dateadd function first before computing the where criteria.

re: One more reason to to understand query plans, not directly performance related

drsql — Wed, 23 Jan 2013 16:30:16 GMT

SQLChap: Good point that I will add tonight when I am at my writing computer :) One thing to note is that putting the criteria in the JOIN criteria is probably a good bit safer than in the WHERE clause. In the JOIN criteria, it is defining the set and is definitely going to be applied as you expect. In the WHERE clause, other JOINs (particularly any OUTER JOINS) could be dictating that the criteria not be applied until later in the plan.

Thanks!

re: One more reason to to understand query plans, not directly performance related

Russell Tye — Sat, 26 Jan 2013 21:13:42 GMT

Hi Louis,

Thanks for sharing. That was a great demo. I started reading Grant Fritchey's book about two weeks. I strongly recommend it also. It should be required reading for all SQL developers DBAs.

re: One more reason to to understand query plans, not directly performance related

Tom — Sun, 27 Jan 2013 12:13:12 GMT

Hi Russell,

Which of Grants book ?

re: One more reason to to understand query plans, not directly performance related

Richard — Mon, 28 Jan 2013 18:15:53 GMT

Couldn't you use a CASE clause instead?

select *,

case when date.dateValue < '9999-12-31' then dateadd(day, 1, date.dateValue) end

from ExampleTable

join date

on date.dateValue = ExampleTable.dateValue

re: One more reason to to understand query plans, not directly performance related

drsql — Mon, 28 Jan 2013 18:58:12 GMT

Richard:

Good point, that too would solve the issue with the overflow, would produce the same results, though the same query plan. I would probably try the JOIN ON criteria first if I were dealing with an issue as it hits at the root of the problem, but in any case, the plan can help guide you the "best" solution in your case.

re: One more reason to to understand query plans, not directly performance related

Don — Wed, 13 Feb 2013 19:02:38 GMT

A scalar function operates on 1 row at a time. It is much better to operate on sets of data instead of 1 row at time. This can be done by generating a temp or table variable with a computed column containing the data in the form you want. Then you can select off that table. By removing the row processing you increase performance.