Reasons NOT to use Linked Servers in SQL Server?

Question

Ladies and Gentlemen -

I work with someone who suggested using a linked server (Informix) in SQL Server.

They discovered (for unknown reasons) that they were more fortunate with client tools connecting to SQL and SQL Server proxying SQL data for Informix and then pointing client tools directly to Informix. Of course, my thought, of course, is "Solve the client," the problem with connecting Informix, do not use a hack, "but this, moreover, and perhaps is not subject to discussion for real.

As the saying goes, what is the danger of this approach in terms of performance?

• ALL queries will fall into one bit on Informix, and we will not need to do any heterogeneous JOINs between our own SQL tables and Informix. SQL Server literally acts like nothing more than a data proxy.
• Most of the queries performed will also make a fair amount from GROUPing and aggregation, therefore (with luck) we will not move tons and tons of rows between the boxes.

My question is:

Can someone identify the scenarios when the “GROUP BY” question released on SQL Server will return individual granular rows to SQL Server and aggregated there compared to Informix? This is an apocalypse, as far as I know.

Are there other (bad) performance consequences that a linked server uses in this type of situation that I should be aware of (and use as a way to simplify the solution and go to the client interfaces> Informix)?

Thanks!

Answer 1

If you are using a linked server in a query directly from SQL Server in the form:

SELECT Col1, col2, col3, SUM(col4) FROM LinkedServer.Database.schema.Table GROUP BY Col1, col2, col3 

He will then perform aggregations on SQL Server. But if you use OPENQUERY or OPENROWSET , then it will execute the query on the linked server and then retrieve the data on SQL Server:

 SELECT * FROM OPENQUERY(LinkedServer, ' SELECT Col1, col2, col3, SUM(col4) FROM Database.schema.Table GROUP BY Col1, col2, col3')