Optimizing SQL Query with a linked server

Question

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

Ed Harper · Accepted Answer · 2012-04-18 08:45:35Z

3

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.

answered Apr 18, 2012 at 8:45

Ed Harper

21.6k5 gold badges60 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scott Over a year ago

Thank you very much, creating the view on the Linked server and calling it worked great, dropped the time to nearly 1 tenth of what it was initiallly.

Bob Mc Over a year ago

The temp table on my local server worked perfectly for me. Did a SELECT INTO of just the rows I needed into the temp table, which ran very quickly, then used the temp table to join to the local tables. Good tip!

hkf · Accepted Answer · 2012-04-18 07:10:10Z

0

You can optimise it with JOINs:

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'

etc. Repeat for each of your subqueries in the SELECT.

answered Apr 18, 2012 at 7:10

hkf

4,5201 gold badge32 silver badges46 bronze badges

1 Comment

Scott Over a year ago

Thanks, this worked a bit, but my query was still a bit too long running.

Collectives™ on Stack Overflow

Optimizing SQL Query with a linked server

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related