Which is Better

Question

Which is Better

Viewing 9 posts - 1 through 10 (of 10 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I would say "none"...

I don't see anything that can't be done with a set-based solution, which performs much better. I suggest you to try to convert this into a set based update, maybe using some sort of temp table to store the data you are working with.

Regards

Gianluca

-- Gianluca Sartori

Eswin SSCrazy Eights Points: 8320 More actions · Answer 2

are both same.

can you give me some example.

Tanx 😀

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Eswin (6/26/2009)
are both same.
can you give me some example.

Maybe if you could explain what the innards of that cursor are supposed to do, someone can help. It's rather hard reverse-engineering a cursor.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eswin SSCrazy Eights Points: 8320 More actions · Answer 4

I want to fetch one row at a time and then satisfy the conditions following while @@row_count 0 and while @@fetch_status = 0 .

Will using SELECT statement that assigns values to a variables and then using "while @@row_count 0" allow me to fetch one row at a time and perform queries on it.

Tanx 😀

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Eswin (6/26/2009)
Will using SELECT statement that assigns values to a variables and then using "while @@row_count 0" allow me to fetch one row at a time and perform queries on it.

Yes, but it's no better than a cursor. It's still row-by-row processing. I's still going to be slow. The best way to move on from here is to work out what needs doing to the resultset as a whole (not one row at a time) and convert this entire piece into set-based code.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 6

If you are interested in a set-based solution you might try giving us DDL, sample data and expected output in addition explaining what you are actually trying to do.

I'm not sure if this'll help or not:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

LutzM SSC Guru Points: 107049 More actions · Answer 7

Hi Eswin,

just a few thoughts while you're working on providing the data:

- since you're using DATEPART(WEEKDAY ...) function you need to make sure that the setting for @@datefirst is a value you expect. I'd recommend to use SET DATEFIRST ... at the beginning of your proc.

- your "double-if"

If @day != 1

begin

If @day != 7

begin

can be replaced by IF @day >1 AND @day < 7 (in a set based solution this most probably would go the the WHERE clause)

- you should separate the insert and the update statement in two statements. The @count condition can be replaced by EXISTS rsp. NOT EXIST (to determine what rows need to be updated and what rows need to get inserted) EDIT: or by using inner rsp. left outer join.

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]

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 8

For your date range search, do not use all these filters

and ( ( ( leave.leave_start_dt between @start_date and @end_date)

and (leave.leave_end_dt between @start_date and @end_date) )

or leave.leave_start_dt >= @start_date and leave.leave_start_dt = @start_date and leave.leave_end_dt <= @end_date

or leave.leave_start_dt = @end_date)

Use this simple one instead

and leave.leave_start_dt = @start_date

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

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 9

Eswin (6/26/2009)
I want to fetch one row at a time and then satisfy the conditions following while @@row_count 0 and while @@fetch_status = 0 .

That would be the problem... you have already resigned yourself to working whatever this problem is to one row at a time. Stop thinking about rows... start thinking about what you want to do to a column.

And I agree with the others. State what the problem actually is... not how you think it needs to be solved.

--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)