Get ride of cursor/ Performance issus

Question

Post reply

Get ride of cursor/ Performance issus

Rem70Rem

SSCertifiable

Points: 6166
More actions
June 23, 2010 at 12:39 pm

#220345

Hi,
I found a piece of SQL that contains a cursor and seems slow. I would like to optimize it but can't figure out what to do. Basically I want to remove all entries in a stage table base on what we received from load table
Here is part of the code:
/* create a temporary table to hold each the minimum and maximum shipping date for each accounts */
select acct, item, max(sdate) as maxDate, min(sdate) as minDate
into #ShippingDate
from load_shipping
group by dist, item
declare @sacct varchar(6)
declare @sitem varchar(10)
declare @smindate int
declare @smaxdate int
declare datecursor cursor for
select acct, item, mindate, maxdate from #ShippingDate
open datecursor
/* Remove entry in stage table */
while @@FETCH_STATUS = 0
BEGIN
delete from SHIPPING_STG
where stg_acct = @sacct
and stg_item = @sitem
and stg_shippingdate >=@smindate
and stg_shippingdate <= @smaxdate
fetch next from datecursor into @sdist, @sitem, @smindate, @smaxdate
END
CLOSE DATECURSOR
DEALLOCATE DATECURSOR
All help will be helpfull.
Thanks

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

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

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 1

delete from SHIPPING_STG

from SHIPPING_STG ss

inner join (

select acct, item, max(sdate) as maxDate, min(sdate) as minDate

from load_shipping

group by acct, item

) AS ls on

s.stg_acct = ls.acct

and s.stg_item = ls.item

and s.stg_shippingdate >= ls.minDate

and s.stg_shippingdate <= ls.maxDate

Scott Pletcher, SQL Server MVP 2008-2010

WayneS SSC Guru Points: 95460 More actions · Answer 2

Rem-487422,

Do you understand how the code that Scott sent completely replaces that cursor?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 3

Scott is correct, but I spotted a typo. It should be :

delete from SHIPPING_STG

from SHIPPING_STG ss

inner join (

select acct, item, max(sdate) as maxDate, min(sdate) as minDate

from load_shipping

group by acct, item

) AS ls on

ss.stg_acct = ls.acct

and ss.stg_item = ls.item

and ss.stg_shippingdate >= ls.minDate

and ss.stg_shippingdate <= ls.maxDate

Converting oxygen into carbon dioxide, since 1955.

Rem70Rem SSCertifiable Points: 6166 More actions · Answer 4

This looks very interesting. I will have to dig a little to fully understand the query, It's the first time I am seeing something like this. If you have a few minutes to explain to me, this will be appreciated.

Thanks

WayneS SSC Guru Points: 95460 More actions · Answer 5

Rem-487422 (6/23/2010)
This looks very interesting. I will have to dig a little to fully understand the query, It's the first time I am seeing something like this. If you have a few minutes to explain to me, this will be appreciated.
Thanks

If you were to replace the first line ("delete from SHIPPING_STG") with "select *", would you understand what is going on? I ask this instead of directly answering, because your answer will decide how this needs to be answered for you to understand it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2