O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Performance Consideration

In the beginning of this chapter, the following statement was made: “With SQL Server, getting to and querying distributed data is not a problem; SQL Server can access data from a plethora of data sources. SQL Server provides several methods for accessing data from many different external data sources from a source location.” Yet, you cannot simply rely on distributed queries alone to solve complex application performance concerns.

This section, therefore, looks at several options for improving database and application performance. 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 Profiler 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 specific to distributed queries:

  • OLE DB SQL Command providers used for remote query execution
  • OLE DB Index providers allowing indexed access

The next sections briefly 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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required