ANSI_NULLS and Performance

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:
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
With ANSI_NULLS OFF (my current setting):
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. With ANSI_NULLS ON:
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.
6 logical reads against 7071 logical reads on my current settings [:O].
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

Are the users of application using DBlibrary or OLEDB ?
If they are using Microsoft OLEDB or ODBC drivers the default option is SET ANSI NULL ON when they access in such cases this settings would return zero rows for NULL values as you are aware.
Even after you turn the ANSI to OFF , the user connecting using the OLDEB will turn it back ON by default.If the users are using DB library with ANSI NULL OFF then I guess you have catch 22 since you have app code which does not ahere to SQL 92 standard.
In relation to indexes used in predicates I have no clue how it relates to this setting , will need to dig deeper.
Reference
http://doc.ddart.net/mssql/sql70/set-set_5.htm
Cheers
Sat

Hi techbabu303,
and thanks for your reply.
[quote user="techbabu303"]
Are the users of application using DBlibrary or OLEDB ?
[/quote]


The application in version 2.0 uses DB-Library, while the application in version 3.0 uses OLE DB. Users complain of slow query only with version 3.0 and analyzing the execution of queries with SQL Profiler showed the situation that I described.

We arrived to solving the problem [], summarized in this article.

Thanks to all!

Thx for posting the article but the issue it is foreign language I dont undertsand. Do you have anything in English ?
-Sat

<p>Translation using google tool bar hope it is right .........&nbsp;</p><p>&nbsp;</p><p>When the option ANSI_NULLS Affects optimizer and performance</p><p><o>&nbsp;</o></p><p>A topic already addressed earlier, in post ANSI_NULLS by defaultifferences between OLE DB and BDE, to add some considerations on the behaviourdell'optimizer (and hence performance) when the value assigned to theANSI_NULLS is different from default. <br>Let's assume that you have removed all dependencies DB-Library to replace BDEwith OLE DB in an application written in <st1lace w:st="on">Delphi</st1lace>.<br>To maintain compatibility of operators equals (=) and other (&lt;&gt incomparison with the value NULL, immediately after establishing the connectionwith SQL Server we set off to the option ANSI_NULLS (SET ANSI_NULLS OFF). <br>After this setting users complain about a slowdown in the query. </p><p><br>Let's assume the following scenario: a user requires the extraction of somedata relating to sales orders, the table dbrderHeader stores tested customerorders, while the table dbrderDetail stores lines. We have thereforestructures of the following data: <br>• Table dbrderHeader (250,000 lines) with the following structure:<!--[if gte vml 1]><v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" oreferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"> <v:stroke joinstyle="miter"/> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"/> <v:f eqn="sum @0 1 0"/> <v:f eqn="sum 0 0 @1"/> <v:f eqn="prod @2 1 2"/> <v:f eqn="prod @3 21600 pixelWidth"/> <v:f eqn="prod @3 21600 pixelHeight"/> <v:f eqn="sum @0 0 1"/> <v:f eqn="prod @6 1 2"/> <v:f eqn="prod @7 21600 pixelWidth"/> <v:f eqn="sum @8 21600 0"/> <v:f eqn="prod @7 21600 pixelHeight"/> <v:f eqn="sum @10 21600 0"/> </v:formulas> <vath o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"/> <o:lock v:ext="edit" aspectratio="t"/></v:shapetype><v:shape id="_x0000_i1025" type="#_x0000_t75" alt="dbrderHeader" style='width:243.75pt;height:158.25pt'> <v:imagedata src="file:///COCUME~1WINXP~1LOCALS~1Tempmsohtml11clip_image001.png" o:href="http://community.ugiss.org/blogs/sgovoni/Immagini/OrderHeader%28ANSI_NULLS%29.bmp"/></v:shape><![endif]--><!--[if !vml]--><img src="file:///C:/DOCUME%7E1/WINXP%7E1/LOCALS%7E1/Temp/msohtml1/01/clip_image002.jpg" style="height: 211px; width: 325px;" alt="dbrderHeader" title="dbrderHeader" v:shapes="_x0000_i1025" height="211" width="325"><!--[endif]--><o></o></p><ul type="disc"><li class="MsoNormal" style=""><span style="">&nbsp;</span>Table dbrderDetail (250,000 lines) with the following structure::<o></o></li></ul><p><!--[if gte vml 1]><v:shape id="_x0000_i1026" type="#_x0000_t75" alt="dbrderDetail" style='width:180.75pt;height:84pt'> <v:imagedata src="file:///COCUME~1WINXP~1LOCALS~1Tempmsohtml11clip_image003.png" o:href="http://community.ugiss.org/blogs/sgovoni/Immagini/OrderDetail%28ANSI_NULLS%29.bmp"/></v:shape><![endif]--><!--[if !vml]--><img src="file:///C:/DOCUME%7E1/WINXP%7E1/LOCALS%7E1/Temp/msohtml1/01/clip_image004.jpg" style="height: 112px; width: 241px;" alt="dbrderDetail" title="dbrderDetail" v:shapes="_x0000_i1026" height="112" width="241"><!--[endif]--><o></o></p><p style="margin-left: 0.75in; text-indent: -0.25in;"><!--[if !supportLists]--><span style="font-family: Symbol;"><span style="">·<span style="font-family: 'Times New Roman'; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span></span></span><!--[endif]-->Query data extraction (simplified):</p><p>Select count (*) From dbrderHeader OH Left Outer Join dbrderDetail ORON OD.OrderHeaderID = OH.OrderID <br>Where (OH.CustomerID = @ P1) and (OD.OrderHeaderID = @ P2) <br>Confrontiamo hour plans for implementing the query when it is running on aconnection with ANSI_NULLS ON = (default for connections OLE DB - SSMS) andwhen it is running on a connection with ANSI_NULLS = OFF (default forconnections DB-Library). <br>-- Plan for implementing the queries performed with ANSI_NULLS = OFF:<!--[if gte vml 1]><v:shape id="_x0000_i1027" type="#_x0000_t75" alt="Execution Plan ANSI_NULLS = ON" style='width:525pt;height:119.25pt'> <v:imagedata src="file:///COCUME~1WINXP~1LOCALS~1Tempmsohtml11clip_image005.png" o:href="http://community.ugiss.org/blogs/sgovoni/Immagini/Query-Plan-ANSI_NULLS-OFF.bmp"/></v:shape><![endif]--><!--[if !vml]--><img src="file:///C:/DOCUME%7E1/WINXP%7E1/LOCALS%7E1/Temp/msohtml1/01/clip_image006.jpg" style="height: 159px; width: 700px;" alt="Execution Plan ANSI_NULLS = ON" title="Execution Plan ANSI_NULLS = ON" v:shapes="_x0000_i1027" height="159" width="700"><!--[endif]--><o></o></p><p>Table 'OrderHeader'. Scan count 1, logical reads <b>2607</b>, physical reads0, read-ahead reads 0.<o></o></p><p>Table 'OrderDetail'. Scan count 1, logical reads 899, physical reads 0,read-ahead reads 0.<o></o></p><p>-- Plan for implementing the queries performed with ANSI_NULLS = ON:<!--[if gte vml 1]><v:shape id="_x0000_i1028" type="#_x0000_t75" alt="Execution Plan ANSI_NULLS = ON" style='width:494.25pt;height:118.5pt'> <v:imagedata src="file:///COCUME~1WINXP~1LOCALS~1Tempmsohtml11clip_image007.png" o:href="http://community.ugiss.org/blogs/sgovoni/Immagini/Query-Plan-ANSI_NULLS-ON.bmp"/></v:shape><![endif]--><!--[if !vml]--><img src="file:///C:/DOCUME%7E1/WINXP%7E1/LOCALS%7E1/Temp/msohtml1/01/clip_image008.jpg" style="height: 158px; width: 659px;" alt="Execution Plan ANSI_NULLS = ON" title="Execution Plan ANSI_NULLS = ON" v:shapes="_x0000_i1028" height="158" width="659"><!--[endif]--><o></o></p><p>Table 'OrderDetail'. Scan count 1, logical reads 899, physical reads 0,read-ahead reads 0. <br>Table 'OrderHeader'. Scan count 1, logical reads 5, physical reads 0,read-ahead reads 0. <br>&nbsp; <br>In the case of ANSI_NULLS = OFF are not used indexes, but runs a scan of thetables dbrderHeader and dbrderDetail with a consequent increase access tothe pages of memory. <br>Thanks to the valuable contribution of David Mauri, we observe that: <br>"The queries performed on the two connections are no longer equivalent ifthe behaviour of equality is different from the default, then the optimizer cannot apply in the case of ANSI_NULLS = OFF, the same optimizations that mayapply if ANSI_NULL = ON. <br>With a Left Outer Join you can still get NULL values and if the operator equals(=) must give the same results when applied to NULL values, it follows that theimplementation plan must necessarily be different, with the result that in thisIf the indices are no longer used.&nbsp;<o></o></p>

<p>Translation using google tool bar hope it is right .........&nbsp;</p><p>&nbsp;</p><p>When the option ANSI_NULLS Affects optimizer and performance</p><p><o>&nbsp;</o></p><p>A topic already addressed earlier, in post ANSI_NULLS by defaultifferences between OLE DB and BDE, to add some considerations on the behaviourdell'optimizer (and hence performance) when the value assigned to theANSI_NULLS is different from default. <br>Let's assume that you have removed all dependencies DB-Library to replace BDEwith OLE DB in an application written in <st1lace w:st="on">Delphi</st1lace>.<br>To maintain compatibility of operators equals (=) and other (&lt;&gt incomparison with the value NULL, immediately after establishing the connectionwith SQL Server we set off to the option ANSI_NULLS (SET ANSI_NULLS OFF). <br>After this setting users complain about a slowdown in the query. </p><p><br>Let's assume the following scenario: a user requires the extraction of somedata relating to sales orders, the table dbrderHeader stores tested customerorders, while the table dbrderDetail stores lines. We have thereforestructures of the following data: <br>• Table dbrderHeader (250,000 lines) with the following structure:<!--[if gte vml 1]><v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" oreferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"> <v:stroke joinstyle="miter"/> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"/> <v:f eqn="sum @0 1 0"/> <v:f eqn="sum 0 0 @1"/> <v:f eqn="prod @2 1 2"/> <v:f eqn="prod @3 21600 pixelWidth"/> <v:f eqn="prod @3 21600 pixelHeight"/> <v:f eqn="sum @0 0 1"/> <v:f eqn="prod @6 1 2"/> <v:f eqn="prod @7 21600 pixelWidth"/> <v:f eqn="sum @8 21600 0"/> <v:f eqn="prod @7 21600 pixelHeight"/> <v:f eqn="sum @10 21600 0"/> </v:formulas> <vath o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"/> <o:lock v:ext="edit" aspectratio="t"/></v:shapetype><v:shape id="_x0000_i1025" type="#_x0000_t75" alt="dbrderHeader" style='width:243.75pt;height:158.25pt'> <v:imagedata src="file:///COCUME~1WINXP~1LOCALS~1Tempmsohtml11clip_image001.png" o:href="http://community.ugiss.org/blogs/sgovoni/Immagini/OrderHeader%28ANSI_NULLS%29.bmp"/></v:shape><![endif]--><!--[if !vml]--><img src="file:///C:/DOCUME%7E1/WINXP%7E1/LOCALS%7E1/Temp/msohtml1/01/clip_image002.jpg" style="height: 211px; width: 325px;" alt="dbrderHeader" title="dbrderHeader" v:shapes="_x0000_i1025" height="211" width="325"><!--[endif]--><o></o></p><ul type="disc"><li class="MsoNormal" style=""><span style="">&nbsp;</span>Table dbrderDetail (250,000 lines) with the following structure::<o></o></li></ul><p><!--[if gte vml 1]><v:shape id="_x0000_i1026" type="#_x0000_t75" alt="dbrderDetail" style='width:180.75pt;height:84pt'> <v:imagedata src="file:///COCUME~1WINXP~1LOCALS~1Tempmsohtml11clip_image003.png" o:href="http://community.ugiss.org/blogs/sgovoni/Immagini/OrderDetail%28ANSI_NULLS%29.bmp"/></v:shape><![endif]--><!--[if !vml]--><img src="file:///C:/DOCUME%7E1/WINXP%7E1/LOCALS%7E1/Temp/msohtml1/01/clip_image004.jpg" style="height: 112px; width: 241px;" alt="dbrderDetail" title="dbrderDetail" v:shapes="_x0000_i1026" height="112" width="241"><!--[endif]--><o></o></p><p style="margin-left: 0.75in; text-indent: -0.25in;"><!--[if !supportLists]--><span style="font-family: Symbol;"><span style="">·<span style="font-family: 'Times New Roman'; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span></span></span><!--[endif]-->Query data extraction (simplified):</p><p>Select count (*) From dbrderHeader OH Left Outer Join dbrderDetail ORON OD.OrderHeaderID = OH.OrderID <br>Where (OH.CustomerID = @ P1) and (OD.OrderHeaderID = @ P2) <br>Confrontiamo hour plans for implementing the query when it is running on aconnection with ANSI_NULLS ON = (default for connections OLE DB - SSMS) andwhen it is running on a connection with ANSI_NULLS = OFF (default forconnections DB-Library). <br>-- Plan for implementing the queries performed with ANSI_NULLS = OFF:<!--[if gte vml 1]><v:shape id="_x0000_i1027" type="#_x0000_t75" alt="Execution Plan ANSI_NULLS = ON" style='width:525pt;height:119.25pt'> <v:imagedata src="file:///COCUME~1WINXP~1LOCALS~1Tempmsohtml11clip_image005.png" o:href="http://community.ugiss.org/blogs/sgovoni/Immagini/Query-Plan-ANSI_NULLS-OFF.bmp"/></v:shape><![endif]--><!--[if !vml]--><img src="file:///C:/DOCUME%7E1/WINXP%7E1/LOCALS%7E1/Temp/msohtml1/01/clip_image006.jpg" style="height: 159px; width: 700px;" alt="Execution Plan ANSI_NULLS = ON" title="Execution Plan ANSI_NULLS = ON" v:shapes="_x0000_i1027" height="159" width="700"><!--[endif]--><o></o></p><p>Table 'OrderHeader'. Scan count 1, logical reads <b>2607</b>, physical reads0, read-ahead reads 0.<o></o></p><p>Table 'OrderDetail'. Scan count 1, logical reads 899, physical reads 0,read-ahead reads 0.<o></o></p><p>-- Plan for implementing the queries performed with ANSI_NULLS = ON:<!--[if gte vml 1]><v:shape id="_x0000_i1028" type="#_x0000_t75" alt="Execution Plan ANSI_NULLS = ON" style='width:494.25pt;height:118.5pt'> <v:imagedata src="file:///COCUME~1WINXP~1LOCALS~1Tempmsohtml11clip_image007.png" o:href="http://community.ugiss.org/blogs/sgovoni/Immagini/Query-Plan-ANSI_NULLS-ON.bmp"/></v:shape><![endif]--><!--[if !vml]--><img src="file:///C:/DOCUME%7E1/WINXP%7E1/LOCALS%7E1/Temp/msohtml1/01/clip_image008.jpg" style="height: 158px; width: 659px;" alt="Execution Plan ANSI_NULLS = ON" title="Execution Plan ANSI_NULLS = ON" v:shapes="_x0000_i1028" height="158" width="659"><!--[endif]--><o></o></p><p>Table 'OrderDetail'. Scan count 1, logical reads 899, physical reads 0,read-ahead reads 0. <br>Table 'OrderHeader'. Scan count 1, logical reads 5, physical reads 0,read-ahead reads 0. <br>&nbsp; <br>In the case of ANSI_NULLS = OFF are not used indexes, but runs a scan of thetables dbrderHeader and dbrderDetail with a consequent increase access tothe pages of memory. <br>Thanks to the valuable contribution of David Mauri, we observe that: <br>"The queries performed on the two connections are no longer equivalent ifthe behaviour of equality is different from the default, then the optimizer cannot apply in the case of ANSI_NULLS = OFF, the same optimizations that mayapply if ANSI_NULL = ON. <br>With a Left Outer Join you can still get NULL values and if the operator equals(=) must give the same results when applied to NULL values, it follows that theimplementation plan must necessarily be different, with the result that in thisIf the indices are no longer used.&nbsp;<o></o></p>

[quote user="techbabu303"]
Translation using google tool bar hope it is right .........
[/quote]
I was to do the same thing [].
Bye
Sergio