November 3, 2011 at 4:23 pm
Evil Kraig F (11/3/2011)
SQLRNNR (11/3/2011)
GSquared (11/3/2011)
I just found a stored procedure from my predecessor at my current job, and it just kind of made my jaw drop in the elegant simplicity of it's complete stupidity.It looked basically like this:
create proc dbo.MyProcas
set nocount on;
declare @TableVariable (
columns, including PK definition)
insert into @TableVariable
select distinct columns
from tables
where business rules
select distinct * from @TableVariable
The actual query involves several computed columns and joins between four different tables. It works as designed.
I discovered this query because it started to throw a primary key violation on what everyone here thought was just a Select statement. Note that it doesn't actually DO anything with the table variable except select from it. No data modifications. Not even a Where clause on the select.
How does someone come up with a design like that? Why? What weird logic process leads to doing all that just to select some data.
Sorry, had to vent, and thought you guys might get some entertainment out of this.
PS: Even our business analyst thought this one was a bit odd in terms of working a Select that way. He doesn't even write code.
That is pure awesomeness
I personally am quite impressed at the double distinct. When in doubt, overkill!
Clearly an immortal understanding of TSQL
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 3, 2011 at 4:25 pm
Evil Kraig F (11/3/2011)
Anyone else been having serious problems with posting today? Edits, quotes, even just add reply is being feisty. Won't seem to trigger the post creation page, then it takes two or three tries of 'post this' to get it to go through, and half the time it won't take.Getting annoying.
Dear Sincerely Annoyed,
NOPE - haven't had a problem with that at all.:-D:-D:-D:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 3, 2011 at 7:17 pm
No other reports to me, at least that I saw when I got back in town this afternoon.
November 3, 2011 at 8:16 pm
Well, my daughter Kirisa was just inducted into the National Honor Society ths evening!
November 3, 2011 at 9:03 pm
Lynn Pettis (11/3/2011)
Well, my daughter Kirisa was just inducted into the National Honor Society ths evening!
Congrats
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 3, 2011 at 10:58 pm
For something a little different, I thought I'd put The Thread back on topic for a bit as I go running back to the TITD (which I haven't visited in a while so I hope the hippo remembers me!).
I really wonder about some of the people that come here. Some of the questions are just so elemetary that I can't believe someone actually has to ask the question.
November 3, 2011 at 11:31 pm
BUT WAIT, THERE"S MORE!
Now that we have moved data from B to A, there is more that needs to be done that we didn't tell you.
Come on people, tell us the whole story up front, not this piece meal crap.
Party Time at the TITD!
November 4, 2011 at 4:55 am
Lynn Pettis (11/3/2011)
BUT WAIT, THERE"S MORE!Now that we have moved data from B to A, there is more that needs to be done that we didn't tell you.
Come on people, tell us the whole story up front, not this piece meal crap.
Party Time at the TITD!
Just had one of those myself - but not in the forums 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 4, 2011 at 5:50 am
Lynn Pettis (11/3/2011)
Well, my daughter Kirisa was just inducted into the National Honor Society ths evening!
Congrats!
-Ki
November 4, 2011 at 6:04 am
Lynn Pettis (11/3/2011)
Well, my daughter Kirisa was just inducted into the National Honor Society ths evening!
Congrats Lynn, you must be a proud Papa. 🙂
-Roy
November 4, 2011 at 6:32 am
Lynn Pettis (11/3/2011)
I really wonder about some of the people that come here. Some of the questions are just so elemetary that I can't believe someone actually has to ask the question.
The only stupid question is the one you didn't ask. Because that's the one you really need the answer to.
I remember being a noob at many things, and needing to ask what the pros might consider "elementary" questions. Getting the answers helped me a lot.
EDIT: Congrats to your daughter, BTW.
November 4, 2011 at 6:39 am
WHOOT! Just got my author's copy of Space Tramps in.
Space Tramps information:
Amazon link: http://www.amazon.com/Space-Tramps-Full-Throttle-Tales/dp/0984592741
ISBN numbers, if you care to order it from your local bookstore (which I highly encourage!):
ISBN-10: 0984592741
ISBN-13: 978-0984592746
If you do happen to read it, I'd love to hear reviews.
November 4, 2011 at 6:45 am
Lynn Pettis (11/3/2011)
Well, my daughter Kirisa was just inducted into the National Honor Society ths evening!
Congratulations. That's definitely a big deal.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 4, 2011 at 6:48 am
Brandie Tarvin (11/4/2011)
Lynn Pettis (11/3/2011)
I really wonder about some of the people that come here. Some of the questions are just so elemetary that I can't believe someone actually has to ask the question.The only stupid question is the one you didn't ask. Because that's the one you really need the answer to.
I remember being a noob at many things, and needing to ask what the pros might consider "elementary" questions. Getting the answers helped me a lot.
EDIT: Congrats to your daughter, BTW.
Starting at the bottom, thank you. She has worked hard to achieve this honor.
As for how elementary, check this one out.
November 4, 2011 at 6:50 am
Kiara (11/3/2011)
GSquared (11/3/2011)
I just found a stored procedure from my predecessor at my current job, and it just kind of made my jaw drop in the elegant simplicity of it's complete stupidity.It looked basically like this:
create proc dbo.MyProcas
set nocount on;
declare @TableVariable (
columns, including PK definition)
insert into @TableVariable
select distinct columns
from tables
where business rules
select distinct * from @TableVariable
The actual query involves several computed columns and joins between four different tables. It works as designed.
I discovered this query because it started to throw a primary key violation on what everyone here thought was just a Select statement. Note that it doesn't actually DO anything with the table variable except select from it. No data modifications. Not even a Where clause on the select.
How does someone come up with a design like that? Why? What weird logic process leads to doing all that just to select some data.
Sorry, had to vent, and thought you guys might get some entertainment out of this.
PS: Even our business analyst thought this one was a bit odd in terms of working a Select that way. He doesn't even write code.
I can only think of one reason - and the only reason I can actually think of it is because I just had to write something similar. (Similar, I said. Not exactly like.) Before you all start throwing tomatoes and other rotten fruit at me...
I have a situation where one of the applications I don't manage makes calls to SQL to pull data. I can stage the data for that app in a matter of ms. However, it can take that program 10-15 minutes to actually process it - the wait stats on the process can be amazingly annoying. Oh - and that program asks for new data literally every 5 minutes.
What was originally happening was that because that app can't consume the data anywhere near as quickly as I can send it, I had locks sitting on tables that other people were trying to use, but couldn't get to until the app was finished. (I got our dev folks to radically change their design, but ultimately I'm passing data to a noSQL environment that can't do set-based reads. Long story, many details, options, and other attempts to fix the problem left out of the mix here.)
The end fix was very similar to the code above. I'm basically having that program call a stored proc with a couple of parameters - then loading the data that will be returned into a table variable. Quick select against my normal db tables, locks released quickly, happy users. Then, when I select from that table variable, that connection can sit and do it's row by row chunking through the data I sent it to its heart's content without sending my side of the universe into block and deadlock hell.
So - I had a very practical reason to write something along those lines (and if someone wants to show me a better way to handle that situation, I'm happy hear it - just because I found something that both works and makes my server happy doesn't mean I'm convinced that it's either the only way or the best way to solve a particular challenge.)
I'm curious as to what the proc you found is being used for...
Your situation sounds like a good case for Snapshot Isolation. Have you tried that?
This one is used to populate a list on a web page. It doesn't even hold the connection open after that, much less lock the data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 31,381 through 31,395 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply