Geek asks and answers

SQL query optimization with linked server

I have an SQL query that is used to pull data for a report. It works, it just takes too much time for the program to pull out a report. Is there a way to optimize this SQL? This is a SELECT query with several subqueries using a linked server named SYSPROD1. I tried, but did not succeed. Here is 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
+3
source share
2 answers

, ( ) , SQL , ; , , , sysadmin, db_owner db_ddladmin, . , SYSPROD1.SysproCompanyD.dbo.InvMaster, SYSPROD1.SysproCompanyD.dbo.SalProductClass SYSPROD1.SysproCompanyD.dbo.InvWarehouse , .

, . - temp , .

-, , , - , .

+3

JOIN s:

Select    
      invMaster.StockCode, prodclass.[Description],  invMaster.LongDesc, w1.QtyOnHand AS CSSJHB

..<snip>

from         
      SYSPROD1.SysproCompanyD.dbo.InvMaster invMaster  
join SYSPROD1.SysproCompanyD.dbo.SalProductClass prodclass 
      on invMaster.ProductClass = prodclass.ProductClass
INNER JOIN SYSPROD1.SysproCompanyD.dbo.InvWarehouse w1 on w1.StockCode = invMaster.StockCode AND w1.Warehouse = 'P4'

... SELECT.

0

More articles:


All Articles