ANSI_NULLS and Performance

Question

ANSI_NULLS and Performance

sgovoni

Ten Centuries

Points: 1156
More actions
June 10, 2008 at 11:20 am

#122962

Hi,
I am developing an application that uses OLE DB to connect to SQL Server 2000; originally the application was using DB-Library to connect to SQL Server.
To maintain compatibility with operators "=", ">=", etc. in comparison with the values NULL, I set to OFF (immediately after the connection) the database option ANSI_NULLS.
After this setting users complain about slow running query, using SQL Profiler and Query Analyzer I compared the execution plan and the logical reads of slow query and observing this behavior:
[font="Courier New"]SET STATISTICS IO ON
sp_executesql N'SELECT A.idA idA, Sum(B.Qta) Qta, Max(B.Date) Date FROM Table1 B Left Outer Join Table2 A On B.idA = A.id WHERE (B.idE= @P1) And (A.idA= @P2) GROUP BY A.idA', N'@P1 int,@P2 int', 35, 5624[/font]
With ANSI_NULLS OFF (my current setting):
[font="Courier New"]Warning: Null value is eliminated by an aggregate or other SET operation.
Table 'Table1'. Scan count 1, logical reads 7071, physical reads 0, read-ahead reads 0.
Table 'Table2'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0.[/font]
With ANSI_NULLS ON:
[font="Courier New"]Warning: Null value is eliminated by an aggregate or other SET operation.
Table 'Table1'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0.
Table 'Table2'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.[/font]
6 logical reads against 7071 logical reads on my current settings.
With ANSI_NULLS set to OFF the execution plan shows an Index Scan, while with ANSI_NULLS set to ON the execution plan shows a Index Seek ... and I believe it is the difference here.
Do you have any suggestions for improving the performance of queries in a situation like this ?
Thanks in advance.
Sergio

Viewing 8 posts - 1 through 7 (of 7 total)

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

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 1

The "logical read 7071" is the initial read for your query. And, when you ran your query again without DROPCLEANBUFFERS, those data pages are in your cache already, and no need more "logical read".

You can run "DBCC DROPCLEANBUFFERS" between two tests, and get almost the same "logical read".

lalolo666 SSC Enthusiast Points: 158 More actions · Answer 2

I think the problem you have here isn't in relation with ANSI SET.

that warning implies that your query is not completely done. You should to consider NULL values in the WHERE clause for example.

sp_executesql N'SELECT A.idA idA, Sum(B.Qta) Qta, Max(B.Date) Date

FROM Table1 B Left Outer Join Table2 A On B.idA = A.id

WHERE (B.idE= @P1) And (A.idA= @P2)

AND B.idE IS NOT NULL

AND A.idA IS NOT NULL

GROUP BY A.idA', N'@P1 int,@P2 int', 35, 5624

making this change you can SET ANSI on the proper way and to keep the best performance without concerning about "=" operators problems

sgovoni Ten Centuries Points: 1156 More actions · Answer 3

Thanks for your answer.

I added the conditions IS NOT NULL on the fields and trafficking of I/O has decreased drastically, but I still do not understand the difference in execution plans (index scan with high I/O instead of index seek with low I/O). Do you have ideas on this?

In attach:

- Query-without-IS-NOT-NULL.bmp : It represents the query execution plan without conditions IS NOT NULL

- Query-with-IS-NOT-NULL.bmp : It represents the query execution plan with conditions IS NOT NULL

Thanks again.

Sergio

sgovoni Ten Centuries Points: 1156 More actions · Answer 4

We found the solution :D, if you want to know the final: read this article (soon write the English translation :)).

Thanks to all,

Bye

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 5

In the meantime, why don't you just go ahead and tell us what the solution is 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Normally NULL's never match other NULL's, and this is the case when ANSI_NULLS is ON (the default). As far as I know, all indexes are built with this assumption and thus, key column values that are NULL are not included in the indexes (because they should never match anything anyway).

As long as ANSI_NULLS stays ON, this works fine.

However,

When ANSI_NULLS is OFF, then NULL=NULL is True. Therefore, if ANSI_NULLS is OFF and you are searching on a column that allows NULLs and you are searching to match a source that also could be NULL, ...

THEN SQL cannot use Index seeks to resolve these potential matches anymore, -> Because -> the index bucket-tree does not include the NULL key column values.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

sgovoni Ten Centuries Points: 1156 More actions · Answer 7

The example queries (in the first post of this thread) run on two connections (first connection with ANSI_NULLS = ON and second connection with ANSI_NULLS=OFF) are no longer equivalent if the behaviour of equality operator (=) is different from the default, then the optimizer can not apply in the case of ANSI_NULLS = OFF, the same optimizations that may apply if ANSI_NULL = ON.

With a Left Outer Join you can still get NULL values and if the operator equals (=) must give a results when applied to NULL values, it follows that the execution plan must necessarily be different, with the result that in this case the indices are no longer used.