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

General SQL Server Forums

New to SQL Server Programming

cursor

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-28 : 02:56:23

Dear All,
in our appliaction, we have so many procedures and functions using.
at the end of each cursor, we are mentioning
"deallocate"
and "close" commands to free up the memory.

now my doubts are
1)even then the performance decreases?
2)what is the best option to replace the cursor?

i need the guidence from experts like you

Vinod
Even you learn 1%, Learn it with 100% confidence.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-28 : 02:59:46

There is no hard and fix formula to replace cursors. There are many alternatives like using JOINS or subqueries or temporary tables with loop to remodel cursor-based solution. It depends on your requirements and problem at hand.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-28 : 08:20:17

>>what is the best option to replace the cursor?
almost anything is a better option

Almost all people who have done any programming and then get into database development continue using the same procedural techniques that are effective in other technologies. For developing in sql server, a "set based" approach is far more effective then iterating over a set of rows performing the same action once for each row.

>>what is the best option to replace the cursor?
For a specific suggestion provide an example where you are using a cursor I (or someone else) can show you how it could be done with a set-based approach.

Be One with the Optimizer
TG

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-11-28 : 08:47:48

http://weblogs.sqlteam.com/jeffs/archive/2007/04/30/60192.aspx

elsasoft.org

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-11-28 : 08:50:21

quote:
Originally posted by harsh_athalye
...or temporary tables with loop to remodel cursor-based solution.

The temp table/loop method is unlikely to help, since it is the looping, not the use of a cursor itself, that is the root of the inefficiency.

Expect a performance boost between one and two orders of magnitude when converting from cursor/loop based programming to set-based programming.

e4 d5 xd5 Nf6

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-28 : 16:44:30

quote:
Originally posted by blindman

quote:
Originally posted by harsh_athalye
...or temporary tables with loop to remodel cursor-based solution.
The temp table/loop method is unlikely to help, since it is the looping, not the use of a cursor itself, that is the root of the inefficiency.

Expect a performance boost between one and two orders of magnitude when converting from cursor/loop based programming to set-based programming.

e4 d5 xd5 Nf6

I agree with Blindman, that switching to a Looping method in lue of a cursor will not help performance. But, just getting away from a cursor, which can lead to memory leaks and even bring down your server, is worth doing.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-28 : 23:54:05

quote:
Originally posted by TG

>>what is the best option to replace the cursor?
almost anything is a better option

Almost all people who have done any programming and then get into database development continue using the same procedural techniques that are effective in other technologies. For developing in sql server, a "set based" approach is far more effective then iterating over a set of rows performing the same action once for each row.

>>what is the best option to replace the cursor?
For a specific suggestion provide an example where you are using a cursor I (or someone else) can show you how it could be done with a set-based approach.

Be One with the Optimizer
TG

thank you all for the suggesions, i'd like to know what is set based ......
is it nothing but working with joins?

Vinod
Even you learn 1%, Learn it with 100% confidence.

pritika.vats
Starting Member

3 Posts

Posted - 2007-11-29 : 07:30:49

quote:
Originally posted by harsh_athalye

There is no hard and fix formula to replace cursors. There are many alternatives like using JOINS or subqueries or temporary tables with loop to remodel cursor-based solution. It depends on your requirements and problem at hand.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pritika.vats
Starting Member

3 Posts

Posted - 2007-11-29 : 07:35:33

when we r using the cursors in a large program that the deallocation is necessary coz it can improve the performance.and we use joins or some other way to solve the problems like tempory tables,sup queries etc.
depend on ur business requirments

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-29 : 07:40:07

>>i'd like to know what is set based ......

It simply means performing your operation(s) on all the target rows at once rather than performing them once for each row.

Here is a stupid example. Lets say you had a table of dates and you wanted to set all Saturdays to the previous Friday and set all Sundays to the following Monday. A "procedural" approach would be to loop through all rows, test for what day that row is and update that row accordingly, the procede to the next row.

Here is one possible set-based approach to perform the same logic on all rows at once:


update myTable set
	myDate = case
		when datename(weekday, myDate) = 'Saturday' then dateadd(day, -1, myDate)
		when datename(weekday, myDate) = 'Sunday' then dateadd(day, 1, myDate)
		end
from	myTable
where	datename(weekday, myDate) in ('Saturday', 'Sunday')

Be One with the Optimizer
TG

chiman

21 Posts

Posted - 2007-11-29 : 13:03:10

Hi,
I had the same problem with cursors. but got rid of them by using Temp tables and then bulk insert from temp...

Let me know if need to know more on this....

Thanks
Renu

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-30 : 01:05:04

Yes Renu i need more information regarding this

Vinod
Even you learn 1%, Learn it with 100% confidence.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-11-30 : 10:05:01

Then you need to post an example of one of your cursor processes.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources