Multiple queries in the same stored procedure

Question

Multiple queries in the same stored procedure

Viewing 10 posts - 31 through 39 (of 39 total)

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

ManicStar SSCoach Points: 17992 More actions · Answer 1

Luis Cazares (1/26/2016)
Manic Star (1/26/2016)
So we had a development. An SQL MVP at days of .Net told our devs that their beloved UberQueries were ok since the QA cached the plans at the statement level, completly ignoring the other variables of parameter sniffing they introduce.
*headdesk*
MVP does not always means wisdom or knowledge.

Except the feedback I get is 'The MVP said it was ok to code like this' which is undoing all my work to convince them to change.

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 2

Manic Star (1/26/2016)
Luis Cazares (1/26/2016)
Manic Star (1/26/2016)
So we had a development. An SQL MVP at days of .Net told our devs that their beloved UberQueries were ok since the QA cached the plans at the statement level, completly ignoring the other variables of parameter sniffing they introduce.
*headdesk*
MVP does not always means wisdom or knowledge.
Except the feedback I get is 'The MVP said it was ok to code like this' which is undoing all my work to convince them to change.

How many MVPs do you need to say something else in order to convince them?

You already have Gail (MVP and MCM) in this thread, and I'll happily weigh in as well (though I am only MVP, never went for MCM). I am sure that we can drum up a few others to weigh in as well, if that would help.

