Stored Procedure timing

Question

Post reply

Stored Procedure timing

Allen B

SSC-Addicted

Points: 473
More actions
April 27, 2007 at 4:02 pm

#91102

I have a SP that gets me the next customer number for a web app.

I store a number out in a table, when the SP is called it gets the number in the table, increments, updates the table with the new number, then returns the number to the user. I only generate about 20 numbers per day so not a big load at this point.

My question/concern is returning duplicate numbers. I’ve looked thru BOL but couldn’t find the answer. When multiple connections are accessing that SP, is it asynchronous or synchronous? Does one connection have to wait until the SP executes before it gets it, or does another instance of the SP execute. In my current situation with the low load it would be hard to create duplicate numbers but in a very heavy load environment it looks like it could be easy if its asynchronous. Is there any way to make it synchronous?

Thanks
Allen
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

First question would be... Why aren't you using an IDENTITY column to do this along with Scope_Identity.

Next question would be... Please post your SP...

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

Allen B SSC-Addicted Points: 473 More actions · Answer 2

Here most of the sp. I left a little bit out, I'm creating two different cust no's, one has to start with a M, they both have different starting places for the numbering sequence, reason not using an identity column. There will only be two rows in the table ever. Its an internal app.

I was mostly just curious about the synchronous part.

DECLARE @lastNumber AS CHAR(6),

@numberToIncrement AS INT,

@newNumber AS VARCHAR(6),

@len_newNumber AS INT

SELECT @lastNumber = last_cust_no

FROM mm_CompanyNo

WHERE company = @strCompany

SELECT @numberToIncrement = CAST( (RIGHT(@lastNumber, 5)) AS INT)

SELECT @numberToIncrement = @numberToIncrement + 1

SELECT @len_newNumber = LEN(CAST((@numberToIncrement) AS VARCHAR))

IF @len_newNumber = 4

SELECT @newNumber = '0' + CAST(@numberToIncrement AS VARCHAR(5))

ELSE

IF @len_newNumber = 3

SELECT @newNumber = '00' + CAST(@numberToIncrement AS VARCHAR(5))

ELSE

IF @len_newNumber = 2

SELECT @newNumber = '000' + CAST(@numberToIncrement AS VARCHAR(5))

ELSE

IF @len_newNumber = 1

SELECT @newNumber = '0000' + CAST(@numberToIncrement AS VARCHAR(5))

ELSE

SELECT @newNumber = CAST(@numberToIncrement AS VARCHAR(5))

IF @strCompany = 'ABC'

SELECT @newNumber = '0' + @newNumber

ELSE

SELECT @newNumber = 'M' + @newNumber

UPDATE mm_CompanyNo

SET last_cust_no = @newNumber

WHERE company = @strCompany

SELECT @returnNumber = @newNumber

Thanks

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

Try this and see if you can use it. It should take care of when the procedure is used heavily, but I'll leave that to someone else to prove or disprove.

-- create table mm_CompanyNo (last_cust_no varchar(6), company varchar(50))

-- insert into mm_CompanyNo values ('000001', 'ABC')

-- insert into mm_CompanyNo values ('M00100', 'DEF')

declare @strCompany varchar(50),

@returnNumber varchar(6)

set @strCompany = 'ABC'

DECLARE @lastNumber AS CHAR(6),

@numberToIncrement AS INT,

@newNumber AS VARCHAR(6),

@len_newNumber AS INT

update mm_CompanyNo set

@returnNumber = last_cust_no = (select substring(last_cust_no, 1, 1) +

replicate(0, 5 - len(cast(substring(last_cust_no, 2, 5) as int) + 1)) +

cast(cast(substring(last_cust_no, 2, 5) as int) + 1 as varchar(5)) from mm_CompanyNo where company = @strCompany)

where

company = @strCompany

SELECT @returnNumber = @newNumber

select @returnNumber

-- drop table mm_CompanyNo

-- select * from mm_CompanyNo

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Lynn's should work, however this may be a little more simplified

DECLARE @strCompany varchar(50),

@returnNumber varchar(6)

UPDATE

dbo.mm_CompanyNo

SET

@returnNumber = last_cust_no = (CASE WHEN @strCompany = 'ABC' THEN '0' ELSE 'M' END) + RIGHT('00000' + CAST(CAST((RIGHT(last_cust_no, 5)) AS INT) + 1 AS varchar(6)),5)

WHERE

@strCompany

SELECT @returnNumber

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

Allen,

First, there is no reason, whatsoever, to store the "M" in the sequence table. All it does is slow everything down with unneccessary character conversions.

Using the good example that Mr. Pettis started, with some mods... the blue stuff is the actual proc I think you need... guaranteed not to return dupes... guaranteed not to deadlock...

--==============================================================================================

-- Setup for the test

--==============================================================================================

--===== Create sequence table similar to Allen's

CREATE TABLE dbo.mm_CompanyNo (Last_Cust_No INT, Company VARCHAR(50))

--===== Seed the table with starting values for each company

-- This will be the "0" company in this example

INSERT INTO mm_CompanyNo VALUES ( 0,'ABC') --First new id will be "000001"

-- This will be the "M" company in this example

INSERT INTO mm_CompanyNo VALUES (100,'DEF') --First new id will be "M00101"

GO

--===== Create a procedure to get a new customer number depending on the company

CREATE PROCEDURE dbo.GetNextCustNo (@Company VARCHAR(50), @NewCustNo VARCHAR(6) OUTPUT)

AS

DECLARE @NextID INT

SET NOCOUNT ON

UPDATE dbo.mm_CompanyNo

SET @NextID = Last_Cust_No = Last_Cust_No+1

WHERE Company = @Company

SET @NewCustNo = CASE @Company

WHEN 'DEF'

THEN 'M'+REPLACE(STR(@NextID,5),' ','0')

ELSE REPLACE(STR(@NextID,6),' ','0')

END

GO

--==============================================================================================

-- Demo how to use this bugger

--==============================================================================================

--===== Declare local variables for the test

DECLARE @Company VARCHAR(50)

DECLARE @NewCustNo VARCHAR(6)

SET @Company = 'ABC'