The Set-Based Limit

Question

The Set-Based Limit

Viewing 15 posts - 31 through 45 (of 74 total)

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Kit Brandner (12/22/2008)
Yeah, it uses the RAND() function. Nothing too special, but I found set-based methods would hang when trying to generate 1000+ records. Perhaps my conclusion was a bit short-sighted, but after spending A LOT of time with various set-based procedures, I wasn't able to get anything that even came close to the procedural alternative. The machine that this database is on is only running S2K; possibly if it were moved to another server with 2K5 on it there would be other options available to me (like SQL CLR), but since the application that uses it is in full-swing such a migration is unfortunately not of priority.

See above...

--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)

Steve Jones - SSC Editor SSC Guru Points: 736198 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736198

December 22, 2008 at 1:33 pm

#916368

I knew Jeff would have something!

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

timothyawiseman (12/21/2008)
Two things of note though are that it is sometimes faster to code the procedural solution than it is to code the set based one, largely due to habit. It is sometimes the case that that savings in programmer time is far more valuable than the processing speed given up, especially if it is a script being written for one time use.

I have seen this same argument made for SQLCLR as well. Faster is always funnier.......

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

Jeff Moden (12/22/2008)
Kit Brandner (12/22/2008)
Consider the one exception I've encountered - generating a variable number of PIN codes according to a proprietary algorithm. There are easily several set-based approaches, but they run exponentially slower that the procedural solution implemented, especially as the number of records increases.
Dang... to bad it's proprietary... I'd like to take a crack at that.

Every example out there is proprietary and the code can't be shared. Anyone else notice that? I would venture to say that a good consultant would come in and show you how to optimize the set based approach and beat the procedural. To bad it is proprietary.

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

Jonathan Kehayias (12/22/2008)
Every example out there is proprietary and the code can't be shared. Anyone else notice that? I would venture to say that a good consultant would come in and show you how to optimize the set based approach and beat the procedural. To bad it is proprietary.

The thing that cracks me up is why would anyone waste their time on something like an "Algorithm" for a 4 digit pin? I mean, what could you possibly do to it? Make sure there was at least 1 even and 1 odd number? Make sure that two adjacent digits weren't the same? That's just limiting the number of combinations available and seem to be completely useless since the op is likely to change it to something they can remember sooner than later.

--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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Steve Jones - Editor (12/22/2008)
I knew Jeff would have something!

Heh... Thanks, Steve. I'm just surprised because it's such an old trick and it's posted everywhere. You would think that everyone would know this one.

--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)

Kit Brandner SSC Enthusiast Points: 147 More actions · Answer 7

Try a 23-character long alphanumeric PIN code with certain characters excluded for legibility that can't be repeated in the database. So Jeff's solution wouldn't work. I suppose I should have just put that in the original message, but proprietary sounded better.

One of the proposed set-based methods I was trying was having a table that contained all the valid characters, and a tally table. But this method didn't render desirable execution times, and often would hang in the case of generating large sets of PIN codes. I also tried working with CROSS JOINs, but that didn't pan out much further. Any other ideas?

Consider this: the term "PIN code" doesn't necessarily apply to the bank machine, just like the term "algorithm" is a fancy word for a set of rules. And banks often allow for a PIN code longer than 4 digits.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Kit Brandner (12/22/2008)
Try a 23-character long alphanumeric PIN code with certain characters excluded for legibility that can't be repeated in the database. So Jeff's solution wouldn't work. I suppose I should have just put that in the original message, but proprietary sounded better.
One of the proposed set-based methods I was trying was having a table that contained all the valid characters, and a tally table. But this method didn't render desirable execution times, and often would hang in the case of generating large sets of PIN codes. I also tried working with CROSS JOINs, but that didn't pan out much further. Any other ideas?
Consider this: the term "PIN code" doesn't necessarily apply to the bank machine, just like the term "algorithm" is a fancy word for a set of rules. And banks often allow for a PIN code longer than 4 digits.

Heh... no problem... I'll be right back... by the way... what are the "desirable execution times" and do you have any other rules that you'd like to apply?

Also, you said "that can't be repeated in the database" meaning what... that the password must be unique? How many passwords ya got in the database?

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Kit Brandner (12/22/2008)
Try a 23-character long alphanumeric PIN code with certain characters excluded for legibility that can't be repeated in the database. So Jeff's solution wouldn't work. I suppose I should have just put that in the original message, but proprietary sounded better.
One of the proposed set-based methods I was trying was having a table that contained all the valid characters, and a tally table. But this method didn't render desirable execution times, and often would hang in the case of generating large sets of PIN codes. I also tried working with CROSS JOINs, but that didn't pan out much further. Any other ideas?

Yeah, give us the specs and let us go at it!

Heh, I feel a contest coming on... :w00t:

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

Also, if the code really isn't "proprietary", post your procedureal code and let us have a go at it, eh?

--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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

I can't think of a better xmas gift to give for Jeff...

Let's go... post the specs and the code and see what we come up with.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

Jeff Moden (12/22/2008)
Kit Brandner (12/22/2008)
Try a 23-character long alphanumeric PIN code with certain characters excluded for legibility that can't be repeated in the database. So Jeff's solution wouldn't work. I suppose I should have just put that in the original message, but proprietary sounded better.
One of the proposed set-based methods I was trying was having a table that contained all the valid characters, and a tally table. But this method didn't render desirable execution times, and often would hang in the case of generating large sets of PIN codes. I also tried working with CROSS JOINs, but that didn't pan out much further. Any other ideas?
Consider this: the term "PIN code" doesn't necessarily apply to the bank machine, just like the term "algorithm" is a fancy word for a set of rules. And banks often allow for a PIN code longer than 4 digits.
Heh... no problem... I'll be right back... by the way... what are the "desirable execution times" and do you have any other rules that you'd like to apply?
Also, you said "that can't be repeated in the database" meaning what... that the password must be unique? How many passwords ya got in the database?

Just like waving the flag in front of the proverbial bull...:)

----------------------------------------------------------------------------------
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?

Steve Jones - SSC Editor SSC Guru Points: 736198 More actions · Answer 13

Indeed, my guess is Jeff if handcrafting something right now in the winter snows of MI.

peter-757102 SSCertifiable Points: 6877 More actions · Answer 14

Jeff is quiet about my posts - too quiet....is a storm coming?

LOL

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 15

The fact of the matter is that once you can build the keyusing a set solution, you can also validate it using the same solution (might make for a darn long query... but that would work).

The only problem I see is to be able to create a dynamic amount of keys without looping and without sacrificing performance (how do you handle making 10 keys and 10 000 000 keys from the same query with same linear performance). I have a couple theories, but no time to test... maybe next year.