title: Optimizing SQL Query with a linked server
body: <p>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:</p>
  
  <pre><code>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 &amp; 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
  </code></pre>