sql - 在 Cursor 中选择查询花费的时间太长

Question

我设计了一个游标来针对 6500 个检查员运行一些统计数据，但它花费的时间太长了。游标中还有许多其他选择查询，但它们运行正常，但以下选择运行速度非常非常慢。没有游标选择查询运行得很好。

要求:

访问已上传文件的每个检查员的访问次数(1 或 2 或 13)

表格:

• 检查员:InspectorID
• InspectionScope:ScopeID、InspectorID (FK)
• 访问:VisitID、VisitDate ScopeID (FK)
• VisitsDoc:DocID、DocType、VisitID (FK)

光标代码:

DECLARE 
        @curInspID int, 
        @DateFrom date, @DateTo date;

SELECT @DateTo = CAST(GETDATE() AS DATE)
       ,@DateFrom = CAST(GETDATE() - 90 AS DATE)


DECLARE  
        @InspectorID int,
        @TotalVisits int;


DECLARE @Report TABLE (
        InspectorID int,
        TotalVisits int)


DECLARE curList CURSOR FOR
    SELECT InspectorID FROM Inspectors ;        


OPEN curList
FETCH NEXT FROM curList INTO @curInspID;

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT 
    @curInspID = s.InspectorID    
    ,@TotalVisits = COUNT(distinct v.visitID)
from Visits v 
inner join InspectionScope s on s.ScopeID = v.ScopeID 
inner join VisitDocs vd on vd.VisitID = v.VisitID 
where s.InspectorID = @curInspID and vd.DocType IN (1,2,13) 
and v.VisitDate BETWEEN @DateFrom and @DateTo
group by s.InspectorID 


INSERT INTO @Report VALUES(@curInspID,@TotalVisits);

FETCH NEXT FROM curList INTO @curInspID;
END

CLOSE curList
DEALLOCATE curList

SELECT * FROM @Report

以下查询在同一游标内运行正常

    ,@TotalVisitsWithReportScore = (select COUNT(v.visitid) from visits v
                        inner join InspectionScope s on s.ScopeID = v.ScopeID 
                        where v.ReportStandard not in (0,9) and v.VisitType = 1
                            and v.VisitDate BETWEEN @DateFrom and @DateTo
                            and s.InspectorID = @curInspID 
                            )

    ,@TotalVisitsWith_ReportScore_RejectionFeedBack = (select COUNT(v.visitid) from visits v
                        inner join InspectionScope s on s.ScopeID = v.ScopeID 
                        where v.ReportStandard not in (0,9) and v.VisitType = 1
                            and v.DiscrepancyType IN (2,5,6,7,8)
                            and v.VisitDate BETWEEN @DateFrom and @DateTo
                            and s.InspectorID = @curInspID 
                    )

Answer 1

这里不需要光标——您可以使用 INSERT INTO 和 SELECT，加入 Inspector 表。

INSERT INTO @Report 
SELECT 
    s.InspectorID    
    , COUNT(distinct v.visitID)
from Visits v 
    inner join InspectionScope s on s.ScopeID = v.ScopeID 
    inner join VisitDocs vd on vd.VisitID = v.VisitID 
    inner join Inspector i on s.InspectorID = i.InspectorId 
where vd.DocType IN (1,2,13) 
and v.VisitDate BETWEEN @DateFrom and @DateTo
group by s.InspectorID 

---

请注意，如果该表中存在其他表中不存在的结果，您可能需要对 Inspector 表使用 OUTER JOIN。取决于您的数据和期望的结果。