Are the posted questions getting worse?
-
WayneS (1/27/2015)
Lynn Pettis (1/27/2015)
Brandie Tarvin (1/27/2015)
Oh, hey! Let's all go to Russia in the middle of a war!Dear SQLSaturday Speakers,
This is a one-time system message for SQLSaturday #398. SQLSaturday #398 is coming to you on Jun 06, 2015 at Kommunarov st, 268?, Krasnodar, Russia . You are getting this email because you have spoken at previous SQLSaturday events. Please consider speaking again at SQLSaturday #398. You can submit your session by using the call for speakers page.
We look forward to hopefully seeing you as a speaker for SQLSaturday #398. If you have any questions feel free to contact the Event Admins at sqlsaturday398@sqlsaturday.com
Warm Regards,
Team SQLSaturday
Because... FUN.
(Sorry, I'm a little cynical of these SQL Saturdays that are popping up in hot zones).
There hasn't been a SQL Saturday here in Afghanistan yet. Not looking to have one before I leave either.
Ah, come on Lynn. Just chat with Karla - she'll let you do it. :w00t::w00t:
Yea, right. 31 days.......
-
Yesterday in Denver
-
Steve Jones - SSC Editor (1/27/2015)
Yesterday in Denver
While Jacksonville, FL spent most of the morning in the 40s and only just got up to 53 a little bit ago.
This is me sticking out my tongue at you. Pbbbbttttt.
-
So it seems the snow blizzard of the century in New York was just a mild breeze?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Koen Verbeeck (1/27/2015)
So it seems the snow blizzard of the century in New York was just a mild breeze?Not from what I've heard out of NYC. And here in Boston we've got around 2 feet and it is still coming down.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams -
Here in NH we have a little lull going on, but this is what my driveway looks like:
Unfortunately the truck with the plow attached isn't running safely, so I have to pay someone to plow and they aren't pushing the snow back as far as I would so my parking area is going to shrink.
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 -
Jack Corbett (1/27/2015)
Here in NH we have a little lull going on, but this is what my driveway looks like:Unfortunately the truck with the plow attached isn't running safely, so I have to pay someone to plow and they aren't pushing the snow back as far as I would so my parking area is going to shrink.
I think it looks pretty. Of course all we have gotten here is rain and mud.
-
Lynn Pettis (1/27/2015)
I think it looks pretty. Of course all we have gotten here is rain and mud.It is pretty, but I also see a certain amount of pain along with it. How long has it been since you've seen snow? It hasn't been too long, has it?
-
Ed Wagner (1/27/2015)
Lynn Pettis (1/27/2015)
I think it looks pretty. Of course all we have gotten here is rain and mud.It is pretty, but I also see a certain amount of pain along with it. How long has it been since you've seen snow? It hasn't been too long, has it?
We get some snow here, not a lot. Most of the snow is on/in the Mountains to West, North, and East of us.
-
Brandie Tarvin (1/27/2015)
venoym (1/27/2015)
BrainDonor (1/27/2015)
Sean Lange (1/23/2015)
Oh the cursors are unbelievably stuipd. They are used to count!!!
set @Counter = 1
while exists(select * from StupidlyLargeHeap (nolock) where someColume = @Counter) begin
set @Counter = @Counter + 1
end
It must be a way of thinking - I worked at a place where very similair code was used to make a count of certain rows, and then the count was merely checked for > 0. Replaced it with EXISTS. And the really sad thing was the Developer who wrote it had said I'd never find a way to optimise the code.
That's not actually a counter. I know it's named that way. But it's actually looking for the first gap in a number sequence (yes, there is a MUCH easier way)
maybe something like...
SET @Counter = ISNULL((select TOP(1) slh1.someColume + 1
from StupidlyLargeHeap slh1 (nolock)
LEFT OUTER JOIN StupidlyLargeHeap slh2 (nolock)
ON slh1.someColume + 1 = slh2.someColume
WHERE slh2.ID is Null), 0)
Hmmm. Venoym , that actually doesn't work. I tested it with a table I have that actually has an identity property (but has had values removed).
The WHILE loop gave me a value of 10. I reset the counter and tried it with your code and came up with 13392707. I checked the table identities and verified that 10 is indeed the first "gap" in the sequence.
EDIT: WOW. Running your code a second and a third time gave me even different values from 13392707. And I verified that I reset the counter to 1 each time with a SELECT statement. Of course, I did change the WHERE clause, so I need to fiddle a little more and see if that's the issue.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]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 -
Brandie Tarvin (1/27/2015)
WayneS (1/27/2015)
Brandie Tarvin (1/27/2015)
Excuse me while I stand over in my corner gloating that I figured something out all by my lonesome without looking anything up. YES!Congrats Brandie. That sure is a great feeling.
BTW, are you using SQL 2012? If so, check out the LEAD function for getting the gap even easier.
We are about to move to SQL 2012, so I will check out the LEAD function. Thanks, Wayne.
LEAD (or LAG) both provide quite intuitive methods for find gaps I agree. I'm just a little surprised you'd recommend them given the performance characteristics:
SQL Server 2012 Performance Test: Gap Detection[/url]
Although at times the simplest solution may be good enough.
There are many traditional and non-traditional methods for finding gaps:
The SQL of Gaps and Islands in Sequences[/url]
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]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 -
... Mark one off, 31 days on the calendar to go. 31 days on the calendar to go, 31 days to go, ...
-
Lynn Pettis (1/27/2015)
... Mark one off, 31 days on the calendar to go. 31 days on the calendar to go, 31 days to go, ...A standard month. It will be over in a ...blimey I was going to say "flash" but I don't think so.
It will be over before you know it.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
dwain.c (1/27/2015)
Brandie Tarvin (1/27/2015)
WayneS (1/27/2015)
Brandie Tarvin (1/27/2015)
Excuse me while I stand over in my corner gloating that I figured something out all by my lonesome without looking anything up. YES!Congrats Brandie. That sure is a great feeling.
BTW, are you using SQL 2012? If so, check out the LEAD function for getting the gap even easier.
We are about to move to SQL 2012, so I will check out the LEAD function. Thanks, Wayne.
LEAD (or LAG) both provide quite intuitive methods for find gaps I agree. I'm just a little surprised you'd recommend them given the performance characteristics:
SQL Server 2012 Performance Test: Gap Detection[/url]
Although at times the simplest solution may be good enough.
There are many traditional and non-traditional methods for finding gaps:
I must admit... I'm laughing a bit at this line of discussion. It's a good discussion, don't get me wrong. I simply tossed up something in almost, what I thought was, a sarcastic manner (no code on the Thread). I don't even have SQL Server right now (moved to a new job) and so couldn't test it. The only problem with using a Tally Table for a stupidly large heap is that the heap is probably larger than the Tally table.
An index on the ID (even unclustered) would solve the ordering problem with the TOP (1), but you could also add an "ORDER BY sdlh1.ID DESC" to it to make it work properly... It would likely be almost as fast.
-
venoym (1/28/2015)
An index on the ID (even unclustered) wouldsolve thespeed up the orderingproblemwith the TOP (1),butyoucould alsowould have add an "ORDER BY sdlh1.ID DESC" to it tomakeensure it always works properly...Fixed that for you 😉 😀
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 47,176 through 47,190 (of 66,549 total)
You must be logged in to reply to this topic. Login to reply