| Author |
Topic |
|
majkinetor
Yak Posting Veteran
55 Posts |
 Posted - 2006-03-21 : 04:42:11
|
| I don't have much time until the deadline to further investigate all possible methods to avoid cursor. I know there are power users here so I am aksing for some good documentation of various techniques used. Currently I am using inserting desired sql into temporary table with unique index, and after that I iterate throught records with select statements incrementing index. Is this really faster then cursor ? I mean, I am executing select statement for every row plus some overhead in incrementing position variable. I know I can use profiler to measure this, but as I said I currently have no time to play with this, so I have to turn to your help in this matter.Miodragwww.r-moth.comhttp://r-moth.deviantart.com |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
majkinetor
Yak Posting Veteran
55 Posts |
Posted - 2006-03-21 : 05:44:52
|
| madhivanan, do you ever sleep ? No, I guess, according to your answer... You are planning to fail if you don't sleep....To make things clear - your link is not what I wanted, nor would I came here to ask some beginners questions... I usualy search on my own before post some questions, with this to be exclusion - I just wanted some good document about cursor avoidance from you people, nothing more, because I need to do some things quickly so I don't have free time to research. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-03-21 : 07:52:48
|
As a general rule you should try to avoid doing things row-by-row in sql server because the SQL Server performs unfathmably (is that a word??) better when working on complete sets of data instead. That beeing said, your way of replacing the cursor is better than to use a regular cursor but not by as much as one would hope. I don't have any good links for you but look up CASE and DERIVED TABLES in books online and see if you can get anything from it. They opened a whole new world for me  here are a few brief examples:CASE:SELECT Name, City = CASE WHEN Zip = 536465 THEN 'Bangalore' WHEN Zip = 754356 THEN 'New York' ELSE 'Out of this world' ENDFROM tableDerived table:SELECT Name FROM (SELECT Firstname + ' ' + Lastname AS Name FROM table)(totally lame example but the posibilities are endless with derived tables) --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-21 : 08:02:15
|
| >>BangaloreGood to know you are aware of Indian Cities MadhivananFailing to plan is Planning to fail |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-03-21 : 08:06:52
|
Not sure if the zip-code is correct though  --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-21 : 08:41:35
|
quote:
Originally posted by Lumbago
Not sure if the zip-code is correct though --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand"
Yes. You used only Testing zip codes MadhivananFailing to plan is Planning to fail |
|
|
|
majkinetor
Yak Posting Veteran
55 Posts |
Posted - 2006-03-21 : 09:42:03
|
2LumbagoI don't see how that relates to my question except for the first part in which U say that I should avoid row by row examing. Well, I can't avoid it, that is the reason for this question.2Madhivananquote:
Good to know you are aware of Indian Cities
We all finished primary school.www.r-moth.com |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
majkinetor
Yak Posting Veteran
55 Posts |
Posted - 2006-03-22 : 04:45:45
|
| www.r-moth.com |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2006-03-22 : 09:07:05
|
| This question is too general IMO. I think you'll have better response if you post the concrete problem description.Answer to your general question is: Cursor implementation of row by row processing as good as any other (afaik).However, if you post the concrete problem you may find out that you can actually avoid row by row processing. |
|
|
|
|
avoiding cursors