[thelist] math help...

[Ken Schaefer]

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 !