Can I get rid of this cursor?

Question

Can I get rid of this cursor?

Marcia J

SSCertifiable

Points: 5649
More actions
January 16, 2019 at 2:59 pm

#376824
I'm trying to figure out if there's a way to get rid of a cursor in a stored procedure that I'm working on.
The procedure does the following steps:
1. Gathers all records that meet a particular criteria into a temporary table. These records have an operator id on them.
2. Pulls together a list of distinct operator id's into a temporary table.
3. For each operator, pulls the higher of a given percentage or 5 records. For instance, if the percentage is 2 and an operator had 200 records, it would pull 5 record since that's higher 2% of 200. If the operator had 1000, it would pull 20 records.
The cursor is in step 3. For each operator in the table created in step 2, it does the following:
1. Calculates the number of records in the overall table from step 1 for that particular operator.
2. Calculates how many records to pull for the operator.
3. Pull those records and puts them into a table to be used for auditing. It uses "Select top (@RowCount) ... order by NEWID()" to do that where @RowCount is the value calculated in step 2.
It's been operating this way for eight years, so there's no urgency to change it. However, I'm in making other changes, so if I can figure out a way around it, I would like to.
I think I could figure out how to steps 1 and 2 within the cursor in a single operation. What I can't figure out how to do is step 3 outside of the cursor given that I need to pull a different number of records for each operator.
This is in SQL 2008 R2.
Any suggestions would be welcome. I'm not looking for exact code (that's my job), but just any clues as to how to do this differently.
Sorry, this is so long.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

Yes, you can. You want to use a CROSS APPLY with a TOP clause and use a CASE expression in the TOP clause. Post table definitions and sample data, and someone will be able to work up some tested code. The first link in my signature tells you how.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

You don't need a temporary table to store distinct OperatorIds, you can do it all with CTE. Though sometimes it will give you better performance if you create a temporary table and put indexes on it.Something like this should do it:

DECLARE @ProportionToDisplay decimal(3,2) = 0.02 /* 2%  */

;WITH CTE AS
(
    SELECT OperatorId, Col1, Col2, Col3
      FROM dbo.myTable
     WHERE 1=1 --[Search Criteria For Rows]
),
CTE2 AS
(
    SELECT OperatorId, Count(*) NumberOfRows
      FROM CTE
     GROUP BY OperatorId
)
SELECT A.OperateId , B.Col1, B.Col2, B.Col3
  FROM CTE2 A
 CROSS APPLY(SELECT TOP(CASE WHEN CONVERT(int,B.NumberOfRows*@ProportionToDisplay) < 5 THEN 5 ELSE CONVERT(int,B.NumberOfRows*@ProportionToDisplay) END)
                    B.Col1, B.Col2, B.Col3
               FROM CTE B
              WHERE B.OperatorId = A.OperatorId) B

Marcia J SSCertifiable Points: 5649 More actions · Answer 3

I think the above two replies are enough to get me going. Unfortunately, I have to work on another urgent problem. But once I get back to this and try it out, I'll let you know how things go.

Thanks much to both of you.

Marcia J SSCertifiable Points: 5649 More actions · Answer 4

I realize this is a very, very late reply. I wasn't able to get back to the stored procedure right away. However, your answers did help me get rid of the cursor. I ended up using a CTE and a cross apply.

I meant to get back to you but then I ended up getting really sick and then got overwhelmed at work trying to catch up.

So, this is a very belated thank you. I really did appreciate your help.