'N' number of usage - Tally Table

Question

'N' number of usage - Tally Table

Viewing 15 posts - 16 through 30 (of 66 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Jeff Moden (10/7/2008)
jcrawf02 (10/7/2008)
Or are we waiting for Porkslinger 😛 to give us another followup article?
Heh... what an idea... maybe I'll title it "Porkslinger: Have Chops, Will Travel." 😛

Okay, I have to ask. I read this about you and pork chops, and I just gotta know, how did this start??

😎

Jeff Moden SSC Guru Points: 1004360 More actions · Answer 2

J (10/7/2008)
Another use.
Generating Universal Product Code numbers.
UPC-12 is PPPPPP-NNNNN-C (hyphens added for clarity)
P: 6 digit company prefix assigned by EAN Council
N: 5 digit sequential number
C: check digit, based on modulo-10 calculation.
The tally table allows inserting 100,000 - 1 numbers (00000 to 99999) in the UPC_Numbers table in a single INSERT statement, rather than the RBAR way of looping one insert at a time and incrementing aocunter each time.
Performance gain is HUGE. Generating two UPC sets using RBAR took 38 minutes. This went down to 45 seconds using Tally table.
Or any other numbering sequence based on consecutive numbers.
RBAR inserts are poison in large quantities.

I know I'm not allowed to blow out my own candles at this party, BUT... you reminded me of what else I've used the Tally table for... Luhn 10 Checksums for credit card numbers and checksums for bank account numbers.

Your implementation for the generation of UPC codes sounds very interesting. You should either submit an article yourself, or, if you want, post it here and I'll make sure it gets into an article with the appropriate credit.

J, I know you told me before but my "Weekend Alzheimers" kicked in a couple of times... what's your first name? Last name would be good, too, if I ever get around to publishing another article on the Tally table.

--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: 1004360 More actions · Answer 3

Lynn Pettis (10/7/2008)
Jeff Moden (10/7/2008)
jcrawf02 (10/7/2008)
Or are we waiting for Porkslinger 😛 to give us another followup article?
Heh... what an idea... maybe I'll title it "Porkslinger: Have Chops, Will Travel." 😛
Okay, I have to ask. I read this about you and pork chops, and I just gotta know, how did this start??
😎

Heh... some guy had a problem with one of his renegade developers and he asked what he should do about it. Having dealt with a pod wad of really moronic posts just minutes before, I was in a bit of a fiesty mood. I suggested that he "take him out for a nice pork chop dinner... tie him to the chair and then feed him pork chops... at point blank range with a slingshot!" 🙂 It's become a bit of a trademark reply in some cases, as well... :w00t: Helps keep me from BBQ'ing some of the posters with a flame response when someone is being an absolute nub about things... I'll say something like...

"So... You like pork chops by any chance?"

Then, I go out and chop a cord of someone else's wood to get the PO out of my system. Neighbors are starting to get ticked about me cutting down their trees, though. :hehe:

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

Jeff Moden (10/7/2008)
Heh... some guy had a problem with one of his renegade developers and he asked what he should do about it. Having dealt with a pod wad of really moronic posts just minutes before, I was in a bit of a fiesty mood. I suggested that he "take him out for a nice pork chop dinner... tie him to the chair and then feed him pork chops... at point blank range with a slingshot!" 🙂 It's become a bit of a trademark reply in some cases, as well... :w00t: Helps keep me from BBQ'ing some of the posters with a flame response when someone is being an absolute nub about things... I'll say something like...
"So... You like pork chops by any chance?"

Heh. As it happens, I just finished a plate full of pork chops. No lie!

🙂

[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: 1004360 More actions · Answer 5

BWAA-HAA! Did you leave any redmarks on your cheeks from the slingshot kickback? :hehe:

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

Jeff Moden (10/7/2008)
BWAA-HAA! Did you leave any redmarks on your cheeks from the slingshot kickback? :hehe:

Is that the tough chewy part?

[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]

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

rbarryyoung (10/7/2008)
Jeff Moden (10/7/2008)
BWAA-HAA! Did you leave any redmarks on your cheeks from the slingshot kickback? :hehe:
Is that the tough chewy part?

I wonder if they're serving up Porkchops at CodeCamp... What do you think Barry? :hehe:

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

I hope so! 😀

[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: 1004360 More actions · Answer 9

rbarryyoung (10/7/2008)
Jeff Moden (10/7/2008)
BWAA-HAA! Did you leave any redmarks on your cheeks from the slingshot kickback? :hehe:
Is that the tough chewy part?

OMG! I'm clutching my sides with tears in my eyes I'm laughing so bloody hard! WHOOOWEEE! I'll never be able to eat pork chops again because I'll have this vision of Barry getting the slingshot stuck and I'll probably laugh so hard that I'll blow a pork chop bone right out my nose! 😛

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

Jeff Moden (10/7/2008)
I'll never be able to eat pork chops again because I'll have this vision of Barry getting the slingshot stuck ...

I wish I could say that's never happened, but I'd be lying...

😛

[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]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

rbarryyoung (10/7/2008)
Jeff Moden (10/7/2008)
Heh... some guy had a problem with one of his renegade developers and he asked what he should do about it. Having dealt with a pod wad of really moronic posts just minutes before, I was in a bit of a fiesty mood. I suggested that he "take him out for a nice pork chop dinner... tie him to the chair and then feed him pork chops... at point blank range with a slingshot!" 🙂 It's become a bit of a trademark reply in some cases, as well... :w00t: Helps keep me from BBQ'ing some of the posters with a flame response when someone is being an absolute nub about things... I'll say something like...
"So... You like pork chops by any chance?"
Heh. As it happens, I just finished a plate full of pork chops. No lie!
🙂

And guess what I had for dinner tonight when I got home from officiating varsity high school soccer game? Yep, pork chops!

😎

Jeff Moden SSC Guru Points: 1004360 More actions · Answer 12

See? Pork chops RULE!

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

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 13

An article on this topic would be really cool, Jeff.

I just presented this concept at work using your split csv example.

I was just trying to think what other examples I could use for future presentations.

One question that came up, though. If your string is longer than the tally table the split doesn't work.

It would seem that this is a risk factor in using the tally table which you don't have when doing the split with a while loop.

How would you answer that question?

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 14

ggraber (10/8/2008)
An article on this topic would be really cool, Jeff.
I just presented this concept at work using your split csv example.
I was just trying to think what other examples I could use for future presentations.
One question that came up, though. If your string is longer than the tally table the split doesn't work.
It would seem that this is a risk factor in using the tally table which you don't have when doing the split with a while loop.
How would you answer that question?

IANAP (I Am Not A Porkslinger) but you have to put a limit on both your While and the Tally, either way, you just do it in a different manner. What would you do if your upper limit on the While didn't generate enough iterations to complete the action? (A: make it bigger)

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 15

I guess the question is that a while loop has a dynamic number of iterations based on the length of the parameter.

You don't have that kind of flexibility with a tally table. If the tally table you have isn't big enough, you will be missing some elements at the end of the csv.