Article ID: 2000395 - View products that this article applies to.
Consider the following scenario:
You configure a linked server from either SQL Server 2005 or SQL Server 2008 to a third party database server like Oracle using their OLEDB provider.
In this scenario, if you execute a distributed query using four-part naming convention against the remote server you may notice performance issues, if the statement contains a WHERE clause that uses a string literal for date time columns.
This behavior is by design. SQL Server does not remote out the where clause to the third party provider when string literals are used for datetime columns thereby generating an execution plan that fetches the entire table from the remote data source and the WHERE clause applied locally. This behavior could therefore manifest as a serious performance issue at both SQL Server and network level especially when the remote table contain large number of rows.
Use one of the following procedures to remote the string literal for datetime columns to third party OLEDB providers.
Example query: SELECT * FROM OracleLinkedServer..SCHEMA.DATETABLE WHERE D1 = '1/1/2009'
Resulting execution plan:
This can cause a performance issue since the entire table from the remote server has to be fetched.
declare @d datetime
CREATE PROCEDURE OraDateProc @d datetime as
SELECT * FROM OPENQUERY(remote_server_name, SELECT * FROM SCHEMA.DATETABLE WHERE D1 = '1/1/2009')
SELECT * FROM OracleLinkedServer..SCHEMA.DATETABLE WHERE D1 =(select date_literal from tblDateliterals where date_literal = (select convert(datetime,'1/1/2009'))
Note: In the above example, tblDateliterals is that table with one columns date_literal that contains all the possible values of date literals that you need to use in your application. The above workaround only works if you use the convert function in the sub query.
Article ID: 2000395 - Last Review: September 14, 2009 - Revision: 6.0