Computed Column is slowing down performance on a simple select statement

  • Background:

    Previously, my company was using a User Defined Function to html encode some data in a where clause of a stored procedure. Example below:

    DECLARE @LName --HTML encoded last name as input parameter from user

    SELECT * FROM

    (SELECT LName

    FROM SomeView xtra

    WHERE (( @LName <> '' AND dbo.EncodingFunction(dbo.DecodingFunction(xtra.LName))=@LName) OR @Lname=''))

    I simplified this for clarity sake.

    The problem is, when the stored procedure with this query was called 45 times in quick succession, the average performance on a table with 62,000 records was about 85 seconds. When I removed the UDF, the performance improved to just over 1 second to run the sproc 45 times.

    So, we consulted and decided on a solution that included a computed column in the table accessed by the view, SomeView. The computed column was written into the table definition like this:

    [LNameComputedColumn] AS (dbo.EncodingFunction(dbo.DecodingFunction([LName])))

    I then ran a process that updated the table and automatically populated that computed column for all 62,000 records. Then I changed the stored procedure query to the following:

    DECLARE @LName --HTML encoded last name as input parameter from user

    SELECT * FROM

    (SELECT LNameComputedColumn

    FROM SomeView xtra

    WHERE (( @LName <> '' AND xtra.LNameComputedColumn=@LName) OR @Lname='')

    When I ran that stored procedure, the average run time for 45 executions increased to about 90 seconds. My change actually made the problem worse!

    What am I doing wrong? Is there a way to improve the performance?

    As a side note, we are currently using SQL Server 2000 and are planning to upgrade to 2008 R2 very soon, but all code must work in SQL Server 2000.

  • Please post the code for the procedure and the two functions.

  • Can you also post the execution plan(s)?

    You should also make the computed column PERSISTED and put an index on it.

  • I mentioned in the question that I am using SQL 2000. Unfortunately, PERSISTED is not available in SQL 2000. It wasn't introduced until 2005+.

  • Any chance of getting the information we requested?

  • It's on its way. I'm sorry for the delay but I'm setting up another test or two in the interim.

  • Here is the stored procedure that validates the student credentials. I will be posting the UDF's shortly, once I clean them up to look nice.

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    CREATE PROC spa_SomeSproc

    @ListID INT, --List ID where the student is located

    @ItemID INT, --item that belongs to the list

    @LName VARCHAR(255), --last name of student

    @StudentID VARCHAR(75), --student id

    @StudentID2 VARCHAR(75),

    @SiteID VARCHAR(20)

    AS

    SET NOCOUNT ON

    DECLARE @cnt INT, @zeroTrimExtraID INT, @zeroTrimPIN INT

    create table #Students(

    contactsExtraID INT,

    FirstName VARCHAR(255),

    LastName VARCHAR(255),

    StudentBalance DECIMAL(19,4),

    DateAdjusted datetime,

    StudentID VARCHAR(75),

    StudentID2 VARCHAR(75),

    BuildingID VARCHAR(20),

    Meal1Balance DECIMAL(19,2),

    Meal2Balance DECIMAL(19,2),

    StudentGrade VARCHAR(20),

    PayStatus VARCHAR(20)

    )

    -------------------------------------------------------------------------------------------------

    --if the studentid or the studentid2 needs trailing zero processing, do it

    -------------------------------------------------------------------------------------------------

    SELECT

    @zeroTrimStudentID = b.zeroTrimStudentID,

    @zeroTrimStudentID2 = b.zeroTrimStudentID2

    FROM

    tblInventory a,

    tblExportSystem b

    WHERE

    a.ExportSystemID = b.id

    AND a.itemid = @itemid

    IF ISNUMERIC(@StudentID) = 0

    SET @zeroTrimStudentID = 0

    IF ISNUMERIC(@StudentID2) = 0

    SET @zeroTrimStudentID2 = 0

    IF @StudentID <> '' AND @zeroTrimStudentID=1

    BEGIN

    SET @StudentID = REPLACE(@StudentID, '0', ' ')

    SET @StudentID = LTRIM(@StudentID)

    SET @StudentID = REPLACE(@StudentID, ' ', '0')

    END

    IF @StudentID2 <> '' AND @zeroTrimStudentID2 = 1

    BEGIN

    SET @StudentID2 = REPLACE(@StudentID2, '0', ' ')

    SET @StudentID2 = LTRIM(@StudentID2)

    SET @StudentID2 = REPLACE(@StudentID2, ' ', '0')

    END

    -------------------------------------------------------------------------------------------------

    -- This following SQL assumes that if the validations weren't entered (therefore blank) then they

    -- weren't expected. Therefore, we need to do the checks with the validation bit values passed

    -------------------------------------------------------------------------------------------------

    INSERT INTO #Students

    SELECT * FROM

    (

    SELECT

    StudentInfoID,

    FirstName,

    sLNameEncoded,

    StudentBalance,

    DateAdjusted,

    StudentID =

    CASE WHEN @zeroTrimStudentID = 1 THEN

    CASE WHEN ISNUMERIC(StudentID) = 1 THEN CAST(CONVERT(BIGINT, StudentID) AS VARCHAR)

    ELSE StudentID

    END

    ELSE StudentID

    END,

    StudentID2 =

    CASE WHEN @zeroTrimPIN=1 THEN

    CASE WHEN isnumeric(StudentID2)=1 THEN CAST(convert(bigint, StudentID2) AS VARCHAR)

    ELSE StudentID2

    END

    ELSE StudentID2

    END,

    BuildingID,

    Meal1Balance,

    Meal2Balance,

    StudentGrade,

    PayStatus

    FROM

    qStudentIDContacts xtra --this is the view which accesses the table that has the computed column

    WHERE

    xtra.ListID = @ListID

    AND (( @LastName <> '' AND xtra.sLNameEncoded=@LastName) OR @LastName='')

    AND (( @BuildingID <> '' AND xtra.BuildingID=@BuildingID) OR @BuildingID='')

    ) final

    WHERE

    (( @StudentID <> '' AND dbo.fnHTMLEncode(dbo.fnHTMLDecode(final.StudentID))=@StudentID) OR @StudentID='')

    AND (( @StudentID2 <> '' AND dbo.fnHTMLEncode(dbo.fnHTMLDecode(final.StudentID2))=@StudentID2) OR @StudentID2='')

    SET @cnt = @@rowcount

    -------------------------------------------------------------------------------------------------

    -- Check to see if the sample information was used if no rows were returned.

    -------------------------------------------------------------------------------------------------

    IF @cnt = 0

    IF (

    (@LastName='TestLastName' OR @LastName='')

    AND (@StudentID='TestStudentID' OR @StudentID='TestStudentID+' OR @StudentID='TestStudentID-' OR @StudentID='' )

    AND (@StudentID2='TestID2' OR @StudentID2='TestID2+' OR @StudentID2='TestID2-' OR @StudentID2='')

    )

    BEGIN

    INSERT INTO #Students

    SELECT

    StudentInfoID = -1,

    FirstName = 'Jimminy',

    LastName = 'Cricket',

    StudentBalance = ROUND(RAND() * 20000 / 100,2) *

    CASE WHEN @StudentID = 'TestStudentID-' OR @StudentID2 = 'TestID2-'

    THEN -1

    ELSE 1

    END, --generates random # between $0.00 and $20.00

    DateAdjusted = GETDATE(),

    StudentID = @StudentID,

    StudentID2 = @StudentID2,

    BuildingID = '',

    Meal1Balance = ROUND(RAND() * 20000 / 100,0)*

    CASE WHEN @StudentID = 'TestStudentID-' OR @StudentID2 = 'TestID2-'

    THEN -1

    ELSE 1

    END, --generates random # between 0 and 200

    Meal2Balance = ROUND(RAND() * 20000 / 100,0)*

    CASE WHEN @StudentID = 'TestStudentID-' OR @StudentID2 = 'TestID2-'

    THEN -1

    ELSE 1

    END, --generates random # between 0 and 200

    StudentGrade = '03',

    PayStatus = 'R'

    SET @cnt = 1

    END

    SELECT * FROM #Students

    -------------------------------------------------------------------------------------------------

    -- Checks: If the validation bit is set and nothing was passed, this zeros so the sum will not

    -- be 3. If the sum is 3 (everything was passed), the return the count of what was returned

    -- above. The application logic will reject anything that != 1.

    -------------------------------------------------------------------------------------------------

    SELECT

    CASE WHEN

    (CASE WHEN LastNameValidation=1 AND @LastName = '' THEN 0

    ELSE 1

    END

    +

    CASE WHEN StudentIDValidation=1 AND @StudentID = '' THEN 0

    ELSE 1

    END

    +

    CASE WHEN StudentID2Validation=1 AND @StudentID2 = '' THEN 0

    ELSE 1

    END) = 3

    THEN @cnt

    ELSE 0

    END validCount

    FROM

    tblInventory i

    WHERE

    itemid = @itemid

    SET NOCOUNT OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

  • Here is the fnHTMLDecode UDF:

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    CREATE FUNCTION dbo.fnHTMLDecode

    (

    @String varchar(8000)

    )

    RETURNS varchar(8000)

    BEGIN

    DECLARE @HTMLDecodedString varchar(8000)

    SELECT @HTMLDecodedString = ISNULL(@String, '')

    DECLARE @ix int, @iy int, @pos int

    SET @pos = 1 -- start at front of string

    SET @ix = CHARINDEX('&#', SUBSTRING(@HTMLDecodedString, @pos, LEN(@HTMLDecodedString))) -- search for first occurance of encoding delimiter '&#'

    WHILE @ix > 0 -- if one is found

    BEGIN

    SET @iy = CHARINDEX(';', SUBSTRING(@HTMLDecodedString, @pos + @ix + 1, LEN(@HTMLDecodedString))) -- find the encoding terminator ';'

    IF @iy IN (2,3,4) -- iy is one larger than the number to be decoded

    BEGIN

    -- replace all occurrences of '&#<nnn>;' with the char equivalent

    SET @HTMLDecodedString = REPLACE(@HTMLDecodedString, SUBSTRING(@HTMLDecodedString, @pos+@ix-1, @iy+2), CHAR(CAST(SUBSTRING(@HTMLDecodedString, @pos+@ix+1, @iy-1) as int)) )

    -- move position to one past first replacement

    SET @pos = @pos + @ix

    END

    ELSE

    BEGIN

    -- If the encoded number is too large, don't decode it; just leave it be and move position one past the front delimiter

    SET @pos = @pos + @ix + 1

    END

    -- search for next matching encoding delimiter

    set @ix = CHARINDEX('&#', SUBSTRING(@HTMLDecodedString, @pos, LEN(@HTMLDecodedString)))

    END

    RETURN @HTMLDecodedString

    END

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

  • Here is the fnHTMLEncode UDF:

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    CREATE FUNCTION [dbo].[fnHTMLEncode]

    (

    @String varchar(8000)

    )

    RETURNS varchar(8000)

    BEGIN

    SET @String = ISNULL(@String, '')

    DECLARE @HTMLEncodedString varchar(8000)

    SELECT @HTMLEncodedString = ''

    SELECT @HTMLEncodedString = @HTMLEncodedString +

    CASE

    WHEN theChar LIKE '[A-Za-z0-9,._ ]'

    THEN theChar

    ELSE '&#' + CAST(ASCII(theChar) AS varchar(3)) + ';'

    END

    FROM

    (

    SELECT theChar = SUBSTRING(@string, lNumber, 1)

    FROM tblNumbers

    WHERE lNumber <= LEN(@String) ) CharacterArray

    -- Return the result of the function

    RETURN @HTMLEncodedString

    END

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

  • I have now posted the stored procedure and the two functions.

  • crackedcornjimmy (4/5/2012)


    I mentioned in the question that I am using SQL 2000. Unfortunately, PERSISTED is not available in SQL 2000. It wasn't introduced until 2005+.

    Sorry I missed that you mentioned 2000 and were in a 2000 forum. I see so few posts for 2000 I always assume 2005+.

    I just noticed that you are decoding and then re-encoding the data in the column. Why are you doing both? If it is already Encoded you shouldn't need to do anything.

  • Yes. Let's say the data is already encoded, due to some recent application changes to how it is inserted into the database.

    Now, the value passed into the stored procedure is always encoded. But, we don't want to encode the encoded data again because it wouldn't match the encoded passed in parameter. Thus, we decode the assumed encoded data in the database (if it is already decoded (about 97% of our data is unencoded) decoding has no effect) giving it a true plain text foundation, then re-encode it so that it matches the encoded parameter passed in.

    Is that clear enough?

  • I thought that might be the reason, but it still is causing performance issues. Every row in the table has to go through both functions before any filtering can be applied.

    How is the HTML encoded data used? You might even be better off storing a second decoded column then decode the parameter passed in to compare against the decoded column. I think this would allow for better use of any indexes.

  • So, basically, that would cut my UDF calls in half. A good idea.

  • Here is my execution plan:

Viewing 15 posts - 1 through 15 (of 15 total)

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