performing a loop in the SELECT clause

Question

Post reply

performing a loop in the SELECT clause

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

davidandrews13 SSCertifiable Points: 6792 More actions · Answer 1

1234567teamNo

NULL114310NULL6NULLNULL6

startdate enddate teamName

03 Dec 200203 Dec 2009Wales

the current data will show it as this. it should all be on the one line though.

where 1,2,3,4,5,6,7 are the categoryIDs and the numbers below them are the amount of customers with that category.

it would show the following before i pivotted the table

1....null

2....114

3....310

4....null

5....6

6....null

7....null

davidandrews13 SSCertifiable Points: 6792 More actions · Answer 2

Lynn Pettis (12/4/2009)
Please, when you get a chance, go back and eliminate the cursor. Since there is a set-based solution, you really should use that as it should scale better than the cursor-based solution.

sure thing.

its currently a time issue as to why i haven't done so yet 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

davidandrews13 (12/4/2009)
Lynn Pettis (12/4/2009)
Please, when you get a chance, go back and eliminate the cursor. Since there is a set-based solution, you really should use that as it should scale better than the cursor-based solution.
sure thing.
its currently a time issue as to why i haven't done so yet 🙂

Check out the code link in Madhivanan's post when you have the time. It's elegant and fast, but more importantly it's stood up to the test of baptism by fire some months ago.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

davidandrews13 SSCertifiable Points: 6792 More actions · Answer 4

excellent! using Madhivanan's link i've now got rid of my cursor 🙂

i dont think it makes to much difference in this situation because its currently only bringing back 7 rows anyway but i can see it being very useful in other areas.

thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

davidandrews13 (12/7/2009)
excellent! using Madhivanan's link i've now got rid of my cursor 🙂
i dont think it makes to much difference in this situation because its currently only bringing back 7 rows anyway but i can see it being very useful in other areas.
thanks.

Not a good way to think. Just because it only brings back 7 rows now doesn't mean that will be the case in the future. You should always think about the worst case scenario and plan for scalability.

Grant Fritchey SSC Guru Points: 398713 More actions · Answer 6

Lynn Pettis (12/7/2009)
davidandrews13 (12/7/2009)
excellent! using Madhivanan's link i've now got rid of my cursor 🙂
i dont think it makes to much difference in this situation because its currently only bringing back 7 rows anyway but i can see it being very useful in other areas.
thanks.
Not a good way to think. Just because it only brings back 7 rows now doesn't mean that will be the case in the future. You should always think about the worst case scenario and plan for scalability.

And, just because it's only bringing back 7 rows doesn't mean it's only processing 7 rows. Far too frequently you see a query that returns 1-10 rows, but processes 1-10 million to arrive at the final result. Writing code the right way as often as possible is usually rewarded.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning