使用链接服务器优化SQL查询

Question

我有一个SQL查询，用于获取报告中的数据。它可以工作，但是在拉取报告的程序上花费太长时间了。有没有办法优化这个SQL？它是带有多个子查询的SELECT查询，使用名为SYSPROD1的链接服务器。我已经尝试过了，但没有成功。这是我的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

- Scott

Answer 1

你正在编写的联接服务器查询（在其中连接了远程服务器上的多个表）通常表现不佳，因为SQL引擎无法使用许多用于优化本地查询计划的技巧；例如，除非用于连接到链接服务器的安全上下文是sysadmin, db_owner或db_ddladmin角色的成员，否则调用服务器将无法访问表统计信息。 很可能这个查询的计划会在执行查询期间多次将SYSPROD1.SysproCompanyD.dbo.InvMaster、SYSPROD1.SysproCompanyD.dbo.SalProductClass和SYSPROD1.SysproCompanyD.dbo.InvWarehouse的全部内容拉回到调用机器上，这就是导致时间延长的原因。

你有几个选项来尝试提高此查询的性能。 其中之一是在查询中加入临时表或表变量之前，将你需要的每个表中的行插入到链接服务器上。

第二个选项是，考虑到整个查询都可以通过链接服务器上的表满足，将其创建为该服务器上的视图——这意味着所有处理都在那里进行，应该更加高效。

- Ed Harper

---

非常感谢，创建链接服务器上的视图并调用它非常有效，将初始时间缩短到了几乎十分之一。 - Scott

我的本地服务器上的临时表对我来说完美无缺。我只需要将所需行的SELECT INTO放入临时表中，这非常快速，然后使用临时表连接到本地表。好的提示！ - Bob Mc

Answer 2

你可以使用 JOIN 进行优化：

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

..<剪切>

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 中的每个子查询重复此过程。

- hkf

---

谢谢，这个方法有点用，但我的查询仍然运行时间太长了。 - Scott