运行存储过程时的性能问题

Question

我正在运行一个需要很长时间的存储过程，因此我必须将超时时间增加到将近3分钟，任何人都可以提供有关如何减少要执行的存储过程，它可以在100-500个代码上正常工作，但是当代码超过20000时，它会变慢并且需要3-4分钟，下面是sp，谁能帮我优化它。

@GUID uniqueidentifier 
-- Insert statements for procedure here 
SELECT  
     COUNT(Code.allocatedVendorID) AS Amt, 
     MIN(Code.dateAllocated) AS dateAllocated, 
     derivedtbl_1.startID, derivedtbl_2.endID, 
     tbVendor.name, 
     Code.unitCost, Code.isFree, Code.isAcademic, 
     Code.isVoided, Code.GUID, Code.expiryDate 
FROM   
     Code 
     INNER JOIN 
       (SELECT  MIN(CodeID) AS startID, GUID 
       FROM   Code AS tbAccessCode_1 
       GROUP BY GUID) AS derivedtbl_1 
       ON Code.GUID = derivedtbl_1.GUID 
     INNER JOIN 
       (SELECT  MAX(accessCodeID) AS endID, GUID 
       FROM   Code AS tbAccessCode_1 
       GROUP BY GUID) AS derivedtbl_2 
       ON Code.GUID = derivedtbl_2.GUID 
     INNER JOIN tbVendor 
      ON Code.allocatedVendorID = tbVendor.vendorID 
WHERE  (Code.GUID = @GUID) AND (Code.allocatedVendorID > 0) 
GROUP BY 
     derivedtbl_1.startID, derivedtbl_2.endID, 
     tbVendor.name, 
     Code.unitCost, Code.isFree, Code.isAcademic, 
     Code.isVoided, Code.GUID, Code.expiryDate 

ORDER BY dateAllocated DESC 

Answer 1

您可以用它来消除码表的太多扫描：

SELECT  COUNT(Code.allocatedVendorID) AS Amt, 
       MIN(Code.dateAllocated) AS dateAllocated,  
       Code.startID, 
       Code.endID, 
       tbVendor.name, 
       Code.unitCost, Code.isFree, Code.isAcademic, 
       Code.isVoided, Code.GUID, Code.expiryDate 
    FROM   
     (select Code.*, 
      min(accessCodeID) over (partition by Code.GUID) startID, 
      max(accessCodeID) over (partition by Code.GUID) endID 
     from Code 
     where (Code.GUID = @GUID) AND (Code.allocatedVendorID > 0) 
    ) Code 
    INNER JOIN 
      tbVendor ON Code.allocatedVendorID = tbVendor.vendorID 
    GROUP BY Code.startID, 
       Code.endID, 
       tbVendor.name, 
       Code.unitCost, Code.isFree, Code.isAcademic, 
       Code.isVoided, Code.GUID, Code.expiryDate 
    ORDER BY dateAllocated DESC