INF: Optimizing Distributed Query with Numeric Predicates

ID: Q197456

The information in this article applies to:

Microsoft SQL Server version 7.0
Microsoft OLE DB, version 2.0

SUMMARY

When you perform a SQL Server 7.0 distributed query with numeric predicates against a data source other than SQL Server, some numeric predicates may not be sent to the remote server for evaluation. This article discusses how to optimize distributed query execution with examples of an Oracle query.

MORE INFORMATION

Microsoft SQL Server version 7.0 provides the ability to perform queries against OLE DB providers. This is done by using the OpenQuery or OpenRowset Transact-SQL functions or by using a query with four-part names including a linked-server name. For example,


   EXEC sp_addlinkedserver 'OraDB',  'Oracle',   'MSDAORA', 'MyServer'
   GO
   -- add a login mapping using sp_addlinkedsrvlogin as needed

   select  * from OraDB..SCOTT.SALES
   where QTY > 100

SQL Server delegates as much of the query execution as possible to an OLE DB provider that supports SQL. There are, however, some operations that SQL Server does not delegate to the provider, because such operations do not have standard SQL syntax and semantics. One such operation is type conversion. This article describes how to determine why a query is not being delegated to the provider and provides techniques to rewrite the query so that it is delegated.

If the query involves arithmetic or Boolean expressions, where the two operands of a comparison or arithmetic operation are of different type, an implicit conversion is done to one of the types before the expression is evaluated. SQL Server does not delegate such queries to providers other than SQL Server. The user can rewrite the query in such a way that data type conversion is not necessary, thereby allowing SQL Server to delegate more of the query to the provider.

Examples

In Oracle, all fixed-point numeric data (integer, numeric, decimal) are represented as one internal type number(p,s) where p and s are the precision and scale respectively. Integers are represented as numeric with a scale of 0. Therefore, any comparisons of such columns against integer constants require a type conversion. For example for the query


   select  * from OraDB..SCOTT.SALES
   where QTY > 100

the predicate QTY > 100 is not sent to Oracle because of the type conversion needed from 100 to QTY because they are of distinct types. This query can be rewritten in one of two ways to make SQL Server delegate the predicate to Oracle:

Change the constant to a type that matches the Oracle column type.


      select * from OraDB..SCOTT.SALES
      where QTY > 100.0

-or-

Change the constant to a parameter of type that matches the Oracle column type.


      declare @var1 numeric(10,0)
      select @var1 = 100
      select * from OraDB..SCOTT.SALES
      where QTY > @var1

This technique can be used in general for any OLE DB provider; the general idea is to determine the type of the column and use a constant or parameter of the same type. The second solution is applicable only if the provider supports parameterized query execution using '?' as parameter markers.

For more information about setting up and using Distributed Queries, search on sp_addlinkedserver, OpenQuery, OpenRowset, and related topics in SQL Server 7.0 Books Online.

For more information about Oracle data types, please refer to the section 'Data Types in Oracle and SQL Server' in the SQL Server 7.0 Books Online, as well as the Oracle documentation.

Additional query words: prodsql


Keywords          : 
Version           : WINDOWS:2.0; WINNT:7.0
Platform          : WINDOWS winnt 
Issue type        : kbinfo

Last Reviewed: April 19, 1999