CODEWITHSUNDEEP
sqlsql-server-2008t-sql
user1263981
user1263981

Reputation: 3157

select query in Cursor taking too long

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:

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

Answers (3)

user3514987
user3514987

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

sgeddes
sgeddes

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

marc_s
marc_s

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

Related Questions