Plzz help me in replacing the cursor with Normal Sql Statements

Question

Post reply

Plzz help me in replacing the cursor with Normal Sql Statements

Viewing 13 posts - 16 through 27 (of 27 total)

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

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 1

Mike01 (8/13/2010)
How about using a CTE?

Mike... 2k forum. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 2

WayneS (8/10/2010)
See how this works for you.
-- since you already have a clustered index, we need to dump the table to a temp table
-- where we can build the clustered index that we need for doing the update.
SELECT * INTO #Eam_Member_Eligibility FROM dbo.Eam_Member_Eligibility
CREATE CLUSTERED INDEX [IX_Eam_Member_Eligibility_1] ON #Eam_Member_Eligibility
(
[Subscriber_Id] ASC,
[Start_Date] ASC,
[End_Date] ASC
);
-- declare / initialize needed variables.
declare @Subscriber_Id char(9),
@Start_Date datetime,
@Prev_Subscriber_Id int;
set @Prev_Subscriber_Id = 0;
-- This form of the UPDATE statement has some rules for proper usage.
-- See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/
-- for a complete discussion of how this works, and all of the rules for utilizing it.
UPDATE EME1
SET Span_Num = CASE WHEN @Prev_Subscriber_Id = Subscriber_Id AND EME2.Eligibility_Id IS NULL THEN 1
WHEN @Prev_Subscriber_Id = Subscriber_Id THEN EME1.Span_Num + 1
ELSE 1
END,
@Prev_Subscriber_Id = EME1.Subscriber_Id
FROM #Eam_Member_Eligibility EME1 WITH (TABLOCKX) -- << use the TABLOCKX hint
LEFT JOIN #Eam_Member_Eligibility EME2
ON EME2.Subscriber_Id = EME1.Subscriber_Id
AND EME2.End_Date = dateadd(d,-1,EME1.Start_Date)
OPTION (MAXDOP 1); -- << prevent parallelism!
-- update the main table
UPDATE EME1
SET Span_Nbr = EME2.Span_Nbr
FROM dbo.Eam_Member_Eligibility EME1
JOIN #Eam_Member_Eligibility EME2
ON EME1.Eligibility_Id = EME2.Eligibility_Id

One of the rules has been violated... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

inampudianil (8/24/2010)
Hey Wayne,
Any update on this..plzz help me out..:-)

I'd help but I just don't have the time to fix your data and table so they work together. If you would post the correct table definition and data inserts (AND TEST IT BEFORE YOU POST IT, PLEASE!!!), I could take a look at it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 4

Jeff Moden (8/24/2010)
... I could take a look at it.

So would do others (like me) 🙂

We might be able to find a solution without using joined tables when performing the quirky update (I guess that's what you were referring to, Jeff...).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mike01 SSChampion Points: 11412 More actions · Answer 5

Jeff Moden (8/24/2010)
Mike01 (8/13/2010)
How about using a CTE?
Mike... 2k forum. 🙂

Sorry about that. Didn't realize 🙁

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

inampudianil Ten Centuries Points: 1274 More actions · Answer 6

Hey Jeff,

Thanks for your help and time dude :-D, I tried from my end to solve the problem and Succeeded 100% ,but the only concern is that, it is taking 20 min of my time 3 crore records... Could plzz help in Optimizing the Proc

Select Eligibility_Id, Subscriber_Id,span_num ,start_date,end_date,

(Select Eligibility_Id

From dbo.Member_Eligibility

Where Subscriber_Id = ME.Subscriber_Id and

End_Date = dateadd(d,-1, me.Start_Date)) Prev_Plan_Record

into #span_chk

From dbo.Member_Eligibility ME

;with cte as(select * from

(select *,

(case when Prev_Plan_Record is null then

row_number() over (partition by Subscriber_id order by c.Prev_Plan_Record,c.Eligibility_Id) else null end) as ss from #span_chk c ) as a

), f1 as (select Eligibility_Id,Start_date,End_date,Prev_Plan_Record,Subscriber_Id,ss,(select distinct count(a.ss) from cte a where a.Eligibility_Id<=b.Eligibility_Id and a.Subscriber_Id=b.Subscriber_Id) as span from cte b)

select * from f1 order by 1

Thanks in advance

Anil Inampudi

LutzM SSC Guru Points: 107049 More actions · Answer 7

Now it starts to get confusing...

You posted in a SS2K forum.

When Jeff mentioned it regarding Mike's solution you didn't reply to clarify.

And now you're posting a query that won't work in SS2K as your solution.

Please clarify.

Oh, and it would be great if you could stop calling us names. This is a professional forum not one of your chat rooms.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mike01 SSChampion Points: 11412 More actions · Answer 8

removed to avoid confusion

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

inampudianil Ten Centuries Points: 1274 More actions · Answer 9

Hey

Sorry dude, I am using Sqlserver 2005..y i posted in ss2k is..initilly i made a mistake posting the qury in SS2k..

Select Eligibility_Id, Subscriber_Id,span_num ,start_date,end_date,

(Select Eligibility_Id

From dbo.Member_Eligibility

Where Subscriber_Id = ME.Subscriber_Id and

End_Date = dateadd(d,-1, me.Start_Date)) Prev_Plan_Record

into #span_chk

From dbo.Member_Eligibility ME

;with cte as(select * from

(select *,

(case when Prev_Plan_Record is null then

row_number() over (partition by Subscriber_id order by c.Prev_Plan_Record,c.Eligibility_Id) else null end) as ss from #span_chk c ) as a

), f1 as (select Eligibility_Id,Start_date,End_date,Prev_Plan_Record,Subscriber_Id,ss,(select distinct count(a.ss) from cte a where a.Eligibility_Id<=b.Eligibility_Id and a.Subscriber_Id=b.Subscriber_Id) as span from cte b)

select * from f1 order by 1

the query which i posted is working fine. but the query is having some performance issues.Could you plzz look into it....and try to give any possible solution.

Thanks for your assistance and guidance....

Thanks

Anil

LutzM SSC Guru Points: 107049 More actions · Answer 10

@mike-2: The quirky update method should not be used on joined tables. See Jeff's revised article[/url] for details.

Other than that: as just confirmed the target system is SS2K5. No need for a SS2K workaround.

It seems like the OP still prefer to call us names... So I'll step back.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mike01 SSChampion Points: 11412 More actions · Answer 11

Thanks for the link. I didn't know Jeff revised it.

LutzM (8/25/2010)[hrIt seems like the OP still prefer to call us names... So I'll step back.

I think I'll join you

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 12

inampudianil (8/25/2010)
Hey Jeff,
Thanks for your help and time dude

I realize that there's a bit of a language barrier and that a lot of foreigners think the we like to be called "dude", but I take strong exception to it as I believe you'll find most folks do. Please don't call me "dude" because I'm most definitely not a "dude". My name is Jeff... please stick with that. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 13

LutzM (8/25/2010)
@Mike: The quirky update method should not be used on joined tables. See Jeff's revised article[/url] for details.
Other than that: as just confirmed the target system is SS2K5. No need for a SS2K workaround.
It seems like the OP still prefer to call us names... So I'll step back.

Just in case anyone on this thread missed it, let me say it out loud so that no one misunderstands...

[highlight="Yellow"][font="Arial Black"]THE QUIRKY UPDATE METHOD MUST NOT BE USED ON JOINED TABLES![/font][/highlight]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)