Why would ANSI_NULLS ON cause performance issues?

HubertFarnsworth
HubertFarnsworth used Ask the Experts™
on
Good Day!

I understand that ANSI_NULLS OFF is being deprecated, perhaps in SQL Server 2012. This concerns me only because we have experienced serious performance problems when ANSI_NULLS are ON. We have taken procedures compiled with ANSI_NULLS ON and they perform very poorly. When we recompile the exact same SP with ANSI_NULLS OFF, it is very fast. So, when the day comes that ANSI_NULLS is always ON, I fear my system will slow to a crawl.

I am sure rewriting the SQL is the answer, but I cannot figure out why ANSI_NULLS ON cause a performance issue, in the first place.

None of my SQL uses straight joins on nullable columns. We always use code like IsNull( TableA.ColA, '!NULL!') = IsNull( TableB.ColA, '!NULL!').

So, why would the ANSI_NULLS setting affect performance? Should we stop using IsNull in our joins and just do a straight join on nullable columns or use the ugly ((TableA.ColA = TableB.ColA) OR (TableA.ColA IS NULL and TableB.ColA IS NULL))?

Thanks in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
Only explanation I can think of is that with ANSI NULLs off, the join condition is simplified (the isNull removed), because NULL = NULL is valid comparision, allowing for direct index usage. With ANSI NULLS on, the IsNull needs to be applied, rendering indexes useless.
Top Expert 2012
Commented:
Yes, that is exactly the reason.

Author

Commented:
Thanks...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial