ANSI settings

  • Hi All,

    There is an application that uses DSN. The connection type is ODBC. In the DSN the ANSI settings are set to ON and on database level they are all OFF (http://msdn.microsoft.com/en-us/library/ms175088(v=sql.105).aspx). I'm aware that changing them can cause troubles to the application. The application is a "black box" and does not use stored procedures, i.e. only in-code queries.

    Do these differences in the settings cause less reuse of the query plans?

    BR

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • Just my two cents.

    The execution plan is saved within the cashe allocated to SQL Server instance. It is not related to the other setting outside SQL Server.

    The ANSI setting affects the query durations. Here is a link. May it help.

    http://msdn.microsoft.com/en-us/library/ms177259.aspx

  • SQL ORACLE (4/3/2013)


    Just my two cents.

    The execution plan is saved within the cashe allocated to SQL Server instance. It is not related to the other setting outside SQL Server.

    The ANSI setting affects the query durations. Here is a link. May it help.

    http://msdn.microsoft.com/en-us/library/ms177259.aspx

    However the ODBC overwrites the default database settings. The questions is "Is the ODBC overwrite enough for reusing the already existing plans of queries".

    Thank you

    Igor Micev,My blog: www.igormicev.com

  • The execution plan is saved by SQL Server after the first run. The plan will be changed if you changed your query.

    If you run the same query twice, you can always find that the first run always takes more time. It is an indication that your execution plan is saved by SQL Server.

  • So it seems that according to table on http://msdn.microsoft.com/en-us/library/ms175088(v=sql.105).aspx the default settings does not matter because it all depends on the ODBC connection settings which overwrite most of the settings.

    Igor Micev,My blog: www.igormicev.com

  • SQL ORACLE (4/3/2013)


    The execution plan is saved by SQL Server after the first run. The plan will be changed if you changed your query.

    If you run the same query twice, you can always find that the first run always takes more time. It is an indication that your execution plan is saved by SQL Server.

    Hi I know about this.

    Igor Micev,My blog: www.igormicev.com

Viewing 6 posts - 1 through 5 (of 5 total)

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