August 21, 2012 at 7:05 pm
Steve Jones - SSC Editor (8/21/2012)
Brandie Tarvin (8/21/2012)
At my workplace, we forbid the Devs to use cursors. It's part of our db standards. Any time they want to go against standards, they have to justify it with a well-reasoned business case. Most of the time they can't.
If they can justify it, though, we actually do bend and allow the code. It's just that we don't want Devs writing cursors because that's what they know.
That's been my approach. It's a rule until it needs an exception. Then we grant it.
I would love to do this except that too many of our developers would be crying Crockodile Tears!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 21, 2012 at 7:12 pm
Stefan Krzywicki (8/21/2012)
dwain.c (8/21/2012)
It has been a rare experience for me to post a question to this forum, but I will now. How about a poll?Yesterday I was called upon to go back and look at the first SP I ever wrote (about 15 months ago). The SP is quite complicated, so much so that during its development nobody on the development team could do it right. So I wrote it from scratch, and with my very limited SQL experience at the time, I did the unspeakable and solved the problem with 2 nested CURSORs. That's right - 2 CURSORs! :w00t: In my defence I will say that this occurred before I adoped my mantra. 😀
Sometime after that, and after writing one other SP that contains a CURSOR (1 only, I swear!), I stumbled upon a statement by someone (can't remember who) that said "in your career you should write no more than 5 CURSORs." I take some solace in the fact that my total stands at 3 and I've never again had to resort to one since I came across that quotation.
So now onto my question.
If I rewrite this SP to use 1 or 0 CURSORs, will the SQL-verse acknowledge this reduction in my career CURSOR usage and lower my career total CURSOR count?
There are a number of cases where you simply have no recourse but to use a cursor or a loop. The two I've written recently, one queries a folder and imports all the files it finds and one checks a table and sends a customized email for each row. There have likely been others, but those are the two that come to mind immediately.
I have written several extremely effective set-based, iterative loops. One that springs to mind:
I had a case where I needed to create a "rating" (pricing) SP. There were 5 components to price and one of those had two different options, one of which was much slower than the other, having to rate out of a different (more detailed) set of tables.
When it came to apply that SP to a batch of items to be priced (thousands of them in a batch), doing it with one call to the SP was possible but the run time per item was quite expensive. Instead I broke the batch up, first segregating on items that had pricing scenario 1 (which I rated 50 at a time) and the other where the pricing was scenario 2 (which I rated 100 at a time).
This approach yielded blazing speed. Well, good enough anyway.:-P
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 22, 2012 at 1:22 am
Grrr. Why do some people want to nitpick holes in things I say and do it in a way that sounds very much like a personal attack?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2012 at 1:54 am
GilaMonster (8/22/2012)
Grrr. Why do some people want to nitpick holes in things I say and do it in a way that sounds very much like a personal attack?
If this is the same individual as last time (and the one safely capped by Paul), then I'd give it one last shot to be absolutely sure it's personal before going formal with it. From this angle it's not easy to tell because he gets this confrontational with other folks from time to time. Your restraint, on the other hand, has been commendable.
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
August 22, 2012 at 2:03 am
Yes, same one, and it's the thread with Paul involved.
"But it's not the only target people could chase with the statement, would you agree?"
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2012 at 2:03 am
ChrisM@Work (8/22/2012)
If this is the same individual as last time
He who must not be named...
Between wizards and Jedi, this board is always interesting and fun... 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 22, 2012 at 2:12 am
dwain.c (8/22/2012)
ChrisM@Work (8/22/2012)
If this is the same individual as last timeHe who must not be named...
Between wizards and Jedi, this board is always interesting and fun... 🙂
Dwain, that monster fish in your avatar...you look like you are about to take a bite out of it.
Extreme sashimi.
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
August 22, 2012 at 2:18 am
GilaMonster (8/22/2012)
Yes, same one, and it's the thread with Paul involved."But it's not the only target people could chase with the statement, would you agree?"
Only involved by blog proxy, so far, but that could change. Reading the thread, I particularly like the way he has invented a new way for CHECKPOINT to work (among other misconceptions).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 22, 2012 at 2:26 am
ChrisM@Work (8/22/2012)
dwain.c (8/22/2012)
ChrisM@Work (8/22/2012)
If this is the same individual as last timeHe who must not be named...
Between wizards and Jedi, this board is always interesting and fun... 🙂
Dwain, that monster fish in your avatar...you look like you are about to take a bite out of it.
Extreme sashimi.
If you could zoom the shot you would note the grimace on my face. Trust me it was not hunger, rather the agony of holding that bad boy up for the picture.
Of course, you would also see my shoulder muscles rippling with the effort. That's the part I like. 😀
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 22, 2012 at 2:40 am
dwain.c (8/22/2012)
ChrisM@Work (8/22/2012)
dwain.c (8/22/2012)
ChrisM@Work (8/22/2012)
If this is the same individual as last timeHe who must not be named...
Between wizards and Jedi, this board is always interesting and fun... 🙂
Dwain, that monster fish in your avatar...you look like you are about to take a bite out of it.
Extreme sashimi.
If you could zoom the shot you would note the grimace on my face. Trust me it was not hunger, rather the agony of holding that bad boy up for the picture.
Of course, you would also see my shoulder muscles rippling with the effort. That's the part I like. 😀
I was warned as a child not to trust a guy in a pink shirt holding a fish.
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
August 22, 2012 at 2:51 am
SQL Kiwi (8/22/2012)
GilaMonster (8/22/2012)
Yes, same one, and it's the thread with Paul involved."But it's not the only target people could chase with the statement, would you agree?"
Only involved by blog proxy, so far, but that could change.
No, I meant the one you're posting in. Order by in an insert.
The update thread needs to be abandoned with prejudice.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2012 at 2:53 am
dwain.c (8/22/2012)
ChrisM@Work (8/22/2012)
dwain.c (8/22/2012)
ChrisM@Work (8/22/2012)
If this is the same individual as last timeHe who must not be named...
Between wizards and Jedi, this board is always interesting and fun... 🙂
Dwain, that monster fish in your avatar...you look like you are about to take a bite out of it.
Extreme sashimi.
If you could zoom the shot you would note the grimace on my face. Trust me it was not hunger, rather the agony of holding that bad boy up for the picture.
Of course, you would also see my shoulder muscles rippling with the effort. That's the part I like. 😀
Good thing I'm not wearing a shirt!
Going back in Nov to try for the big one. No more minnows.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 22, 2012 at 3:04 am
GilaMonster (8/22/2012)
No, I meant the one you're posting in. Order by in an insert.
Oh right. I wonder what he'll come up with next on that one.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 22, 2012 at 3:04 am
GilaMonster (8/22/2012)
Grrr. Why do some people want to nitpick holes in things I say and do it in a way that sounds very much like a personal attack?
Same reason you get all sorts of picky little nits thrown at you when you write an article. People are trying to play "stump the chump" but lose track of who the chump is supposed to be.
"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
August 22, 2012 at 3:09 am
Grant Fritchey (8/22/2012)
GilaMonster (8/22/2012)
Grrr. Why do some people want to nitpick holes in things I say and do it in a way that sounds very much like a personal attack?Same reason you get all sorts of picky little nits thrown at you when you write an article. People are trying to play "stump the chump" but lose track of who the chump is supposed to be.
That is probably true! Some people get a thrill from trying to make an expert look bad or trying to prove they're better than the expert.
Putting your credentials in you signature is like painting a target on your throat.
That's why I don't put any credentials in my signature. Of course, not having any may be a contributing factor.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 37,576 through 37,590 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply