2

I'm green with linked servers so forgive some of my nomenclature ignorance.

But my understanding & experience is that it's far more efficient to query a table on the server itself vs. a remote linked server -- as indexes and statistics can be read. One can leverage 'openquery' when kicking off a query from a different remote server to have data pull locally, and that's fine.

But what if there is a big fact table on Server A, and a key dimensional table on Server B? (some corporations have data structures that are terrible, is what it is). And it's not possible to put them on the same server, but a join is necessary.

One way would to do something like

 select * from openquery ([remote_fact_server], select facts from fact_table) 
left join local.dimension_table.

But that doesn't work well for different reasons. One you have to preload the entire fact table before any kind of aggregation and it's not indexed at all either when it's brought along.

I'm thinking an idea would be something like

insert into [remote_fact_server].tempdb.##temp_table_dimension
select * from local.dimension_table

Make a temp table copy of the dimension table, then join it all on one server before further processing/ extraction (There is no other write access). But I'm not sure it's possible to push a temp table onto a remote server.

What am I missing here?

2 Answers 2

2

A question that arises with this method is which is faster, to push (INSERT INTO remotetable SELECT FROM localtable) or pull (INSERT INTO localtable SELECT FROM remotetable) the data. This tip will try to answer that question.

Compare PUSH vs PULL Data Copy Performance in SQL Server

enter image description here

1
1

It's going to highly depend on the exact use case.

If there are filters you can apply, either naturally for only certain facts, or at least by determining you only care to aggregate for certain dimensions, then you should apply those filters explicitly to the remote fact table as part of your query. If they happen to filter the fact table by it's key or indexes, then you may end up with a Remote Query operation, which will filter the table down on the remote server before bringing the results across the Linked Server.

Otherwise, you can use OPENQUERY() to force the filters to be applied remotely. But, one caveat with OPENQUERY() is that SQL Server always estimates 10,000 rows of cardinality for it. So even after you've filtered down the remote table, if it's still returning 100,000 rows or more, you're going to have a pretty poor cardinality estimate in your execution plan.

Aside from all of that, I'd still look to synchronize both tables onto the same server by whichever means is possible. Whether that requires a third server or not, and whether that requires the use of Availability Groups, Replication, SSIS, a scheduled SQL Agent Job, or writing your own application code to do it for you.

Otherwise, if you're unable to do any of the above, you unfortunately are bound to however long it takes to load the remote table locally. But if you're able to query the table across the Linked Server ad-hoc, then I see no reason why you couldn't at least use a scheduled SQL Agent Job to query the remote table across the Linked Server to cache it locally periodically, so your read queries are faster at least.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.