Creating Sequence Numbers
-
As I already said, even with only 1000 rows in the base table, the server will have to process up to 1 000 000 rows. That's why I'll often preffer the temp table method (unless I know for absolute, total fact that there'll never be more than a few 100 rows processed).
-
Good points. As for seeing it in other posts you might from now on. As you said I'm very aware of the numbers table but it had never occured to me to use it in that very particular case. Now I'll make a point to see when it can be used.
Thanx again.
-
I agree, the correlated subquery is less than ideal and can get out of hand quickly. The other option I posted, the self join and group by, would probably perform much better but I doubt it would perform as well as your and John's ideas.
SQL guy and Houston Magician
-
I can already answer this one for you. I had been using that method for a while untill I saw the exponential part in the execution plan. So that's when I found a new good use of temp tables
.You method is fine for small datasets or when you'll have just a few rows per main id. However this method is not garanteed to scope well and the cost of the query is somewhat unpredictable. The cost of the temp table method willl be linear. Because the only factor in this will be the number of rows to process, and they'll all have to be processed only three times (insert/update/select). In the self join / subquery, if you have more than 3 rows average per main id, then the cost will alredy be greater than the temp table (in rows to process).
-
Cool, thanks for the follow-up Ninja. I always like learning new (and better) ways to approach a problem.
SQL guy and Houston Magician
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply