Optimizing Distributed Queries

Optimizing SQL Database Performance

Optimizing Database Performance

Optimizing Distributed Queries

Microsoft® SQL Server™ 2000 distributed queries allow users to reference remote tables and rowsets as though they are local tables by using SELECT, INSERT, UPDATE, and DELETE statements. Distributed queries cause data to be retrieved across the network when data sources are located on remote computers. Therefore, SQL Server performs two types of optimization specific to distributed queries to improve performance:

  • Remote query execution used with OLE DB SQL Command Providers.

  • Indexed access used with OLE DB Index Providers.

An OLE DB provider is considered to be a SQL Command Provider if the OLE DB provider meets the following minimum requirements:

  • Supports the Command object and all of its mandatory interfaces.

  • Supports DBPROPVAL SQL SUBMINIMUM Syntax, or SQL-92 at Entry level or higher, or ODBC at Core level or higher. The provider should expose this dialect level through the DBPROP_SQLSUPPORT OLE DB property.

An OLE DB Provider is considered to be an Index Provider if the OLE DB provider meets the following minimum requirements:

  • Supports the IDBSchemaRowset interface with the TABLES, COLUMNS and INDEXES schema rowsets.

  • Supports opening a rowset on an index using IOpenRowset by specifying the index name and the corresponding base table name.

  • The Index object should support all its mandatory interfaces: IRowset, IRowsetIndex, IAccessor, IColumnsInfo, IRowsetInfo, and IConvertTypes.

  • Rowsets opened against the indexed base table (using IOpenRowset) should support the IRowsetLocate interface for positioning on a row based off a bookmark retrieved from the index.
Remote Query Execution

SQL Server attempts to delegate as much of the evaluation of a distributed query to the SQL Command Provider as possible. An SQL query that accesses only the remote tables stored in the provider's data source is extracted from the original distributed query and executed against the provider. This reduces the number of rows returned from the provider and allows the provider to use its indexes in evaluating the query.

Considerations that affect how much of the original distributed query gets delegated to the SQL Command Provider include:

  • The dialect level supported by the SQL Command Provider

    SQL Server delegates operations only if they are supported by the specific dialect level. The dialect levels from highest to lowest are: SQL Server, SQL-92 Entry level, ODBC core, and Jet. The higher the dialect level, the more operations SQL Server can delegate to the provider.

    Note  The SQL Server dialect level is used when the provider corresponds to a SQL Server linked server.

    Each dialect level is a superset of the lower levels. Therefore, if an operation is delegated to a particular level, then it is also delegated to all higher levels.

    Queries involving the following are never delegated to a provider and are always evaluated locally:

    • bit

    • uniqueidentifier

    The following operations/syntactic elements are delegated to the dialect level indicated (and all higher levels):

    • SQL Server: Outer join, CUBE, ROLLUP, modulo operator (%), bit-wise operators, string functions, and arithmetic system functions.

    • SQL-92 Entry Level: UNION, and UNION ALL.

    • ODBC Core: Aggregation functions with DISTINCT, and string constants.

    • Jet: Aggregate functions without DISTINCT, sorting (ORDER BY), inner joins, predicates, subquery operators (EXISTS, ALL, SOME, IN), DISTINCT, arithmetic operators not mentioned in higher levels, constants not mentioned in higher levels, and all logical operators.

    For example, all operations except those involving CUBE, ROLLUP, outer join, modulo operator (%), bit-wise operators, string functions, and arithmetic system functions are delegated to a SQL-92 Entry level provider that is not also SQL Server.

  • Collation compatibility

    For a distributed query, the comparison semantics for all character data is defined by the character set and sort order of the local SQL Server. Microsoft SQL Server 2000 supports multiple collations, which can be different for each column; each character value has an associated collation property. SQL Server 2000 interprets the collation property of character data from a remote data source and treats it accordingly. For more information on the collation of remote columns, see Collations in Distributed Queries.

    SQL Server can delegate comparisons and ORDER BY operations on character columns to a provider only if it can determine that:

    • The underlying data source uses the collation sequence and character set of the column.

    • The character comparison semantics follow the SQL-92 (and SQL Server) standard.
  • Following the table in the Collations in Distributed Queries topic, SQL Server will determine a collation for each column. If the remote data source supports that collation, then the provider is considered collation compatible.

  • Other SQL support considerations

    The following SQL syntax elements are not dictated by the SQL dialect levels:

    • Nested query support

      If the provider supports nested queries (subqueries), then SQL Server can delegate these operations to the provider. Because nested query support cannot be automatically determined from OLE DB properties, the system administrator should set the NestedQueries provider option to indicate to SQL Server that the provider supports nested queries.

    • Parameter marker support

      If the provider supports parameterized query execution by using the ? parameter marker in a query, then SQL Server can delegate parameterized query execution to the provider. Because nested query support cannot be automatically determined from OLE DB properties, the system administrator should set the DynamicParameters provider option to indicate to SQL Server that the provider supports nested queries.

Indexed Access

SQL Server can use execution strategies that involve using the indexes of the Index provider to evaluate predicates and perform sorting operations against remote tables. Set the IndexAsAccessPath provider option to enable indexed access against a provider.

Additionally, when using indexes involving character columns, set the collation compatible linked server configuration option to true for the corresponding linked server. For more information, see sp_serveroption.

Note  Graphically display the execution plan using SQL Query Analyzer to determine the execution plan for a given distributed query. When remote query execution is employed in the execution plan, it is represented using the Remote Query logical and physical operator. The argument of this operator shows the remotely executed query.

See Also

Configuring OLE DB Providers for Distributed Queries

Subquery Fundamentals