April 22, 2008 at 6:33 am
Dear Experts,
I have written two different queries to acheive the same task. First one is using the Cursor and the second one is using While Loop.
I found that WHILE Loop is faster than the CURSOR. why ? I think cursor will lock that particular table while doing any process but while loop doesn't.
Am i correct ? It would be very helpful if any expert share their suggestion with some good explanation with good examples.
karthik
April 22, 2008 at 6:52 am
Karthik:
In general neither of these alternatives would be my typical first choice at addressing a problem. There are times in which LOOP and CURSOR are the prescribed approach to solving certain problems; however, transact SQL is a language that is oriented at solving set-based problem and therefore it is better to use a set oriented approach to solving problems rather than a record based approach. In general use JOINs rather than LOOPs.
April 22, 2008 at 8:11 pm
There is a whole bulk of topics under "cursors" chapter in BOL.
There are plenty of suggestions, instructions, recommendations and fabulous examples.
Take some time to read it.
If you don't know how to call BOL than as a M.C.A. must know the address msdn.microsoft.com.
You can find everything from BOL on that site.
_____________
Code for TallyGenerator
April 23, 2008 at 12:02 am
It's because you likely did not make a "firehose" (FAST FORWARD) cursor. The real key is, what did you write and why aren't you using set based instead of RBAR?
--Jeff Moden
April 23, 2008 at 3:13 am
Jeff,
Below is my code:
................................................
CREATE PROCEDURE dbo.PAMA_UPD
@dMonth DATETIME,
@Pform VARCHAR(50)= '',
@MId VARCHAR(50) = ''
AS
begin
declare @StDt datetime
declare @EndDt datetime
declare @CheckDt datetime
declare @totCnt integer
declare @MStatus varchar(1)
declare @bCode varchar(20)
declare @bCodeChk varchar(1)
declare @cntExists integer
declare @ErrorDesc varchar(255)
declare @CurPlatform varchar(25)
select @cntExists = 0
IF len((@MId)) > 0
AND len((@Pform)) > 0
begin
Raiserror 99999 'Values Missing'
return
end
IF Len(@MId) > 0 AND len(@Pform)) > 0
begin
RAISERROR 99999 'Empty values are not allowed'
return
end
IF @Pform <> ''
begin
DECLARE curM CURSOR FOR
select MID, Stat, BCd, Pform
FROM MA
where upper(sPlatform) = upper(@Pform)
end
else IF @MId <> ''
begin
DECLARE curM CURSOR FOR
select MID, Stat, BCd, Pform
FROM MA
where upper(MID) = upper(@MId)
end
--open cursor
open curM
-- create the temporary table
CREATE TABLE #pr
(id varchar(20) NOT NULL,
pDt datetime not null,
Tp varchar(1) not null,
gs decimal(10,4) null,
nt decimal(10,4) null,
Stat varchar(1) null,
Pm varchar(25) null)
-- create an index
create nonclustered index id_perf on #pr (id, pDt, Tp, Pm)
--fetch from cursor
fetch curM into @MId , @mstat, @bcd, @CPform
/* iterate records */
while @@sqlstatus = 0
begin
BEGIN TRANSACTION
select @bCdCheck = 'Y'
select @EndDt = @dMonth
select @StDt = min(Dt) from PA where UPPER(sID) = UPPER(@MId) and Tp= 'M'
select @totCnt= DATEDIFF(mm,@StDt,@EndDt)
WHILE @totCnt >= 0
BEGIN
select @CheckDt = dateadd(dd,-(datepart(dd, dateadd(mm,1,@EndDt))), dateadd(mm,1,@EndDt))
select @cntExists = count(*) from PA
where sID = @MId
and datepart(month, dt) = datepart(month, @CheckDt)
and datepart(year, dt) = datepart(year, @CheckDt)
and sType = 'M'
and sPf = @CurPlatform
if (@cntExists = 0) -- it doesn't exists
begin
insert into #pr values(@MId,@CheckDt,'M',null,null,@MStatus,@CurPlatform)
if @@error <> 0
begin
ROLLBACK TRANSACTION
RAISERROR 99999 'Error occured'
break
end
end
select @EndDt = DATEADD(mm,-1,@EndDt)
select @totCnt = @totCnt - 1
select @bck = 'Y'
END
COMMIT TRANSACTION
fetch curM into @MId , @mstat, @bcd, @CPform
end
close curM
deallocate cursor curM
select #pr.id, #pr.pDt, #pr.sType, #pr.gs, #pr.nt, #pr.st,#pr.Pm from #pr
insert PA
select #pr.id, #pr.pDt, #pr.sType, #pr.gs, #pr.nt, #pr.st, #pr.Pm from #pr
end
....................................................................................................
This is the one i have to rewrite,Also it is taking more than 5 hours to complete its execution.
Please help me.
karthik
April 23, 2008 at 6:12 am
Heh... that's a mess, huh? And now, you understand why properly documented code and properly named columns/tables are so valuable. 😛
I'll take a look, Karthick...
--Jeff Moden
April 23, 2008 at 6:36 am
Wanna tell me what's really going on? This code has errors in it in the form of variables that were never declared. That means this code doesn't take 5 hours to run because it doesn't run, period.
--Jeff Moden
April 23, 2008 at 7:40 am
Can you tell me in which part you are getting error message ?
karthik
April 23, 2008 at 8:14 am
Sorry for my mistake.
Please try it and let me know if there is any mistake.I hope i refined all the errors.
-----------------
CREATE PROCEDURE dbo.PAMA_UPD
@dMonth DATETIME,
@Pform VARCHAR(50)= '',
@MId VARCHAR(50) = ''
AS
begin
declare @StDt datetime
declare @EndDt datetime
declare @CheckDt datetime
declare @totCnt integer
declare @MStatus varchar(1)
declare @bCode varchar(20)
declare @bCodeChk varchar(1)
declare @cntExists integer
declare @ErrorDesc varchar(255)
declare @CurPlatform varchar(25)
select @cntExists = 0
IF len((@MId)) > 0
AND len((@Pform)) > 0
begin
Raiserror 99999 'Values Missing'
return
end
IF Len(@MId) > 0 AND len(@Pform) > 0
begin
RAISERROR 99999 'Empty values are not allowed'
return
end
IF @Pform <> ''
begin
DECLARE curM CURSOR FOR
select MID, Stat, BCd, Pform
FROM MA
where upper(sPlatform) = upper(@Pform)
end
else IF @MId <> ''
begin
DECLARE curM CURSOR FOR
select MID, Stat, BCd, Pform
FROM MA
where upper(MID) = upper(@MId)
end
--open cursor
open curM
-- create the temporary table
CREATE TABLE #pr
(id varchar(20) NOT NULL,
pDt datetime not null,
Tp varchar(1) not null,
gs decimal(10,4) null,
nt decimal(10,4) null,
Stat varchar(1) null,
Pm varchar(25) null)
-- create an index
create nonclustered index id_perf on #pr (id, pDt, Tp, Pm)
--fetch from cursor
fetch curM into @MId , @MStatus, @bCode, @CurPlatform
/* iterate records */
while @@sqlstatus = 0
begin
BEGIN TRANSACTION
select @bCdCheck = 'Y'
select @EndDt = @dMonth
select @StDt = min(Dt) from PA where UPPER(sID) = UPPER(@MId) and Tp= 'M'
select @totCnt= DATEDIFF(mm,@StDt,@EndDt)
WHILE @totCnt >= 0
BEGIN
select @CheckDt = dateadd(dd,-(datepart(dd, dateadd(mm,1,@EndDt))), dateadd(mm,1,@EndDt))
select @cntExists = count(*) from PA
where sID = @MId
and datepart(month, dt) = datepart(month, @CheckDt)
and datepart(year, dt) = datepart(year, @CheckDt)
and sType = 'M'
and sPf = @CurPlatform
if (@cntExists = 0) -- it doesn't exists
begin
insert into #pr values(@MId,@CheckDt,'M',null,null,@MStatus,@CurPlatform)
if @@error <> 0
begin
ROLLBACK TRANSACTION
RAISERROR 99999 'Error occured'
break
end
end
select @EndDt = DATEADD(mm,-1,@EndDt)
select @totCnt = @totCnt - 1
select @bck = 'Y'
END
COMMIT TRANSACTION
fetch curM into @MId , @MStatus, @bCode, @CurPlatform
end
close curM
deallocate cursor curM
select #pr.id, #pr.pDt, #pr.sType, #pr.gs, #pr.nt, #pr.st,#pr.Pm from #pr
insert PA
select #pr.id, #pr.pDt, #pr.sType, #pr.gs, #pr.nt, #pr.st, #pr.Pm from #pr
end
-----------------
karthik
April 23, 2008 at 11:37 am
OK, here is my entry. This should be pretty close to what you want. Note that if you do not have case-sensitive collation on your database, then you can get rid of the UPPER() functions, which will help the performance also:
[font="Courier New"]CREATE PROCEDURE dbo.PAMA_UPD @dMonth DATETIME, @Pform VARCHAR(50)= '', @MId VARCHAR(50) = '' AS
begin
IF len(@MId) > 0 AND len(@Pform) > 0
BEGIN
return
END
-- create the temporary table
CREATE TABLE #pr
(id varchar(20) NOT NULL,
pDt datetime not null,
Tp varchar(1) not null,
gs decimal(10,4) null,
nt decimal(10,4) null,
Stat varchar(1) null,
Pm varchar(25) null)
-- create an index
create nonclustered index id_perf on #pr (id, pDt, Tp, Pm)
--====== set up counting tables
SELECT TOP 11000 --thanks to Jeff Moden for this technique
IDENTITY(INT,1,1) AS N
INTO #Numbers
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE #Numbers
ADD CONSTRAINT PK_Numbers_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
Create Table #MonthEnds( Date datetime )
INSERT into #MonthEnds
Select dateadd(dd,-(datepart(dd, dateadd(mm,N,@dMonth))), dateadd(mm,N,@dMonth))
From Numbers
Where N between N and DateDiff(mm, (Select min(Dt) from PA Where Tp='M'), @dMonth)
--=====
BEGIN TRANSACTION
INSERT into #pr -- values(@MId,@CheckDt,'M',null,null,@MStatus,@CurPlatform)
Select MID, MonthEnds.Date, 'M', null, null, Stat, Pform -- BCd,
FROM MA
Join MonthEnds ON MonthEnds.Date
between (Select Min(Dt) from PA where UPPER(sID) = UPPER(MA.MId) and Tp= 'M')
and DateAdd(mm, 1, @dMonth)
Where ( (@Pform <> '' AND upper(sPlatform) = upper(@Pform))
Or (@MId <> '' AND upper(MA.MID) = upper(@MId)) )
And Exists(Select * from PA
Where PA.sID = MA.MId
and datepart(month, PA.dt) = datepart(month, MonthEnds.Date )
and datepart(year, PA.dt) = datepart(year, MonthEnds.Date )
and PA.sType = 'M'
and PA.sPf = MA.Pform )
COMMIT
--
Select #pr.id, #pr.pDt, #pr.sType, #pr.gs, #pr.nt, #pr.st,#pr.Pm from #pr
Insert PA
Select #pr.id, #pr.pDt, #pr.sType, #pr.gs, #pr.nt, #pr.st, #pr.Pm from #pr
end
[/font]
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 24, 2008 at 7:12 am
rbarry,
Thanks a lot.
what do you mean by 'N Between N' ?
I tried out it in a sample query. It is showing NULL in the first Row.Remaining rows are showing the values.
Please correct me if i am wrong.
karthik
April 24, 2008 at 7:22 am
Sorry for not getting back to this sooner... Barry went in the same direction that I would have. (Thanks for the honorable mention in the code, Barry :))
Karthick, look around in the code... Do you see any table that has a column named "N" in it?
--Jeff Moden
April 24, 2008 at 7:33 am
Jeff,
Ok. No problem.
As you said, He used N as a column name.Please take a look at below the code.
SELECT TOP 11000 --thanks to Jeff Moden for this technique
IDENTITY(INT,1,1) AS N
INTO #Numbers
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
But, what is the relationship between these two statements ?
I am very much confused.
I have executed the below one.
...........................................
select distinct ID = identity(1,1,int), LTrim(RTrim(Prod)) As ProdDesc
into #t2
from Stock order by Prod
...........................................
Sample Output:
ID ProdDesc
1Stock
2Fee
3MFee
4Fund
5Share
My query is:
--------------
select * from #t2
where ID between ID and 5 -- I just replaced N with ID.Any way we have to use the column name.
---------------------
Output:
ID ProdDesc
1NULL
2Fee
3MFee
4Fund
5Share
why ? Please explain it.
karthik
April 24, 2008 at 7:47 am
I'm sorry... my bad... not enough coffee. I thought you wanted to know what the "N" was (as most frequetly do) and didn't read the rest of the WHERE clause that Barrry wrote... maybe I've still not had enough coffee, but I'm confused by that WHERE clause, as well.
Barry, would you explain why you used WHERE N BETWEEN N AND somedateformula? I think it should have been WHERE N BETWEEN 1 AND somedateformula.
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply