April 22, 2015 at 8:13 pm
Jeff Moden (4/22/2015)
Hmmm... depending on what it is, joint article?
Possibly... has to do with random numbers.
It is one of those things that doesn't play well in email because I suspect it will require a lot of back and forth speculating on the best way to do it.
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
April 22, 2015 at 8:43 pm
Jeff Moden (4/22/2015)
Matt Miller (#4) (4/22/2015)
Jeff Moden (4/22/2015)
grant-665832 (4/22/2015)
I'd do this in a C# CLR. Or in a simple program to generate the sequence and write it to DB table.The code would be straightforward.
Why? In this case, the equally straight-forward T-SQL would likely be as fast or faster and then you also wouldn't have to worry about more managed code not to mention the fact that you'd still end up with a 55-69GB table.
Agreed - At very least you'd have to stream it back unless you want to put a serious kink in your server's memory when that sucker returns in one big 60GB memory object.
if you're going to persist, set-based would still be fastest.
Will be curious to see what you come back with - I can get past the "server going catatonic" by chunking this up, but not sure I'm making any major gains on the overall perf (still coming in around 2 hours to store all 3.3 Billion sequences).
Thanks, Matt. At an hour and 41 minutes, I didn't do much better. The difference might be as subtle as me using a SELECT/INTO to create the table on the fly. I don't want to post my experimental code that produced all 3.3 billion rows because I truly fear that some neophyte might try it on a company server and lose his or her job or at least suffer a serious trip to the woodshed. 😉
I can send it to you on a PM if you're really interested but I can't imagine it being much different than anything you might have come up with.
{Edit}... and it should be pretty obvious that I didn't use a scalar function in my experiments to do it in that time. :hehe:
No - I think I got the gist:)
I do like the encoding and decoding trick, which would come in handy if you didn't have to persist all 3B in a single session. As long as you knew where you were in terms of generation - it would be kid's play to add in another 500K "sequences if you started to run low.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 23, 2015 at 8:00 am
Jeff Moden (4/22/2015)
DEK46656 has the right idea and he also has the right idea of splitting this thing into obvious paired parts but most people don't understand even the straight forward method never mind the paired optimizations that he (apologies for the generic term but don't know the gender) used. With that thought in mind, we're going to use the straight forward method that I'm pretty sure you'll have no problem grasping.
He is the proper pronoun.
Looking over your code I see the difference in our approach: I typically think “lookup” versus compute.
Nicely done.
Beer's Law: Absolutum obsoletum
"if it works it's out-of-date"
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply