February 19, 2009 at 11:06 am
I 've just been given this function. They claim that this function causes a 'Buffer Pool Memory' issue. My understanding is that it simply takes to long to return records.
Is there a better way to write this?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fComputeNameScore](@SearchName varchar(100), @FileName varchar(100), @CharTested INT)
RETURNS DECIMAL
AS
BEGIN
DECLARE @Output decimal
DECLARE @CharToBeTested INT ; SET @CharToBeTested = 0
DECLARE @CharTestedThisIteration INT ; SET @CharTestedThisIteration = 0
DECLARE @SearchNameChar INT ; SET @SearchNameChar = 1
DECLARE @FileNameChar INT SET @FileNameChar = 1
DECLARE @HitCount INT ; SET @HitCount = 0
DECLARE @MaxTestedChar INT ; SET @MaxTestedChar=15
SET @Output = 0.0
--Normalize parameters
--SET @SearchName = dbo.fCompressName(@SearchName)
SET @FileName = dbo.fCompressName(@FileName)
IF LEN(@SearchName) < LEN(@FileName)
SET @CharToBeTested = LEN(@SearchName)
ELSE
SET @CharToBeTested = LEN(@FileName)
IF @CharTestedThisIteration > @MaxTestedChar
SET @CharTestedThisIteration = @MaxTestedChar
DECLARE @a char(1) -- SUBSTRING(@SearchName,@SearchNameChar, 1)
DECLARE @b-2 char(1) -- SUBSTRING(@FileName,@FileNameChar, 1)
DECLARE @AA char(1) -- SUBSTRING(@SearchName+' ',@SearchNameChar + 1, 1)
DECLARE @BB char(1) -- SUBSTRING(@FileName+' ',@FileNameChar + 1, 1)
--Scanning Logic
WHILE ((@CharTested 0))
BEGIN
SET @a = SUBSTRING(@SearchName,@SearchNameChar, 1)
SET @b-2 = SUBSTRING(@FileName,@FileNameChar, 1)
SET @AA = SUBSTRING(@SearchName+' ',@SearchNameChar + 1, 1)
SET @BB = SUBSTRING(@FileName+' ',@FileNameChar + 1, 1)
BEGIN
SET @HitCount = @HitCount + 1
SET @FileNameChar = @FileNameChar + 1
SET @SearchNameChar = @SearchNameChar + 1
END
ELSE
IF @a = @AA
AND @b-2 = @BB
BEGIN
SET @HitCount = @HitCount + 1
SET @FileNameChar = @FileNameChar + 1
SET @SearchNameChar = @SearchNameChar + 1
SET @CharTestedThisIteration = @CharTestedThisIteration + 1
END
ELSE
IF @a = @BB
AND @AA = @b-2
BEGIN
SET @HitCount = @HitCount + 2
SET @FileNameChar = @FileNameChar + 2
SET @SearchNameChar = @SearchNameChar + 2
SET @CharTestedThisIteration = @CharTestedThisIteration + 1
END
ELSE
IF @AA = @b-2
BEGIN
SET @SearchNameChar = @SearchNameChar + 1
END
ELSE
IF @a = @BB
BEGIN
SET @FileNameChar = @FileNameChar + 1
END
ELSE
BEGIN
SET @FileNameChar = @FileNameChar + 1
SET @SearchNameChar = @SearchNameChar + 1
END
SET @CharTestedThisIteration = @CharTestedThisIteration + 1
SET @CharTested = @CharTested + 1
END
SET @Output = (CONVERT(decimal,@HitCount) / CONVERT(decimal, @CharTested)) * 100
--SET @Output = (@HitCount / @CharTested) * 100
RETURN @Output
END
February 19, 2009 at 11:27 am
Hard to tell without having the data, etc., that goes with it. It looks like it calls a couple of other functions. Have you tested to find out whether it's one of those that's causing the slowdown?
To really help out on this more directly, you'll need to provide table definitions, insert statements for some sample data, and the definitions of the other functions.
At the very least, save, zip and upload the execution plan for the function.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 19, 2009 at 12:36 pm
Thanks GSquared.
Here's the proc that calls the two functions:
USE [FL16_QA]
GO
/****** Object: StoredProcedure [dbo].[_TPX_NameSearch_SEL] Script Date: 02/19/2009 13:55:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[_TPX_NameSearch_SEL]
@fullname varchar(100)
,@minPercentage int
,@dateStart datetime
,@dateEnd datetime
AS
SET NOCOUNT ON
set transaction isolation level read uncommitted
CREATE TABLE #names
(
NameID int
,FullName varchar(100)
,Percentage decimal
)
declare @compressName varchar(50)
declare @primarykey-2 smallint
declare @alternateKey smallint
declare @time datetime
set @time = getdate()
exec master..xp_metaphone @fullname, @primarykey-2 output, @alternateKey output
declare @pd datetime
set @pd = (select plantdate from tblcontrol with (NOLOCK))
set @compressName = dbo.fCompressName(@fullname)
--Grab names to be scored
INSERT INTO #names
SELECT
NameID
, FullName
, (dbo.fComputeNameScore(@compressName, FullName, 0))
FROM
NameSearchKeys with (nolock)
WHERE
NameKey IN (@primaryKey, @alternateKey)
--Filter by date and score
SELECT distinct n.NameID, n.FullName, @fullname as NickName, n.Percentage, @pd as PlantDate
FROM namesxref xr with (nolock)
join #names n
on n.nameid=xr.nameid
where xr.PostedDate between @dateStart AND @dateEnd
and n.Percentage >= @minPercentage
--select 'Filter by date and score',@time as start,getdate() as finish,datediff(mi,getdate(),@time)
DROP TABLE #names
Here's the first function:
-- Create date:
-- Description:
-- =============================================
ALTER FUNCTION [dbo].[fCompressName]
(
@string varchar(200)
)
RETURNS varchar(200)
AS
BEGIN
DECLARE @position int, @C int, @result varchar(200)
DECLARE @char char(1)
-- Initialize the current position and the string variables.
SET @position = 0
SET @string = UPPER(LTRIM(RTRIM(@string)))
SET @result = ''
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT @C = ASCII(SUBSTRING(@string, @position, 1))
SELECT @char = SUBSTRING(@string, @position, 1)
IF (@c >= 65 AND @C = 97 AND @C <= 122)
BEGIN
IF @position = 1
BEGIN
SET @result = @char
END
ELSE
BEGIN
SET @result = @result + @char
END
END
SET @position = @position + 1
END
if @result is null
BEGIN
set @result = ''
END
RETURN @result
END
And the second function:
USE [FL16_QA]
GO
/****** Object: UserDefinedFunction [dbo].[fComputeNameScore] Script Date: 02/19/2009 13:56:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fComputeNameScore](@SearchName varchar(100), @FileName varchar(100), @CharTested INT)
RETURNS DECIMAL
AS
BEGIN
DECLARE @Output decimal
DECLARE @CharToBeTested INT ; SET @CharToBeTested = 0
DECLARE @CharTestedThisIteration INT ; SET @CharTestedThisIteration = 0
DECLARE @SearchNameChar INT ; SET @SearchNameChar = 1
DECLARE @FileNameChar INT SET @FileNameChar = 1
DECLARE @HitCount INT ; SET @HitCount = 0
DECLARE @MaxTestedChar INT ; SET @MaxTestedChar=15
SET @Output = 0.0
--Normalize parameters
--SET @SearchName = dbo.fCompressName(@SearchName)
SET @FileName = dbo.fCompressName(@FileName)
IF LEN(@SearchName) < LEN(@FileName)
SET @CharToBeTested = LEN(@SearchName)
ELSE
SET @CharToBeTested = LEN(@FileName)
IF @CharTestedThisIteration > @MaxTestedChar
SET @CharTestedThisIteration = @MaxTestedChar
DECLARE @a char(1) -- SUBSTRING(@SearchName,@SearchNameChar, 1)
DECLARE @b-2 char(1) -- SUBSTRING(@FileName,@FileNameChar, 1)
DECLARE @AA char(1) -- SUBSTRING(@SearchName+' ',@SearchNameChar + 1, 1)
DECLARE @BB char(1) -- SUBSTRING(@FileName+' ',@FileNameChar + 1, 1)
--Scanning Logic
WHILE ((@CharTested 0))
BEGIN
SET @a = SUBSTRING(@SearchName,@SearchNameChar, 1)
SET @b-2 = SUBSTRING(@FileName,@FileNameChar, 1)
SET @AA = SUBSTRING(@SearchName+' ',@SearchNameChar + 1, 1)
SET @BB = SUBSTRING(@FileName+' ',@FileNameChar + 1, 1)
BEGIN
SET @HitCount = @HitCount + 1
SET @FileNameChar = @FileNameChar + 1
SET @SearchNameChar = @SearchNameChar + 1
END
ELSE
IF @a = @AA
AND @b-2 = @BB
BEGIN
SET @HitCount = @HitCount + 1
SET @FileNameChar = @FileNameChar + 1
SET @SearchNameChar = @SearchNameChar + 1
SET @CharTestedThisIteration = @CharTestedThisIteration + 1
END
ELSE
IF @a = @BB
AND @AA = @b-2
BEGIN
SET @HitCount = @HitCount + 2
SET @FileNameChar = @FileNameChar + 2
SET @SearchNameChar = @SearchNameChar + 2
SET @CharTestedThisIteration = @CharTestedThisIteration + 1
END
ELSE
IF @AA = @b-2
BEGIN
SET @SearchNameChar = @SearchNameChar + 1
END
ELSE
IF @a = @BB
BEGIN
SET @FileNameChar = @FileNameChar + 1
END
ELSE
BEGIN
SET @FileNameChar = @FileNameChar + 1
SET @SearchNameChar = @SearchNameChar + 1
END
SET @CharTestedThisIteration = @CharTestedThisIteration + 1
SET @CharTested = @CharTested + 1
END
SET @Output = (CONVERT(decimal,@HitCount) / CONVERT(decimal, @CharTested)) * 100
--SET @Output = (@HitCount / @CharTested) * 100
RETURN @Output
END
I'm working on getting the e plan now. (I can't save it, permissions issue)
February 20, 2009 at 12:04 pm
There's a line in the CompressName function that tests the value of @C. I think it may not have come across in the forum correctly, because it checks if @C is greater than or equal to 65 and equal to 97 and less than or equal to 122. In other words, it will only return anything at all if the number is exactly 97. I'm going to assume that the equation should be from 65 to 90 and from 97 to 122. Let me know if that's not correct.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 20, 2009 at 12:10 pm
Even without the execution plan, we'd need the table definitions and some sample data to test this out with. Can you provide that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply