Post reply

ANSI_NULLS and Performance

  • 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

  • June 11, 2008 at 10:27 am

    #827390

    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".

  • June 11, 2008 at 11:47 am

    #827438

    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

  • June 12, 2008 at 10:18 am

    #827976

    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

  • July 4, 2008 at 10:03 am

    #838395

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

    Thanks to all,

    Bye

  • July 4, 2008 at 10:13 pm

    #838466

    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)
    Intro to Tally Tables and Functions

  • July 5, 2008 at 11:47 am

    #838536

    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]

  • July 7, 2008 at 3:32 am

    #838740

    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.

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

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