特定的sql查询优化。性能下降

问题描述：

我有这个查询给我结果，我需要但执行得非常慢。速度慢是因为[Contoso $ Item Ledger Entry]表约有0.6M记录。请建议如何优化/简化此查询（如果可能）以及我需要具备哪些索引才能实现最佳效果。特定的sql查询优化。性能下降

DECLARE @TempTable TABLE 
(
    Numeris varchar(20) 
) 
INSERT INTO @TempTable (Numeris) 
SELECT No_ 
FROM [Contoso$Sales Line] 
WHERE [Document no_] = 'PP03428' 

SELECT I.No_, CASE WHEN 
      ISNULL(ROUND(CAST(SUM(ILE.[Remaining Quantity]) AS float), 2), 0) < 
      (SELECT ISNULL(SUM([Outstanding Quantity]), 0) 
      FROM [Contoso$Sales Line] SL 
      WHERE (SL.No_ = I.No_) 
       AND (SL.[Document type] = 1 OR SL.[Document type] = 2) 
       AND (SL.[DA4 eksportas] = 0) 
       AND (SL.Type = 2) 
      ) 
     THEN 0 
     ELSE 
      ISNULL(ROUND(CAST(SUM(ILE.[Remaining Quantity]) AS float), 2) - 
      (SELECT ISNULL(SUM([Outstanding Quantity]), 0) 
      FROM [Contoso$Sales Line] SL 
      WHERE (SL.No_ = I.No_) 
       AND (SL.[Document type] = 1 OR SL.[Document type] = 2) 
       AND (SL.[DA4 eksportas] = 0) 
       AND (SL.Type = 2)), 0 
      ) 
    END AS Kiekis 
FROM [Contoso$Item] I 
LEFT OUTER JOIN [Contoso$Item Ledger Entry] ILE ON I.No_ = ILE.[Item No_] 
WHERE I.No_ IN (SELECT Numeris FROM @TempTable) 
GROUP BY I.No_

'[Contoso $ Item]'有'No_'上的索引吗？ '[Contoso $ Item Ledger Entry]'有'[Item No_]'的索引吗？你能否将查询的执行计划添加到问题中？ – 2013-04-30 15:01:47

答

使用执行计划知道您的查询如何执行并知道您的查询如此缓慢的原因。您可以在Contoso $ Item表的No_ Column上添加一个索引以避免fullscan表

特定的sql查询优化。性能下降

相关推荐