August 20, 2010 at 9:24 am
Hi All
I have created a function that cleans a string of all unknown characters other than [a-z] and returns the string using a tally table loop. The function loops through the characters one by one returning anything that isn't a alpha character.
I think i found some of the code on here and adapted it slightly...very useful!
CREATE FUNCTION [dbo].[Fn_StringClean](
@String varchar(255),
@Excp varchar(100))
RETURNS varchar(255)
AS
BEGIN
DECLARE @Return varchar(255)
SET @Return = ''
SELECT @Return = @Return + SUBSTRING(@String,N,1)
FROM Tally
WHERE N <= Len(@String)
AND SUBSTRING(@String,N,1) LIKE @Excp -- Exceptions
RETURN Ltrim(Rtrim(REPLACE(REPLACE(@Return,' ',' '),' ',' '))) -- Remove spaces.
END
GO
I am using this function to loop through a set of names and clean them
select top 1000
Fn_StringClean(CustDetail.last_name + CustDetail.first_name,'[a-z]')
from customertbl
On one server I can see from profiler that this is being passed in one hit as one batch, however in another it is being passed through 1000 times and is taking 16 times longer. Can anyone explain why, and possibly tell me how I can stop this.
All Db level settings seem the same, indexes are not needed and execution plan seems the same.
If you have any questions I will try and answer them as best I can
Many thanks everyone in advance for your help..
Matt
August 20, 2010 at 9:27 am
Tally table create script taken from Jeffs tally table article....Thanks Jeff!
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Dbo].[Tally]') AND type in (N'U'))
DROP TABLE [Dbo].[Tally]
GO
/****** Object: Table [Dbo].[Tally] Script Date: 08/12/2010 11:49:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N)
WITH FILLFACTOR = 100
August 20, 2010 at 3:51 pm
Do you get the same discrepancy if you bring the logic in-line using CROSS APPLY?
SELECT TOP 1000 CleanString
FROM customertbl
CROSS APPLY
(
SELECT SUBSTRING(last_name + first_name, N, 1)
FROM Tally
WHERE N <= LEN(last_name + first_name)
AND SUBSTRING(last_name + first_name, N, 1) LIKE '[a-z]' FOR XML PATH('')
) AS Z (CleanString)
August 23, 2010 at 4:57 am
Hi,
Thanks for this. It hasn't actually solved the fluctuation in performance across the servers which is very confusing, but this works efficiently and is handled exactly the same on both.
Thank you very much for this. It is massively appreciated after a day spent trying to work out what was going on.
😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply