| Author |
Topic |
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
 Posted - 2006-11-23 : 04:14:09
|
| I know with SQL cursors have been cursed with being slow, i'm wondering what is the best alternative, is it still this one:http://www.sql-server-performance.com/dp_no_cursors.aspor has someone thought of anything else...I just wanna know a simple alternative in general i know it matter which application but im curious in general...or should a temp table be used...? |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2006-11-23 : 04:29:26
|
| A set-based solution is always going to give you the best performance. Just ask yourself repeatedly "do i really need to do this row-by-row?"Mark |
 |
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2006-11-23 : 04:33:54
|
| im new at this , what exactly is a set based solution? |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-11-23 : 04:41:35
|
Set-based solution = NOT (procedural row-by-row solution)  Harsh AthalyeIndia."Nothing is Impossible" |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2006-11-23 : 04:44:23
|
| any examples of a simple cursor side by side to set based solution, or something that explains it a little more then harsh's example? |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2006-11-23 : 04:52:17
|
| like for example if u wanted to create a new field and assign each row a new value which is incremented by one, surely u cant do this set based |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-11-23 : 05:00:48
|
Plenty of such examples are there, but I will give you a simple one!Lets say there is table called Acct_tran (Account Transactions) and has following structure:create table acct_tran( acct_no int, tran_dt datetime, cr_bal numeric(10,2), dr_bal numeric(10,2)) and has following data:insert into acct_transelect 1, '20060101', 100, 0 union allselect 1, '20060102', 0, 50 union allselect 1, '20060103', 160, 0 union allselect 2, '20060101', 0, 80 union allselect 2, '20060102', 0, 570 union allselect 2, '20060103', 700, 0 union allselect 3, '20060101', 860, 0 union allselect 3, '20060102', 0, 80 Now we want to know the running balance for each account like this:acct_no cr_bal dr_bal balance ----------- ------------ ------------ ---------------------------------------- 1 100.00 .00 100.001 .00 50.00 50.001 160.00 .00 210.002 .00 80.00 -80.002 .00 570.00 -650.002 700.00 .00 50.003 860.00 .00 860.003 .00 80.00 780.00 Then we can have following query to calculate running balance:select acct_no, cr_bal, dr_bal, (select sum(cr_bal - dr_bal) from acct_tran b where a.acct_no = b.acct_no and b.tran_dt <= a.tran_dt) balancefrom acct_tran aorder by acct_no, tran_dt rather than looping through entire table to calculate running balance.Harsh AthalyeIndia."Nothing is Impossible" |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2006-11-23 : 05:16:52
|
quote:
like for example if u wanted to create a new field and assign each row a new value which is incremented by one, surely u cant do this set based
You might want to look up IDENTITY before writing that cursor!  Mark |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-23 : 05:18:32
|
| Or create a CURSOR based on Acct and TranDate?Peter LarssonHelsingborg, Sweden |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2006-11-23 : 05:24:32
|
| wow thats good, now gotta try understand that :) |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2006-11-23 : 05:51:53
|
| just a questions , uve set a and b... what is their purpose, and excuse my silly question but what are they considered (e.g variable?) |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2006-11-23 : 06:04:10
|
These are aliases. Writing SELECT a.Column1, a.Column2 FROM MyLongNamedTableTable a is a shorthand way of writing SELECT a.Column1, a.Column2 FROM MyLongNamedTableTable AS a and is more succinct than writing SELECT MyLongNamedTableTable.Column1, MyLongNamedTableTable.Column2 FROM MyLongNamedTableTable It's a good habit to get into as it saves repeating long table names all over the place, and improves maintainability. |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2006-11-23 : 06:11:49
|
| excellent, man this forum is good... (fast too)okay one more thing whats the purpose of this"b.tran_dt <= a.tran_dt"why are u making sure date is less then or equal to? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-23 : 06:13:52
|
| So that no other transactions are calculated (future ones, regarding to current transactiondate).Peter LarssonHelsingborg, Sweden |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-11-23 : 08:42:28
|
| SET-BASED.......use a large spoon to put sugar in a tea-cup.CURSOR-BASED....use a large tweezers to put sugar in a tea-cup...grain by grain!!! |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2006-11-23 : 08:46:59
|
| yeah i understand the meaning i just cant get my mind around understanding the logic, how the computer is working in set based examples such as one mensioned above...? isnt it still looking threw eachother field, i think i just need to visualise the steps better, that the computer is taking |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-11-23 : 09:08:05
|
| Set-based solution is better because we let SQL server handles the way it want to perform the processing...In some case, it may even process two steps in parallel.Harsh AthalyeIndia."Nothing is Impossible" |
|
|
|
|
are cursor's still slow??