Replacement of 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

SQL Server Administration (2000)

Replacement of Cursor

Author

Topic

arehmanbtc
Starting Member

7 Posts

Posted - 2007-12-11 : 02:08:19

Hi Guys,

I have made a query which uses cursors in that..
what problem i am facing is it is taking 2-4hrs to execute that query.So,Can anybody tell me is there any other way??

Thanks,
sohails

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 04:04:26

We have to see the query before making any assumption.

E 12°55'05.25"
N 56°04'39.16"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-12-11 : 04:24:38

>> is there any other way
yes.

==========================================
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.

arehmanbtc
Starting Member

7 Posts

Posted - 2007-12-11 : 06:17:14

This is the Sp :

create PROC setup_Orglevel6_insert

AS
BEGIN
SET NOCOUNT ON

--Taking the count first
Declare @intorglevel6count INT
DECLARE @UNITID1 AS VARCHAR(10)
DECLARE @UNITID2 AS VARCHAR(10)
DECLARE @UNITID3 AS VARCHAR(10)
DECLARE @UNITID4 AS VARCHAR(10)
DECLARE @UNITID5 AS VARCHAR(10)
DECLARE @UNITID6 AS VARCHAR(10)

--TAKE THE DATA FROM THE MASTER FOR ORGLEVEL 1

Declare Crsr_Orglevel_1insert cursor FAST_FORWARD for
select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 1
open Crsr_Orglevel_1insert
Fetch Next from Crsr_Orglevel_1insert into @UNITID1
while @@Fetch_Status = 0
BEGIN

--TAKE THE DATA FROM THE MASTER FOR ORGLEVEL 2
Declare Crsr_Orglevel_2insert cursor FAST_FORWARD for
select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 2
open Crsr_OrgleveL_2insert
Fetch Next from Crsr_OrgleveL_2insert into @UNITID2
while @@Fetch_Status = 0
BEGIN

--TAKE THE DATA FROM THE MASTER FOR ORGLEVEL 3
Declare Crsr_Orglevel_3insert cursor FAST_FORWARD for
select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 3
open Crsr_OrgleveL_3insert
Fetch Next from Crsr_OrgleveL_3insert into @UNITID3
while @@Fetch_Status = 0
BEGIN

--TAKE THE DATA FROM THE MASTER FOR ORGLEVEL 4
Declare Crsr_Orglevel_4insert cursor FAST_FORWARD for
select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 4
open Crsr_OrgleveL_4insert
Fetch Next from Crsr_OrgleveL_4insert into @UNITID4
while @@Fetch_Status = 0
BEGIN

--TAKE THE DATA FROM THE MASTER FOR ORGLEVEL 5
Declare Crsr_Orglevel_5insert cursor FAST_FORWARD for
select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 5
open Crsr_OrgleveL_5insert
Fetch Next from Crsr_OrgleveL_5insert into @UNITID5
while @@Fetch_Status = 0
BEGIN

--TAKE THE DATA FROM THE MASTER FOR ORGLEVEL 6
Declare Crsr_Orglevel_6insert cursor FAST_FORWARD for

select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 6
open Crsr_OrgleveL_6insert
Fetch Next from Crsr_OrgleveL_6insert into @UNITID6
while @@Fetch_Status = 0
BEGIN

-- Check for the data whether it is present or not

SELECT @intorglevel6count = COUNT(*) FROM SETUP_ORGLevelUnitDetails_TEMP WITH (NOLOCK)
WHERE OLUD_OLM_LevelID = 6 AND OLUD_UnitID = @UNITID6 AND
OLUD_ParentUnitCode1 = @UNITID1 AND
OLUD_ParentUnitCode2 = @UNITID2 AND
OLUD_ParentUnitCode3 = @UNITID3 AND
OLUD_ParentUnitCode4 = @UNITID4 AND
OLUD_ParentUnitCode5 = @UNITID5

IF @intorglevel6count = 0

BEGIN
-- SELECT * FROM Setup_ORGLevelUnitDetails_TEMP where olud_olm_levelid = 1
INSERT INTO Setup_ORGLevelUnitDetails_TEMP (
OLUD_OLM_LevelID,
OLUD_UnitID,
OLUD_ParentUnitCode1,
OLUD_ParentUnitCode2,
OLUD_ParentUnitCode3,
OLUD_ParentUnitCode4,
OLUD_ParentUnitCode5,
OLUD_ParentUnitCode6,
OLUD_ParentUnitCode7,
OLUD_EffectiveFrom,
OLUD_EffectiveTo)
VALUES(6,@UNITID6,@UNITID1,@UNITID2,@UNITID3,@UNITID4,@UNITID5,'X','X','2000-01-01','2050-01-01')
END

-- 6TH CURSOR

-- PRINT @UNITID6 + ',' +@UNITID5 + ',' + @UNITID4 + ',' + @UNITID3 +',' + @UNITID2 +','+ @UNITID1
Fetch Next from Crsr_OrgleveL_6insert into @UNITID6
END
close Crsr_OrgleveL_6insert
deallocate Crsr_OrgleveL_6insert

-- 5TH CURSOR

-- PRINT @UNITID5 + ',' + @UNITID4 + ',' + @UNITID1 +',' + @UNITID2 +','+ @UNITID3
Fetch Next from Crsr_OrgleveL_5insert into @UNITID5
END
close Crsr_OrgleveL_5insert
deallocate Crsr_OrgleveL_5insert
-- 4TH CURSOR
Fetch Next from Crsr_OrgleveL_4insert into @UNITID4
--PRINT '@UNITID4 = ' + @UNITID4
END
close Crsr_OrgleveL_4insert
deallocate Crsr_OrgleveL_4insert

-- 3RD CURSOR
Fetch Next from Crsr_OrgleveL_3insert into @UNITID3
-- PRINT '@UNITID3 = ' + @UNITID3
END
close Crsr_OrgleveL_3insert
deallocate Crsr_OrgleveL_3insert

-- 2ND CURSOR
Fetch Next from Crsr_OrgleveL_2insert into @UNITID2
-- PRINT '@UNITID2 = ' +@UNITID2
END
close Crsr_OrgleveL_2insert
deallocate Crsr_OrgleveL_2insert

--CLOSING THE 1ST CURSOR
Fetch Next from Crsr_OrgleveL_1insert into @UNITID1
END

close Crsr_OrgleveL_1insert
deallocate Crsr_OrgleveL_1insert

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

and This is the table script:

CREATE TABLE [Setup_ORGLevelUnitDetails_TEMP] (
[OLUD_Id] [int] IDENTITY (1, 1) NOT NULL ,
[OLUD_OLM_LevelID] [int] NOT NULL ,
[OLUD_UnitID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OLUD_ParentUnitCode1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OLUD_ParentUnitCode2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OLUD_ParentUnitCode3] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OLUD_ParentUnitCode4] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OLUD_ParentUnitCode5] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OLUD_ParentUnitCode6] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OLUD_ParentUnitCode7] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OLUD_EffectiveFrom] [datetime] NOT NULL ,
[OLUD_EffectiveTo] [datetime] NOT NULL
) ON [PRIMARY]
GO

Thanks,
sohails

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 06:32:50

The very first thing I notice is that for all entries in Level1, you later pick ALL entries for level regardless of "parent" in level 1!
What you bascially is doing is six CROSS JOIN!
All records for Level1 x All records for Level2 x All records for Level3 x All records for Level4 x All records for Level5 x All records for Level6 = shitload of combinations.

At least for each subsequent cursor declaration, include a WHERE statement for filtering for current records only, like this:

Declare Crsr_Orglevel_2insert cursor FAST_FORWARD for
select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 2 AND OLUD_UnitID = @UNITID1
open Crsr_OrgleveL_2insert

Please post some proper and accurate sample data to your table definition and also post your expected output.
Because I have absolutely no idea what you are trying to accomplish here.

E 12°55'05.25"
N 56°04'39.16"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-12-11 : 09:32:29

SELECT @intorglevel6count = COUNT(*) FROM SETUP_ORGLevelUnitDetails_TEMP
....
IF @intorglevel6count = 0

s.b.
if not exists (SELECT * FROM SETUP_ORGLevelUnitDetails_TEMP)

how about this - I think it's what you are doing but I doubt if it's what you want.
insert Setup_ORGLevelUnitDetails_TEMP (
OLUD_OLM_LevelID,
OLUD_UnitID,
OLUD_ParentUnitCode1,
OLUD_ParentUnitCode2,
OLUD_ParentUnitCode3,
OLUD_ParentUnitCode4,
OLUD_ParentUnitCode5,
OLUD_ParentUnitCode6,
OLUD_ParentUnitCode7,
OLUD_EffectiveFrom,
OLUD_EffectiveTo)
select (6,i6.OLUM_UnitId,i5.OLUM_UnitId,i4.OLUM_UnitId,i3.OLUM_UnitId,i2.OLUM_UnitId,i1.OLUM_UnitId,'X','X','2000-01-01','2050-01-01')
from
(select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 1) i1
cross join
(select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 2) i2
cross join
(select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 3) i3
cross join
(select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 4) i4
cross join
(select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 5) i5
cross join
(select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 6) i6
left join SETUP_ORGLevelUnitDetails_TEMP t
on t.OLUD_ParentUnitCode1 = i1.OLUM_UnitId AND
and t.OLUD_ParentUnitCode2 = i2.OLUM_UnitId AND
and t.OLUD_ParentUnitCode3 = i3.OLUM_UnitId AND
and t.OLUD_ParentUnitCode4 = i4.OLUM_UnitId AND
and t.OLUD_ParentUnitCode5 = i5.OLUM_UnitId AND
and t.OLUD_UnitID = i6.OLUM_UnitId AND
and t.OLUD_OLM_LevelID = 6
where t.OLUD_OLM_LevelID is null

