tds_fdw
tds_fdw copied to clipboard
tds-fdw
SET ANSI_NULLS ON/OFF?
We're noticing a tremendous difference in query execution times between a query sent with tds_fdw and the same query executed on SSMS. The difference seems to be the ANSI_NULLS setting whereby SSMS has ANSI_NULLS ON by default and tds_fdw has ANSI_NULLS OFF by default.
By setting ANSI_NULLS OFF on SSMS, the execution timings between tds_fdw and SSMS are the same. With ANSI_NULLS ON in SSMS, the execution timings are 100x faster then tds_fdw.
Is there a current way to set ANSI_NULLS ON via tds_fdw? If not, can this be added?
Please advise
Can you try setting this on the MSSQL side and see if it makes a difference?
ALTER DATABASE [****] SET ANSI_NULL_DEFAULT ON
ALTER DATABASE [****] SET ANSI_NULLS ON
We have set both options ON yet we continue to see the same poor execution plan.
The reason for this relates to the default setting applied to the client connection based on the client library type the caller is using.
(see https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-null-dflt-on-transact-sql?view=sql-server-2017 )
…The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server automatically set ANSI_NULL_DFLT_ON to ON when connecting. The default for SET ANSI_NULL_DFLT_ON is OFF for connections from DB-Library applications.
tds-fdw unfortunately makes use of DB-Library (legacy client library) which inherits ANSI_NULL_DFLT_ON=OFF connection-wide.
Given that the connection-based ANSI_NULL_DFLT_ON setting takes precedence over any prior setting, we will need to find a way to issue a command to set ANSI_NULL_DFLT_ON=ON subsequent to establishing the connection.
Would you be willing to provide a configuration option for choosing the ANSI_NULL_DFLT_ON value via all connections via tds_fdw?
Hi @bradleyf,
I'm not sure if I'll get around to this change, but if anyone else wants to submit a pull request with the change, I'd gladly review it.
