troubleshooting Question

Why would ANSI_NULLS ON cause performance issues?

Avatar of HubertFarnsworth
HubertFarnsworthFlag for United States of America asked on
Microsoft SQL Server
3 Comments2 Solutions734 ViewsLast Modified:
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!
ASKER CERTIFIED SOLUTION
Qlemo
"Batchelor", Developer and EE Topic Advisor
Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros