|
| Is it expected that a query through a linked server (where all tables are on
the said linked server) would be significantly slower than the same query
run on the linked server itself?
Example:
ServerA is linked to ServerB. Both are local (on the LAN).
From ServerA, I execute a query joins together 5 tables and uses 3
subqueries, using tables all on ServerB - no tables on any other server are
referenced in the query. The tables are all referenced as
ServerName.DBName.dbo.tablename. The query runs takes about 15 seconds to
run, and it returns 6 rows.
If I connect to ServerB via EM and execute the same query, removing the
"ServerName.DBName.dbo." from the table references, it executes in 1 or 2
seconds and returns the same 6 rows.
The SQL Servers are linked as "Server Type" of "SQL Server" and I've tried
checking "Collation Compatible" and "Remote Collation" with no effect on
speed.
I suspect that using OPENQUERY() from ServerA to run the query on ServerB
would speed it up, but with the query being large and having some literals
and variables in it, it gets messy. But I'd really like to have these
queries in stored procs on ServerA, since the database on ServerB is from a
vendor's product and I'd rather not create objects in that database, but the
speed penalty of creating them in one of our databases is just unacceptable.
|
|