Rewriting Stored Procedure from Cursor to SET-based

Question

Rewriting Stored Procedure from Cursor to SET-based

kwe477

SSC Eights!

Points: 820
More actions
May 22, 2012 at 7:41 am

#149949

I'm not a developer but am tasked with rewriting a SP to be set-based.
This particular goodie takes about 6 hours to finish, since the underlying tables are huge. (> 1 billion rows)
I'd really appreciate help on this one.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[stat_call] @in_date varchar(6) as
declare @cmd0 varchar(8000)
, @cmd1 varchar(8000)
, @where varchar(500)
, @region varchar(500)
, @in_date_region varchar(8)
, @cnt int
, @cnt2 int
, @ident int
, @art varchar(2)
, @d varchar(100)
, @w_o varchar(100)
, @bun varchar(100)
, @circle varchar(100)
, @jc-2 varchar(100)
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tf_kdu]'))
begin
create table tf_kdu ( in_date varchar(6)
, region varchar(500)
, artvarchar(2)
, ident int
, ident2 int
, heading varchar(500)
, group varchar(70)
, device varchar(500)
, Ins_BG float
, Single_BGfloat
, Allein_1float
, Allein_2float
, couple_1float
, couple_2float
, couple_3float
, couple_4float
, diversfloat
, Ins_P float
, P1 float
, P2 float
, P3 float
, P4 float
, P5 float
, P6Plusfloat
)
end
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[parameters]'))
begin
create table parameters
( bm varchar(6)
, region varchar(50)
, art varchar(1)
, aktiv smallint
)
end
set @in_date_region = @in_date + '03'
DECLARE my_cur_region CURSOR
FOR
select substring(krs_schl1,1,5) + ' ' + krs_lang1 as knr
, 1 as art
from [msi_dlp].[dbo].[td_dwh1]
, [msi_dlp].[dbo].[td_dwh2]
, [msi_dlp].[dbo].[td_dwh3]
where td_dwh2.mon1 = td_dwh1.mon1
and td_dwh3.owk_id < 3
and td_dwh3.krs_date <= td_dwh2.mon3
and ( td_dwh3.krs_date2 is null
or td_dwh3.krs_date2 >= td_dwh2.mon3)
and td_dwh1.mon5 = cast(@in_date_region as int)
union
select substring(str_1,1,5) + ' ' + str_2
, 2 as art
from [msi_dlp].[dbo].[td_dwh1]
, [msi_dlp].[dbo].[td_dwh2]
, [msi_dlp].[dbo].[td_dwh4]
, [msi_dlp].[dbo].[td_dwh5]
where td_dwh2.mon1 = td_dwh1.mon1
and ( td_dwh4.str_dat11 is null
or td_dwh4.str_dat11 >= td_dwh2.mon3)
and td_dwh5.str_id = td_dwh4.str_id
and td_dwh5.ort_dat11 <= td_dwh2.mon3
and (td_dwh5.ort_dat22 is null
or td_dwh5.ort_dat22 >= td_dwh2.mon3)
and td_dwh1.mon5 = cast(@in_date_region as int)
OPEN my_cur_region
FETCH NEXT FROM my_cur_region
INTO @region, @art
WHILE @@FETCH_STATUS = 0
BEGIN
insert into parameters values(@in_date,@region,@art ,0)
if @art = 1 exec( 'STAT_LK.dbo.stat_v3 @in_date = ''' + @in_date + '''
, @d = ''''
, @w_o = ''''
, @bun = ''''
, @circle = ''' + @region + '''
, @jc-2 = ''''')
else exec( 'STAT_LK.dbo.stat_v3 @in_date = ''' + @in_date + '''
, @d = ''''
, @w_o = ''''
, @bun = ''''
, @circle = ''''
, @jc-2 = ''' + @region + '''')
FETCH NEXT FROM my_cur_region
INTO @region, @art
END
CLOSE my_cur_region
DEALLOCATE my_cur_region
update parameters set aktiv = 1 where bm = @in_date

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

it looks to me like the cursor is calling a stored procedure [STAT_LK.dbo.stat_v3] for each row;

to change this to a set base, wer would need to see the defintiino of that procedure; we cannot tell what it might be doing so far.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kwe477 SSC Eights! Points: 820 More actions · Answer 2

Lowell (5/22/2012)
it looks to me like the cursor is calling a stored procedure [STAT_LK.dbo.stat_v3] for each row;
to change this to a set base, wer would need to see the defintiino of that procedure; we cannot tell what it might be doing so far.

Gulp!!!

I just checked and I hesitate to answer, but that particular SP is 9000 rows long, itself full of cursors, while loops and so forth. I guess it would take days to go through it.

Thanks for your help though, but I guess that's more than could be done on a forum.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

kwe477 (5/22/2012)
Lowell (5/22/2012)
it looks to me like the cursor is calling a stored procedure [STAT_LK.dbo.stat_v3] for each row;
to change this to a set base, wer would need to see the defintiino of that procedure; we cannot tell what it might be doing so far.
Gulp!!!
I just checked and I hesitate to answer, but that particular SP is 9000 rows long, itself full of cursors, while loops and so forth. I guess it would take days to go through it.
Thanks for your help though, but I guess that's more than could be done on a forum.

You may want to start documenting that monster SP so that you can get a handle on what it is doing. You may need to hire an outside consultant to work on rewriting the SP as you may not have the time dependin on what other duties you need to accomplish.

Also, how critical is it for you to modify these procedures. Is it causing issues with your system or is it a nice to do opportunity that could have benefits by improving performance.