How to avoid a cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to avoid a cursor

Author

Topic

SQLMike
Starting Member

7 Posts

Posted - 2004-12-18 : 11:32:56

I've read that people think cursors are very bad, but haven't seen why, but I haven't been able to come up with a way to do this without using one...

I need to select the top 20 (by amount and count) for each division, and then use that data to pull more stats and run reports against it..

Sample code:
----------------------------------------
ALTER PROCEDURE Update_Top20
@StartDate datetime = null, @EndDate datetime = null as

-- The default date range for this process is DOE<=t-2 months-1 day (t being the last CGA update)

Declare @DIV varchar(6)

--If no End Date is passed in set it to the default:t-2 months-1 day (t being the last CGA update)
If @EndDate is null
Set @EndDate=DateAdd(day,-1,DateAdd(month,-2,(Select top 1 Convert(varchar,Update_DT,101) From dbo.cga)))

--If no Start Date is passed in set it to the oldest DOE on file
If @StartDate is null
Set @StartDate=(Select Min(DOE) From dbo.cga)

Declare cDivs Cursor For
select distinct Division_ID from dbo.cga Where Group_ID Not In('3M','C3')

Truncate Table Top20QTY
Truncate Table Top20Amount

Open cDivs
Fetch Next From cDivs Into @DIV

While @@Fetch_Status = 0
Begin

Insert InTo Top20QTY
select Top 20 'QTY' as sType, Division_ID, TM_ID, Sum(QTY) as Count
from dbo.cga
where DOE between @StartDate AND @EndDate
And Division_ID=@DIV
Group By Division_ID, TM_ID
Order By Division_ID, Sum(QTY) Desc

Insert InTo Top20Amount
Select top 20 'Amount' as sType, Division_ID, TM_ID, Sum(Charge) as Amount
from dbo.cga
where DOE between @StartDate AND @EndDate
And Division_ID=@DIV
Group By Division_ID, TM_ID
Order By Division_ID, Sum(Charge) Desc

Fetch Next From cDivs Into @DIV
End

Close cDivs
Deallocate cDivs
----------------------------------------

Then there is more code to take those base tables and grab a bunch more information about those TM_IDs. Is there some easier way to get a top 20 for each Division_ID? (Right now there are 108, but more can get added at any time.)

Thanks!

Michael

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-18 : 13:18:52

something like

select division_Id, TM_ID
from cga t
where TM_ID in (
select top 20 t2.TM_ID from cga t2 where t2.division_Id = t.division_Id and t2.DOE between @StartDate AND @EndDate group by t2.TM_ID order by sum(t2.qty) desc
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-12-18 : 16:35:03

And the reason WHY is that we have many examples here on this site where people have had lousy performance with CURSORs that have been made radically faster (like from hours to seconds) by replacing them with a set-based statement.

---------------------------------------------------------------------------------
Infoneering: Information Technology solutions engineered to professional standards.

SQLMike
Starting Member

7 Posts

Posted - 2004-12-20 : 10:49:11

I don't think that query will work as it would create a cartesian. (There may be 10,000, or more, Division_ID, TMID combinations.) I, also, simplified it too much, as I need to link on one more field (Tm_Prov_ID)

I need one record for each Division_ID, TMID, Tm_Prov_ID combination that is in the top 20 (one set by amount, one set by QTY)

I still can't come up with a way to do it... The closest I come, but it won't run is:

Select 'Amount' as sType, a.Division_ID, b.TM_ID, b.Tm_Prov_ID, b.Amount
From (Select Distinct Division_ID From cga) as a
Inner Join
(Select Top 20 TM_ID, Tm_Prov_ID, Sum(Charge) as Amount
from cga
where DOE between @StartDate AND @EndDate
And Division_ID=a.Division_ID
Group By Division_ID, TM_ID, Tm_Prov_ID
Order By Division_ID, Sum(Charge) Desc) as b
On a.Division_ID = b.Division_ID

SQLMike
Starting Member

7 Posts

Posted - 2004-12-20 : 10:54:03

quote:
Originally posted by AjarnMark

And the reason WHY is that we have many examples here on this site where people have had lousy performance with CURSORs that have been made radically faster (like from hours to seconds) by replacing them with a set-based statement.

It does currently take almost 15 minutes to generate the two tables for this process, but I am still unable to figure out how to do it any other way.

I've made some really complex queries with nested selects and have been amazed at how fast they run, but I just can't see a way to make this one work.

SQLMike
Starting Member

7 Posts

Posted - 2005-01-06 : 20:03:40

Anyone have any ideas on how to make this work without a cursor?

deangc
Starting Member

3 Posts

Posted - 2005-01-09 : 16:00:07

quote:
Originally posted by SQLMike

Anyone have any ideas on how to make this work without a cursor?

I'll have to see if I can find some time to try a few things over the next few days, but, in all honesty, I don't think I'd worry about it in this particular case. Presumably, you don't have 10,000 divisions, so it seems to me that the impact of the cursor is minimal.

I realize that this is a test of SQL purity (nobody who uses cursors can truly be Pure! :)), but there are times (IMO) when it makes sense to use one. This might be one of those times.

Oh, and being strictly technical, you can rewrite this using a WHILE loop instead of a cursor. That is sometimes enough to satisfy the anti-cursor forces.

deangc
Starting Member

3 Posts

Posted - 2005-01-10 : 19:33:18

quote:
Originally posted by SQLMike

I don't think that query will work as it would create a cartesian. (There may be 10,000, or more, Division_ID, TMID combinations.) I, also, simplified it too much, as I need to link on one more field (Tm_Prov_ID)

I need one record for each Division_ID, TMID, Tm_Prov_ID combination that is in the top 20 (one set by amount, one set by QTY)

I still can't come up with a way to do it... The closest I come, but it won't run is:

Select 'Amount' as sType, a.Division_ID, b.TM_ID, b.Tm_Prov_ID, b.Amount
From (Select Distinct Division_ID From cga) as a
Inner Join
(Select Top 20 TM_ID, Tm_Prov_ID, Sum(Charge) as Amount
from cga
where DOE between @StartDate AND @EndDate
And Division_ID=a.Division_ID
Group By Division_ID, TM_ID, Tm_Prov_ID
Order By Division_ID, Sum(Charge) Desc) as b
On a.Division_ID = b.Division_ID

This looks like the right approach to me. What do you mean 'it won't run?'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources