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.
| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2005-04-11 : 06:11:49
|
I have been doing these optimizations for ages now it seems and I was quite surprised to see what I saw today. I have made 3 different versions of the same procedure:proc1: using a forward-only cursor doing some stuff row-by-rowproc2: same as proc 1 using a table-variable with an identity-field and a while-loop proc3: same as proc 1 using an external procedure 100% set basedQuery cost shows up like this:proc1: 30.42%proc2: 33.19%proc3: 36.39%Is the ol' query analyzer pulling a trick on me?  --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-04-11 : 06:23:22
|
| Not necessarily....without seeing the details of the 3 solutions.A cursor declared as "select * from table1 where x=y" will probably give the same estimated execution plan as the same set-based "select * from table1 where x=y" operation.....but just perform far worse.It's about overall resultant actual performance, rather than 'estimated execution plans'. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2005-04-11 : 07:05:14
|
| Of course it's about overall performance but it's just that I would assume that a cursor (proc 1) would perform worse than at least proc 2. I could have messed up in proc 3 doing something wrong, but proc 1 and 2 are 100% identical except for the fact that proc 2 uses a table-variable instead. When comparing proc 1 & 2 only I get 45.53% and 54.47%. I'm quite surprised by this...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
|
|
|
Cursors busting my ball$