Reasons NOT to use Linked Servers in SQL Server?

Question

Ladies and gents -

I'm working with someone who has proposed using a linked server against (Informix) in SQL Server.

They've found (for reasons unknown) that they have better luck with client tools connecting to SQL, and SQL Server proxying SQL statments to Informix then pointing the client tools directly at Informix. My thought, of course is "Solve the Client > Informix connectivity problem, don't use a hack" - but that's besides the point and probably not negotiable by yours truly.

That being said, what are the dangers of this approach in terms of performance?

• ALL queries will hit a single db on Informix, and we won't need to do any heterogeneous JOINs between native SQL tables and Informix. SQL Server is literally acting as nothing more than a proxy / data pump.
• Most of the queries being executed will also be doing a fair amount of GROUPing and aggregation, so (with luck) we won't be moving tons and tons of rows between boxes.

My question:

Can anyone identify scenarios where a "GROUP BY" question fired at SQL Server would cause the individual, granular rows to be returned to SQL Server and aggregated there vs. on Informix? That's the apocalypse as far as I'm concerned.

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

Thanks!

Answer 1

If you use the linked server on a query directly from SQL Server on the form of:

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

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

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