Performance and handling differences for tally function across servers

  • 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

  • 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

  • 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)

  • 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