Home > SQL Challenge > SQL – Openquery (1) Use It Or Not Use It

SQL – Openquery (1) Use It Or Not Use It

From Transact-SQL book: “Openquery executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.”

This is a typical Openquery that points to a DB2 linked server used in the From clause.

image

Although OPENQUERY is a convenient way to dynamically query data via a linked server, it has many limitations. Here are what I can think of:

1) Performance: I’ve observed many OPENQUERY failed with unexplained “Interruption” from the remote server in production environment when the SQL and/or the linked server are overloaded.

2) There is an allowable number of simultaneous OPENQUERY connections, 50, 40… (not sure). Rest of the OPENQUERY connections will fail when the server reaches the limit.

3) Cannot call stored procedures on the linked server.

4) Although we can use INSERT, UPDATE, or DELETE statement with OPENQUERY, it is subject to the capabilities of the OLE DB provider, and the syntax is not straightforward (will talk about this later).

5) When temp tables used inside OPENQUERY, the performance of the query deteriorate dramatically. To resolve the performance issue, very often we have to abandon OPENQUERY, and move the entire process to a SSIS package to take advantage of staging tables etc.

6) Anybody who is crazy enough to have more than 8000 chars inside OPENQUERY will tell you that this is not possible. SQL server will tell you plainly that the query cannot have more than 8000 chars.

Categories: SQL Challenge
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: