384
Part III: Advanced T-SQL Data Types and Querying Techniques
This section, therefore, looks at several options for improving database and application per-
formance. Although SQL Server does as much of the query optimization as it can, there still
remain areas where the design and development decision can have a huge impact on
query performance.Optimizing Distributed Queries
As SQL developers the responsibility to write optimal performing queries falls upon you.
With tools and utilities such as SQL Server Profi ler and Execution Plans, there is no excuse
for writing bad queries. This also goes for distributed queries.However, SQL Server also steps in and performs two types of optimization specifi c to dis-
tributed queries:■ OLE DB SQL Command providers used for remote query execution■ (^) OLE DB Index providers allowing indexed access
The next sections briefl y cover each of these.
Remote Query Execution
SQL Server can delegate as much of the elevation of a distributed query to the SQL
Command provider as possible. The key here is the use of an OLE DB provider, in that, the
OLE DB provider is considered a SQL Command provider if the OLE DB provider supports the
Command object and all associated interfaces. The OLE DB provider must all meet syntax
criteria, including the support of ISO at an entry level or higher to support ODBC at the Core
level or higher.
This plays an important role on how remote queries are executed. SQL queries that access
only the remote tables stored in the data source of the provider are extracted from the orig-
inal distributed query and executed against the provider. This extraction is crucial because
it reduces the number of rows returned from the provider and allows the provider to use its
own indexes in any query evaluation.
SQL Server doesn’t stop at query extraction to improve performance. SQL Server considers
different aspects of the environment to determine how much of the original distributed
query is handed off to the SQL Command Provider:
■ (^) SQL Command provider dialect level
■ Collation compatibility
What are dialect levels? Specifi cally, they are SQL Server, ISO Entry Level, ODBC core, and
Jet. SQL Server delegates query operations only if they are supported by the specifi c dialect
level. The highest level is SQL Server. The lowest level is Jet. The higher the dialect level,
the more operations SQL Server can delegate to the provider. Each dialect level is a superset
of the lower levels. Thus, if an operation is delegated to a particular level, it can also be
delegated to all the higher levels.
c15.indd 384c15.indd 384 7/30/2012 4:50:41 PM7/30/2012 4:50:41 PM
http://www.it-ebooks.info