SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Computed Column is slowing down performance on a simple select statement


Computed Column is slowing down performance on a simple select statement

Author
Message
crackedcornjimmy
crackedcornjimmy
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 11
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.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90011 Visits: 38942
Please post the code for the procedure and the two functions.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42097 Visits: 14925
Can you also post the execution plan(s)?

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



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
crackedcornjimmy
crackedcornjimmy
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 11
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+.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90011 Visits: 38942
Any chance of getting the information we requested?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
crackedcornjimmy
crackedcornjimmy
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 11
It's on its way. I'm sorry for the delay but I'm setting up another test or two in the interim.
crackedcornjimmy
crackedcornjimmy
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 11
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




crackedcornjimmy
crackedcornjimmy
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 11
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




crackedcornjimmy
crackedcornjimmy
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 11
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


crackedcornjimmy
crackedcornjimmy
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 11
I have now posted the stored procedure and the two functions.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search