(I'm also interested to know the name of that MVP who gave that advise, but perhaps that is better put in a PM to prevent public bashing)

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

ScottPletcher SSC Guru Points: 100948 More actions · Answer 3

Luis Cazares (1/26/2016)
Manic Star (1/26/2016)
So we had a development. An SQL MVP at days of .Net told our devs that their beloved UberQueries were ok since the QA cached the plans at the statement level, completly ignoring the other variables of parameter sniffing they introduce.
*headdesk*
MVP does not always means wisdom or knowledge.

That might be somewhat hasty and unfair to him/her. After all, we don't know the specific context and/or situation that caused that comment, or even if it is being rendered 100% accurately, human memory being what it is.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

ScottPletcher (1/26/2016)
Luis Cazares (1/26/2016)
Manic Star (1/26/2016)
So we had a development. An SQL MVP at days of .Net told our devs that their beloved UberQueries were ok since the QA cached the plans at the statement level, completly ignoring the other variables of parameter sniffing they introduce.
*headdesk*
MVP does not always means wisdom or knowledge.
That might be somewhat hasty and unfair to him/her. After all, we don't know the specific context and/or situation that caused that comment, or even if it is being rendered 100% accurately, human memory being what it is.

I'm sorry, it wasn't my intention to be disrespectful. I appreciate the job that MVPs do and I know that most of them are completely trustworthy. However, I've seen some give crappy advice, which is an exception to what they usually do.

As some say, you shouldn't trust anyone based solely on words. Make some testing to be sure that the assertions are correct and be sure to have your testing reviewed and the results reproducible.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Manic Star (1/26/2016)
So we had a development. An SQL MVP at days of .Net told our devs that their beloved UberQueries were ok since the QA cached the plans at the statement level, completly ignoring the other variables of parameter sniffing they introduce.
*headdesk*

MVP is a "community service award". Like Luis says, the people who've been awarded MVP are usually pretty darned smart but, just like any group of people, you shouldn't just take their word for something and MVPs aren't necessarily smart. There is no test to getting the MVP award.

Before you take on the idea that I might simply be bashing MVPs, I'm not. I'm just saying that having the MVP award is no guarantee of any particular skill. Some of the MVPs are absolutely brilliant but not because they're MVPs... it's because they smart with or without the award.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 100948 More actions · Answer 6

Jeff Moden (1/26/2016)
Manic Star (1/26/2016)
So we had a development. An SQL MVP at days of .Net told our devs that their beloved UberQueries were ok since the QA cached the plans at the statement level, completly ignoring the other variables of parameter sniffing they introduce.
*headdesk*
MVP is a "community service award". Like Luis says, the people who've been awarded MVP are usually pretty darned smart but, just like any group of people, you shouldn't just take their word for something and MVPs aren't necessarily smart. There is no test to getting the MVP award.
Before you take on the idea that I might simply be bashing MVPs, I'm not. I'm just saying that having the MVP award is no guarantee of any particular skill. Some of the MVPs are absolutely brilliant but not because they're MVPs... it's because they smart with or without the award.

I don't disagree with that. But that wasn't my area of focus anyway, and I didn't specifically mention MVP in my original concerns. I don't think anyone should be "bashed" as incompetent, etc., based just on hearsay.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

ScottPletcher (1/27/2016)
Jeff Moden (1/26/2016)
Manic Star (1/26/2016)
So we had a development. An SQL MVP at days of .Net told our devs that their beloved UberQueries were ok since the QA cached the plans at the statement level, completly ignoring the other variables of parameter sniffing they introduce.
*headdesk*
MVP is a "community service award". Like Luis says, the people who've been awarded MVP are usually pretty darned smart but, just like any group of people, you shouldn't just take their word for something and MVPs aren't necessarily smart. There is no test to getting the MVP award.
Before you take on the idea that I might simply be bashing MVPs, I'm not. I'm just saying that having the MVP award is no guarantee of any particular skill. Some of the MVPs are absolutely brilliant but not because they're MVPs... it's because they smart with or without the award.
I don't disagree with that. But that wasn't my area of focus anyway, and I didn't specifically mention MVP in my original concerns. I don't think anyone should be "bashed" as incompetent, etc., based just on hearsay.

True enough but it does certainly require one to raise a serious eyebrow and do some deep digging. I think that what people are responding to is that if the "hearsay" was what was actually said, then it deserves a bit o' bashing because it's absolutely not true in all cases nor even as a general statement. "It Depends" on how the Uber query was written.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 8

Just my 2 cents... If you want to prove anything to anybody, do it with the code...

Write a 2nd version of the proc (the way you believe it should have been written in the 1st place), build a test harness and pit them against each other.

That way the winner wins, the loser loses and it makes no difference who said what.

ScottPletcher SSC Guru Points: 100948 More actions · Answer 9

Jeff Moden (1/27/2016)
ScottPletcher (1/27/2016)
Jeff Moden (1/26/2016)
Manic Star (1/26/2016)
So we had a development. An SQL MVP at days of .Net told our devs that their beloved UberQueries were ok since the QA cached the plans at the statement level, completly ignoring the other variables of parameter sniffing they introduce.
*headdesk*
MVP is a "community service award". Like Luis says, the people who've been awarded MVP are usually pretty darned smart but, just like any group of people, you shouldn't just take their word for something and MVPs aren't necessarily smart. There is no test to getting the MVP award.
Before you take on the idea that I might simply be bashing MVPs, I'm not. I'm just saying that having the MVP award is no guarantee of any particular skill. Some of the MVPs are absolutely brilliant but not because they're MVPs... it's because they smart with or without the award.
I don't disagree with that. But that wasn't my area of focus anyway, and I didn't specifically mention MVP in my original concerns. I don't think anyone should be "bashed" as incompetent, etc., based just on hearsay.
True enough but it does certainly require one to raise a serious eyebrow and do some deep digging. I think that what people are responding to is that if the "hearsay" was what was actually said, then it deserves a bit o' bashing because it's absolutely not true in all cases nor even as a general statement. "It Depends" on how the Uber query was written.

Regardless of who said it, the idea does need "bashed", but not (necessarily) the person saying it. There's a fundamental difference between "attacking" poor code -- which is quite reasonable to do, even without being sure who wrote it -- but attacking the ignorance of the person that wrote it is only justified once it's known.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Tavis Reddick SSC Eights! Points: 817 More actions · Answer 10

Jason A. Long (1/27/2016)
Just my 2 cents... If you want to prove anything to anybody, do it with the code...
Write a 2nd version of the proc (the way you believe it should have been written in the 1st place), build a test harness and pit them against each other.
That way the winner wins, the loser loses and it makes no difference who said what.

Performance/scalability is one database objective that Paul Nielsen (SQL Server Bible) lists to support the "Information Architecture Principle". As well as Availability, the other four design-related objectives are:

Usability (including simplicity of design, and a well-defined abstraction layer with stored procedures and views)

Extensibility (which is related to simplicity and again involves looking at the abstraction layer as a whole)

Data integrity (it might be advisable for data quality reasons to perform checks on data operations in order to meet complex business rules, even at the expense of slowing them down somewhat)

Security (I suppose that some auditing might slow down some stored procedures)

Nielsen says that while an elegant design may address all these objectives:

"It's the organization's privilege to then prioritize the objectives compared with the relative cost."

So, even if the test was considered fair by all parties, perhaps speed alone will not be enough to pick a winner?