April 4, 2012 at 7:11 pm
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.
April 4, 2012 at 7:21 pm
Please post the code for the procedure and the two functions.
April 5, 2012 at 7:42 am
Can you also post the execution plan(s)?
You should also make the computed column PERSISTED and put an index on it.
Jack Corbett
Consultant - Straight Path Solutions
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
April 5, 2012 at 10:11 am
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+.
April 5, 2012 at 10:18 am
Any chance of getting the information we requested?
April 5, 2012 at 10:20 am
It's on its way. I'm sorry for the delay but I'm setting up another test or two in the interim.
April 5, 2012 at 11:21 am
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
April 5, 2012 at 11:27 am
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
April 5, 2012 at 11:29 am
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
April 5, 2012 at 11:30 am
I have now posted the stored procedure and the two functions.
April 5, 2012 at 11:35 am
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.
Jack Corbett
Consultant - Straight Path Solutions
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
April 5, 2012 at 11:54 am
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?
April 5, 2012 at 12:01 pm
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.
Jack Corbett
Consultant - Straight Path Solutions
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
April 5, 2012 at 12:19 pm
So, basically, that would cut my UDF calls in half. A good idea.
April 5, 2012 at 12:28 pm
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