时间戳

首页 > 解决方案 > SQL Server：查询性能优化

sql - SQL Server：查询性能优化

问题描述

我有以下查询，其执行时间随着数据的增加而变得非常高。我该如何优化呢？

SELECT 
    txh.clid AS clid,
    txh.id AS hlid,
    holdinNo,
    holding,
    ClientID AS cliendID,
    ClientName,
    itm.itemID,
    itm.Item,
    itm.rate,
    (SELECT TOP 1 asset 
     FROM tx_asset 
     WHERE tx_asset.hlid = txh.id 
     ORDER BY id DESC) AS asset
FROM 
    tx_holding AS txh
INNER JOIN 
    tx_set_bill_holding AS itm ON txh.id = itm.hlid AND itm.status = 1
WHERE 
    txh.id IN (SELECT hlid FROM tx_asset 
               WHERE asset IS NOT NULL AND asset != 0)
    AND txh.id NOT IN (SELECT hlid FROM tx_bill_pay 
                       WHERE YEAR(date_month) = YEAR(@tdate) 
                         AND hlid IS NOT NULL)
    AND txh.clid IN (SELECT id FROM tbl_client 
                     WHERE client_type = 'Non-Govt.')
    AND itm.type = 'Non-Govt.' 
    AND txh.roadno = @roadno

标签： sqlsql-serverquery-optimization

解决方案

尝试这个 ：

SELECT hlid,max(asset) asset into #temp FROM tx_asset group by hlid
SELECT txh.clid AS clid,
       txh.id AS hlid,
       holdinNo,
       holding,
       ClientID AS cliendID,
       ClientName,
       itm.itemID,
       itm.Item,
       itm.rate,
       t.asset
FROM tx_holding AS txh
INNER JOIN tx_set_bill_holding AS itm ON txh.id = itm.hlid AND itm.status=1
INNER /*or left*/ join #temp t on t.hlid=txh.id
WHERE txh.id IN 
(
 SELECT hlid FROM tx_asset WHERE asset IS NOT NULL AND asset!=0
)
 AND txh.id NOT IN 
 (
 SELECT hlid FROM tx_bill_pay WHERE year(date_month)=year(@tdate) AND hlid IS NOT NULL
 )
 AND txh.clid IN 
 (
  SELECT id FROM tbl_client WHERE client_type='Non-Govt.'
 )
 AND itm.type='Non-Govt.' AND txh.roadno=@roadno

 Drop table #temp

推荐阅读