You may notice performance issues when using date literal values in distributed queries with third party OLEDB providers from SQL server.

Article translations Article translations
Close Close
Article ID: 2000395 - View products that this article applies to.
Expand all | Collapse all

Symptoms

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.

Cause

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.

Resolution

Use one of the following procedures to remote the string literal for datetime columns to third party OLEDB providers.

  1. Configure the value as a datetime parameter in a SQL batch that contains the distributed query.
  2. Configure the value as a datetime parameter in a SQL stored procedure that has a datetime parameter.
  3. Use OpenQuery function instead of a four-part naming convention to send the entire query as is to the remote server.
  4. Create a separate table in the SQL database with a datetime column, populate that with all the possible dates your application might be using and then use a subquery based on that table as a predicate of your WHERE clause as shown in the example under Workaround 4  in the 'More information' section below.

More Information

Example query:  SELECT * FROM OracleLinkedServer..SCHEMA.DATETABLE WHERE D1 = '1/1/2009'

Resulting execution plan:

Filter(WHERE:([OracleLinkedServer]..[SCHEMA].[DATETABLE].[D1]='2009-01-01 00:00:00.000'))                                                                                                 
  |--Compute Scalar(DEFINE:([OracleLinkedServer]..[SCHEMA].[DATETABLE].[ID]=[OracleLinkedServer]..[SCHEMA].[DATETABLE].[ID], [OracleLinkedServer]..[SCHEMA].[DATETABLE].[D1]=[OracleLinkedServer]..[SCHEMA].[DATETABLE].[D1]))
       |--Remote Query(SOURCE:(OracleLinkedServer), QUERY:(SELECT "Tbl1002"."ID" "Col1004","Tbl1002"."D1" "Col1005" FROM "SCHEMA"."DATETABLE" "Tbl1002"))                 

This can cause a performance issue since the entire table from the remote server has to be fetched.

Workaround 1:

declare @d datetime
set @d = '1/1/2009'
SELECT * from OracleLinkedServer..PSS.DATETABLE WHERE D1 = @d

 

Workaround 2:

CREATE PROCEDURE OraDateProc  @d datetime as
SELECT* FROM OracleLinkedServer..PSS.DATETABLE WHERE D1 = @d

 

Workaround 3:

SELECT * FROM OPENQUERY(remote_server_name, SELECT * FROM  SCHEMA.DATETABLE WHERE D1 = '1/1/2009')

 

Workaround 4:

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.

 

Properties

Article ID: 2000395 - Last Review: September 14, 2009 - Revision: 6.0
APPLIES TO
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Evaluation Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Standard
Keywords: 
KB2000395

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com