See Peso's comment about the 6 cross joins and the 6 cross joins above.

==========================================
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.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-12-11 : 09:37:33

Maybe something like
insert Setup_ORGLevelUnitDetails_TEMP (
OLUD_OLM_LevelID,
OLUD_UnitID,
OLUD_ParentUnitCode1,
OLUD_ParentUnitCode2,
OLUD_ParentUnitCode3,
OLUD_ParentUnitCode4,
OLUD_ParentUnitCode5,
OLUD_ParentUnitCode6,
OLUD_ParentUnitCode7,
OLUD_EffectiveFrom,
OLUD_EffectiveTo)
select (6,i6.OLUM_UnitId,i5.OLUM_UnitId,i4.OLUM_UnitId,i3.OLUM_UnitId,i2.OLUM_UnitId,i1.OLUM_UnitId,'X','X','2000-01-01','2050-01-01')
from
(select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 1) i1
join
(select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 2) i2
on i2.OLUD_ParentUnitCode = i1.OLUM_UnitId
join
(select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 3) i3
on i3.OLUD_ParentUnitCode = i2.OLUM_UnitId
join
(select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 4) i4
on i4.OLUD_ParentUnitCode = i3.OLUM_UnitId
join
(select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 5) i5
on i5.OLUD_ParentUnitCode = i4.OLUM_UnitId
join
(select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 6) i6
on i6.OLUD_ParentUnitCode = i5.OLUM_UnitId
left join SETUP_ORGLevelUnitDetails_TEMP t
on t.OLUD_ParentUnitCode1 = i1.OLUM_UnitId AND
and t.OLUD_ParentUnitCode2 = i2.OLUM_UnitId AND
and t.OLUD_ParentUnitCode3 = i3.OLUM_UnitId AND
and t.OLUD_ParentUnitCode4 = i4.OLUM_UnitId AND
and t.OLUD_ParentUnitCode5 = i5.OLUM_UnitId AND
and t.OLUD_UnitID = i6.OLUM_UnitId AND
and t.OLUD_OLM_LevelID = 6
where t.OLUD_OLM_LevelID is null

==========================================
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.

arehmanbtc
Starting Member

7 Posts

Posted - 2007-12-13 : 00:31:50

Thanks nr for ur response.
i tried ur query but it is giving me error as below

Server: Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ','.
Server: Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'i1'.
Server: Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'i2'.
Server: Msg 102, Level 15, State 1, Line 20
Incorrect syntax near 'i3'.
Server: Msg 102, Level 15, State 1, Line 23
Incorrect syntax near 'i4'.
Server: Msg 102, Level 15, State 1, Line 26
Incorrect syntax near 'i5'.
Server: Msg 102, Level 15, State 1, Line 29
Incorrect syntax near 'i6'.

Thanks,
sohails

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-13 : 03:55:54

Why not follow whatg the error messages say then?


select 6,i6.OLUM_UnitId,i5.OLUM_UnitId, i4.OLUM_UnitId,i3.OLUM_UnitId,i2.OLUM_UnitId,i1.OLUM_UnitId,'X','X','2000-01-01','2050-01-01'
from
(select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 1) i1
join
(select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 2) i2
on i2.OLUD_ParentUnitCode = i1.OLUM_UnitId
join
(select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 3) i3
on i3.OLUD_ParentUnitCode = i2.OLUM_UnitId
join
(select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 4) i4
on i4.OLUD_ParentUnitCode = i3.OLUM_UnitId
join
(select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 5) i5
on i5.OLUD_ParentUnitCode = i4.OLUM_UnitId
join
(select OLUM_UnitId from Setup_ORGLevelUnitMst WITH (NOLOCK) where OLUM_OLM_LevelID = 6) i6
on i6.OLUD_ParentUnitCode = i5.OLUM_UnitId
left join SETUP_ORGLevelUnitDetails_TEMP t
on t.OLUD_ParentUnitCode1 = i1.OLUM_UnitId 
and t.OLUD_ParentUnitCode2 = i2.OLUM_UnitId 
and t.OLUD_ParentUnitCode3 = i3.OLUM_UnitId 
and t.OLUD_ParentUnitCode4 = i4.OLUM_UnitId 
and t.OLUD_ParentUnitCode5 = i5.OLUM_UnitId 
and t.OLUD_UnitID = i6.OLUM_UnitId 
and t.OLUD_OLM_LevelID = 6
where t.OLUD_OLM_LevelID is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources