Search code examples
t-sqlviewssmslinked-server

TSQL What is the most efficient way to query a view on a linked server?

I have two SQL Servers on which I am a sysadmin. One is a 2008R2 server called LIGHT, as the box experiences very little activity. The other is a 2005 server called HEAVY, which sees a decent, constant amount of activity throughout the day. Both servers are in the same data center. I have created a linked server on LIGHT so that it can see HEAVY.

I've created a view on HEAVY to total up sales per location by day for the last few days that is more or less:

SELECT BusinessDate,
       Location,
       SUM(Sales)
FROM   Last7DaysOfSales
GROUP BY BusinessDate, Location

When I use SSMS to run this view on HEAVY, it runs in 30 seconds. When I run this same view from LIGHT via the linked server connection, it takes 17 minutes! I was hoping that using a view would mean the "heavy lifting" would be done on HEAVY, and the results of the view sent to LIGHT. Considering the ridiculous run time, it would seem that LIGHT is pulling all of the records over and doing the work itself?

What is the most efficient way I can deal with this problem? Is it even possible to have HEAVY handle the execution locally and pass the results to LIGHT? Or is my only option to create a nightly process on HEAVY to dump the results of this view into a table each night, then simply have LIGHT grab the data in the table?

Many thanks in advance.

Solution

  • I would suggest the use of OPENQUERY if you want to force the processing to be done on the remote machine. For example:

    SELECT *
FROM   OPENQUERY(
        HEAVY,
        'SELECT BusinessDate,
               Location,
               SUM(Sales)
        FROM   MyDatabase.dbo.Last7DaysOfSales -- Note the dot notation
        GROUP BY BusinessDate, Location')