Search code examples
sqloptimizationlinked-server

Optimizing SQL Query with a linked server

I have an SQL query, its used in pulling data for a report. It works, just it takes too long for the program pulling the report. Is there any way i can optimise this SQL ? It's a SELECT query with multiple subqueries, using a linked server named SYSPROD1. I've tried but haven't had any success. Here's my SQL:

Select   
      invMaster.StockCode, prodclass.[Description],  invMaster.LongDesc,
      (select inv.QtyOnHand from SYSPROD1.SysproCompanyD.dbo.InvWarehouse inv where inv.StockCode = invMaster.StockCode and inv.Warehouse = 'P4') as CSSJHB,
      (select inv.QtyOnHand from SYSPROD1.SysproCompanyD.dbo.InvWarehouse inv where inv.StockCode = invMaster.StockCode and inv.Warehouse = 'KK') as KFCJHB,
      (select inv.QtyOnHand from SYSPROD1.SysproCompanyD.dbo.InvWarehouse inv where inv.StockCode = invMaster.StockCode and inv.Warehouse = 'KF') as KFCMIDRAND,
      (select inv.QtyOnHand from SYSPROD1.SysproCompanyD.dbo.InvWarehouse inv where inv.StockCode = invMaster.StockCode and inv.Warehouse = 'QK') as QKJHB,
      (select inv.QtyOnHand from SYSPROD1.SysproCompanyD.dbo.InvWarehouse inv where inv.StockCode = invMaster.StockCode and inv.Warehouse = 'SD') as SDBBLOEM, 
      (select inv.QtyOnHand from SYSPROD1.SysproCompanyD.dbo.InvWarehouse inv where inv.StockCode = invMaster.StockCode and inv.Warehouse = 'SL') as SEQUENCE,
      (select inv.QtyOnHand from SYSPROD1.SysproCompanyD.dbo.InvWarehouse inv where inv.StockCode = invMaster.StockCode and inv.Warehouse = 'PA') as CSSCT,
      (select inv.QtyOnHand from SYSPROD1.SysproCompanyD.dbo.InvWarehouse inv where inv.StockCode = invMaster.StockCode and inv.Warehouse = 'VL') as CSGEORGE



from        
      SYSPROD1.SysproCompanyD.dbo.InvMaster invMaster  join SYSPROD1.SysproCompanyD.dbo.SalProductClass prodclass
      on invMaster.ProductClass = prodclass.ProductClass 

  where prodclass.[Description] in ('WHOLEBIRDS','ABI & OTHER', 'CATERING PORTIONS', 'FILLETED PRODUCTS', 'FRESH PRODUCTS','INDUSTRIAL CATERING', 'IQF PORTIONS', 'LOW VALUE FIXED MASS', 'RED MEAT', 'REJECT EGGS' ,'SUNDRY PRODUCTS','VALUE ADDED')                   
    group by invMaster.StockCode, prodclass.[Description],  invMaster.LongDesc

  order by prodclass.[Description], invMaster.StockCode asc
Solution

  • Linked server queries of the sort you're writing here (where several tables on the remote server are joined) typically perform poorly because the SQL engine can't use many of the tricks it uses to optimise the plan for local queries; for example, unless the security context used to connect to the linked server is a member of the sysadmin, db_owner or db_ddladmin role, the calling server has no access to the table statistics. It's likely that the plan for this query will pull the entire contents of SYSPROD1.SysproCompanyD.dbo.InvMaster, SYSPROD1.SysproCompanyD.dbo.SalProductClass and SYSPROD1.SysproCompanyD.dbo.InvWarehouse back to the calling machine multiple times during the execution of the query, and it's this that is taking the time.

    You have a couple of options to try and improve the performance of this query. One is to insert the rows you need from each of the tables on the linked server into a temp table or table variable before joining them in the query.

    The second, given that this entire query could be satisfied by tables on the linked server, would be to create it as a view on that server - this would mean that all the processing was carried out there and should be much more efficient.