In my environment, we have a server that stores sales data and another server that stores our HR data. For some reports, we need to combine sales data with employee data. Our sales server contains various tables with millions of rows. The employee table on the HR server contains about 12,000 rows.
On the sales server we have the HR server setup as a Linked Server. For employee sales reporting, we run the main queries on the sales server. Typically this involves joining multiple tables on this server and a single join across to the HR server to the employee table.
As you probably suspect, we are having performance issues. I've tried using both four dot nation and
I haven't seen a major difference in performance between the two. Both are bad. If the query optimizer picks a plan where the remote table is queried once, performance is acceptable, even if all 12,000 rows are returned. Unfortunately, I have seen execution plans where the remote table was hit thousands of times in a single query.
I've considered simply creating a local table and running a SQL Agent job to refresh it from the HR employee table every hour or so. Although not ideal, this would be acceptable because the HR employee table doesn't change all that often.
Are there any other approaches I should be considering to solve my performance issue?