Link to home
Start Free TrialLog in
Avatar of HubertFarnsworth
HubertFarnsworthFlag for United States of America

asked on

Why would ANSI_NULLS ON cause performance issues?

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
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of HubertFarnsworth

ASKER

Thanks...