views:

255

answers:

3

I have a table that stores user information. The table has a userid (identity) column in it. The table data is queried by a view which is referenced by a lot of sprocs, but we reference something called auid which is created by the UDF below. The UDF is called in the view and sprocs then join or query from the view based on auid. It appears that this routine is being called A LOT (thousands of times) and is causing some unnecessary load on our SQL Server. Is there a better way to take a user_id = 255 and turn it into a varchar = 000000255 (9 characters long)?

UDF syntax:

ALTER FUNCTION [dbo].[udf_AUID] (@user_id int)  
RETURNS char(9) 
with schemabinding
AS  
BEGIN 
DECLARE @user_id_string varchar(9)
DECLARE @rval char(9)

SELECT @user_id_string=CAST(@user_id as varchar(9))
SELECT @rval=LEFT('000000000',9-len(@user_id_string))+@user_id_string

RETURN @rval
END

Basic syntax from the view is:

ALTER VIEW [dbo].[v_users]
AS
SELECT     
dbo.udf_AUID(dbo.users.user_id) AS auid, 
user_id, 
dbo.users.username 
FROM dbo.users

Example call in a sproc looks like this:

DECLARE @auid CHAR(9)
SET @auid = '000002444'  --normally passed in, but set here for example
SELECT dealer_range FROM users WHERE auid = @auid

DECLARE @cat_access TINYINT, @mit_access TINYINT
SELECT @cat_access = chan_access & 1, @mit_access = chan_access & 2 
    FROM appian.dbo.v_users
WHERE auid = @auid

Thanks in advance!

+2  A: 

Any reason you can't just store the userid that way to begin with?

Gandalf
Agreed, unless the AUID could change, there is no reason to recompute it with each query.
Goblyn27
-1: This should be a comment, not an answer.
Eric
Hmm, seemed kind of in between to me. I'm still learning.
Gandalf
No worries, I like to leave a comment if I downvote, so I just thought I'd let you know why. Of course, I don't seem to be the popular opinion :)
Eric
Sometimes the best answer is the simplest one. I'm not the developer on the project, just the DBA being blamed for poor application performance. I honestly don't know why we don't/can't store the auid at the time of a new user setup. Seems quite reasonable to me. I will check with the dev team. Thanks!
dtaylo04
A: 
RIGHT('000000000' + CAST(@user_id as varchar(9)), 9)

This way, you aren't doing multiple casts, and you don't have a LEN to worry about.

Eric
+2  A: 

You can use two functions that will help you out: RIGHT and REPLICATE

SELECT RIGHT(REPLICATE('0', 9) + '123456789', 9)    -- Result: 123456789
SELECT RIGHT(REPLICATE('0', 9) + '255', 9)          -- Result: 000000255
SELECT RIGHT(REPLICATE('0', 9) + '12', 9)           -- Result: 000000012
SELECT RIGHT(REPLICATE('0', 9) + '1', 9)            -- Result: 000000001
Jon Erickson