Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Computed Column is slowing down performance on a simple select statement Expand / Collapse
Author
Message
Posted Wednesday, April 4, 2012 7:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 8, 2012 3:54 PM
Points: 10, 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.
Post #1278505
Posted Wednesday, April 4, 2012 7:21 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 23,293, Visits: 32,028
Please post the code for the procedure and the two functions.



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)
Post #1278509
Posted Thursday, April 5, 2012 7:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:50 AM
Points: 11,264, Visits: 13,022
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

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
Post #1278802
Posted Thursday, April 5, 2012 10:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 8, 2012 3:54 PM
Points: 10, 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+.
Post #1278985
Posted Thursday, April 5, 2012 10:18 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 23,293, Visits: 32,028
Any chance of getting the information we requested?



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)
Post #1278990
Posted Thursday, April 5, 2012 10:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 8, 2012 3:54 PM
Points: 10, 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.
Post #1278994
Posted Thursday, April 5, 2012 11:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 8, 2012 3:54 PM
Points: 10, 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



Post #1279034
Posted Thursday, April 5, 2012 11:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 8, 2012 3:54 PM
Points: 10, 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



Post #1279040
Posted Thursday, April 5, 2012 11:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 8, 2012 3:54 PM
Points: 10, 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

Post #1279042
Posted Thursday, April 5, 2012 11:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 8, 2012 3:54 PM
Points: 10, Visits: 11
I have now posted the stored procedure and the two functions.
Post #1279044
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse