[thelist] math help...
Ken Schaefer
Ken at adOpenStatic.com
Sat Mar 18 05:51:17 CST 2006
Can I strongly suggest the other method which I mentioned before. Your system
doesn't scale - the more codes you generate, the greater the chance of
regenerating an existing code, and the more time your sproc spends in its
loop. Additionally, you need to use a sufficiently high isolation level when
running this sproc so that some other thead running the same sproc doesn't
generate the same code.
Create a table of all possible values. Use another field to indicate whether
the code has been used or not.
The following query:
SELECT
TOP 1 Code
FROM
MasterTableOfCodes
WHERE
CodeUsed = 0
ORDER BY
NewID()
Will select just a single, random record from that table. There is no need
for loops. You will want to use at least a "repeatable read" isolation level
for this type of transaction (the default is read committed, but I don't
think that's sufficient for what you want to achieve).
Cheers
Ken
: -----Original Message-----
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Brian Cummiskey
: Sent: Saturday, 18 March 2006 5:11 AM
: To: thelist at lists.evolt.org
: Subject: Re: [thelist] math help...
:
: Hi Ken,
:
: Thanks for the response.
:
:
: Ken Schaefer wrote:
: > What sort of transaction isolation are you using? In a multi-threaded
: > environment, what happens when you have two threads with the same value
: that
: > both pass the first part of the test, but then baulk at the second?
:
: It's using a modulus process. I didn't write this process, and it is in
: use on a lot of our jobs here at the office. This isn't running on a
: transaction. I think the best way would be to post the whole SP. See
: below.
:
: > Secondly, do you really think this is a good idea? I don't. It really
: won't
: > scale as you get close to the maximum number of possible values as your
: > generator routine will mostly generate already used values.
:
: I don't think this will be a problem. I doubt we will ever reach the
: number that was posted yesterday. If it is, I can simply add another
: pre-fix character to being with, such as BST2.
:
: > But do you really need random IDs? It seems you just need IDs that
: haven't
: > been used before.
:
: They need to be random, as they are given to sales reps as their
: commission ID. If they learn that its just the next number in order,
: there could be some cheating.
:
: Could I suggest you use GUIDs instead? You can either set a
: > field to a GUID type, or set it to varchar/char and use NewID() to
: generate
: > the value and insert it.
:
: I just read this: http://www.webopedia.com/TERM/G/GUID.html quickly, and
: i think it's overkill.
: Our phone agents read back these numbers to the sales rep's. So, it
: needs to be tiny, normalized (the BST prefix), and easy to write down
: over the phone.
:
:
:
:
:
:
: SET QUOTED_IDENTIFIER ON
: GO
: SET ANSI_NULLS OFF
: GO
:
: ALTER PROCEDURE get_Confirmation_Code_TABLE
: @confirmation_code varchar(8) output,
: @rowid int output
: AS
: declare @counter int,
: @sum int,
: @charNum int
: repeat:
: SET @counter = 1
: SET @sum=0
: SET @confirmation_code='BST'
: while @counter<6
: begin
: SET @charNum=convert(int,(RAND()*24))
: IF @charNum<10 set
: @confirmation_code=@confirmation_code+CHAR(@charNum+48)
: IF @charNum=10 set
: @confirmation_code=@confirmation_code+'A'
: IF @charNum=11 set
: @confirmation_code=@confirmation_code+'C'
: IF @charNum=12 set
: @confirmation_code=@confirmation_code+'E'
: IF @charNum=13 set
: @confirmation_code=@confirmation_code+'F'
: IF @charNum=14 set
: @confirmation_code=@confirmation_code+'G'
: IF @charNum=15 set
: @confirmation_code=@confirmation_code+'H'
: IF @charNum=16 set
: @confirmation_code=@confirmation_code+'J'
: IF @charNum=17 set
: @confirmation_code=@confirmation_code+'K'
: IF @charNum=18 set
: @confirmation_code=@confirmation_code+'M'
: IF @charNum=19 set
: @confirmation_code=@confirmation_code+'R'
: IF @charNum=20 set
: @confirmation_code=@confirmation_code+'T'
: IF @charNum=21 set
: @confirmation_code=@confirmation_code+'U'
: IF @charNum=22 set
: @confirmation_code=@confirmation_code+'W'
: IF @charNum=23 set
: @confirmation_code=@confirmation_code+'X'
: IF @charNum=24 set
: @confirmation_code=@confirmation_code+'Y'
: set @sum=@sum+ at charNum
: set @COUNTER=@COUNTER+1
: end
: -----wape off some bad word
: IF @confirmation_code LIKE '%BADWORDS%' goto repeat
:
: -----confirm modulus of 25
: if ((@sum/25.0)<>floor(@sum/25)) goto repeat
:
: if exists(select confirmation_code from TABLE where
: confirmation_code=@confirmation_code )
: ---the confirmation code has been used
: begin
: goto repeat
: end
: else
: ---get the cconfirmation code successfully
: begin
: insert TABLE (confirmation_code) values
: (@confirmation_code)
: end
: if @@error=0
: begin
: set @rowid = @@identity
: end
:
: GO
: SET QUOTED_IDENTIFIER OFF
: GO
: SET ANSI_NULLS ON
: GO
:
: --
:
: * * Please support the community that supports you. * *
: http://evolt.org/help_support_evolt/
:
: For unsubscribe and other options, including the Tip Harvester
: and archives of thelist go to: http://lists.evolt.org
: Workers of the Web, evolt !
More information about the thelist
mailing list