Create view with no outer joins

Question

Create view with no outer joins

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 1

ChrisM@Work (8/28/2012)
jdbrown239 (8/27/2012)
Sorry for the initial messy post.
I think you guys are right. I have tried writing the view without joins and the result set is never the same. I will look at indexing the tables.
Thanks!
Indexing won't help the WHERE clause, it isn't SARGable (the datetime functions prevent SQL Server from using any index on c.CTS_LAST_SAVED_DATE). If you remove those functions from c.CTS_LAST_SAVED_DATE, you may get a welcome performance boost:
WHERE c.CTS_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND c.CTS_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE())

I was able to get an index seek with a DATEADD in a where clause:

SELECT * FROM AF_A_TEST1

WHERE MESSAGEDATE = DATEADD(MINUTE,1,CONVERT(DATETIME,'2011-06-19 14:36:43.237'))

edit: as Steve Thompson-454462 pointed out, this was actually what ChrisM was doing, I had misread it so my apologies!

/edit

so the rule is probably a bit more subtle than "no datetime functions in a where clause".

Here's another example I found on another website of a suggested fix, from:

http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable

Bad: Select ... WHERE DateDiff(mm,OrderDate,GetDate()) >= 30

Fixed: Select ... WHERE OrderDate < DateAdd(mm,-30,GetDate())

The common situation seems to be that in this case, you don't surround the TABLE COLUMN NAME with date functions.

Steve Thompson-454462 SSCrazy Points: 2624 More actions · Answer 2

patrickmcginnis59 (8/29/2012)
The common situation seems to be that in this case, you don't surround the TABLE COLUMN NAME with date functions.

I believe that that's pretty much what ChrisM said in his post; he didn't say no Date functions at all, he simply said that wrapping them around a data column made that condition un-SARGable. Note that he "fixed" the OP's clause by doing exactly what your post suggests: wrapping the functions around the GETDATE side of the clause, rather than the side with the table column.

So, general rule: if you apply a (non-deterministic???) function to a column in a filtering condition, the optimizer will not be able to use an index while evaluating that condition (since the index is built on the original data, not the result of the function).

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 3

patrickmcginnis59 (8/29/2012)
The common situation seems to be that in this case, you don't surround the TABLE COLUMN NAME with date functions.

^^^ This is right.

Comparing a column value to the result of a function applied to a constant will not necessarily preclude SQL Server's use of an index on that column to execute a query. This can be OK:

WHERE OrderDate <= DATEADD(month, -6, getdate())

Comparing the result of a function applied to a column value to a constant will generally preclude SQL Server's use of an index on that column to execute a query. This usually will be very bad for performance because SQL Server cannot use an index on the OrderDate column:

WHERE DATEADD(month, 6, OrderDate) <= DeliveryDate

Jason Wolfkill