Article ID: 197456
Article Last Modified on 2/23/2004
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft OLE DB 2.0
This article was previously published under Q197456
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: kbinfo KB197456
