Reputation: 3157
I have designed a cursor to run some stats against 6500 inspectors but it is taking too long. There are many other select queries in cursor but they are running okay but the following select is running very very slow. Without cursor select query is running perfectly fine.
Requirements:
Number of visits for each inspectors where visits has uploaded document (1 or 2 or 13)
Tables:
Inspectors: InspectorID
InspectionScope: ScopeID, InspectorID (FK)
Visits: VisitID, VisitDate ScopeID (FK)
VisitsDoc: DocID, DocType, VisitID (FK)
Cursor code:
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
Following queries run okay inside the same cursor
,@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
)
Upvotes: 2
Views: 5977
Reputation: 220
Cursors is not recommended any more. It is better for you to insert the data into a temporary table and add a primary key to it.
So in your loop you will have a while loop that loops through your table with a WHERE clause on your Id in the temporay table.
That is much much more faster.
Upvotes: 1
Reputation: 62861
No need for a cursor here -- you can use INSERT INTO
with SELECT
, joining on the Inspector
table.
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
Please note, you may need to use an OUTER JOIN
with the Inspector
table if there are results in that table that do not exist in the other tables. Depends on your data and desired results.
Upvotes: 4
Reputation: 755351
The best way to speed up a cursor is.... to get rid of it!
Here, you definitely don't need a cursor - a simple SELECT
will do - and should be substantially faster!
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
INSERT INTO @Report (InspectorID, TotalVisits)
SELECT
i.InspectorID,
COUNT(v.visitID)
FROM
dbo.Inspectors i
INNER JOIN
dbo.InspectionScope s ON s.InspectorId = i.InspectorId
INNER JOIN
dbo.Visits v ON s.ScopeID = v.ScopeID
INNER JOIN
dbo.VisitDocs vd ON vd.VisitID = v.VisitID
WHERE
vd.DocType IN (1, 2, 13)
AND v.VisitDate BETWEEN @DateFrom AND @DateTo
GROUP BY
s.InspectorID
SELECT * FROM @Report
Upvotes: 1