Issue with slow find and replace function code from a lookup table can I do this in a better way

Question

Post reply

Issue with slow find and replace function code from a lookup table can I do this in a better way

Northern Monkey

Ten Centuries

Points: 1017
More actions
August 3, 2016 at 5:10 am

#328806

I have some dim tables for find and replace for colours and sizes as I have multiple columns in a table that could hold the value see below
,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)
WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)
WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)
WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)
WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)
WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)
WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)
WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)
WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)
WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)
END
The function cod is here
ALTER FUNCTION [dbo].[UTfn_LikeColourTableFilter]
(
@Value VARCHAR(8000)
)
RETURNS TABLE AS
RETURN
WITH ColourList AS ( -- replace this section with your Colour table when the time comes.
SELECT SCT.LookupValue,SCT.DisplayInFilter FROM dbo.Dim_Colour SCT
),
LookupList
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY LEN(cl.LookupValue) DESC) AS rn,-- prevents "Yellow" from butchering up "LightGoldenRodYellow"..
cl.LookupValue,cl.DisplayInFilter
FROM
dbo.Dim_Colour cl
WHERE
@Value LIKE '%' + cl.LookupValue + '%'
), RecursiveReplace AS (
-- -- My select 1
SELECT cis.DisplayInFilter AS ReturnString,
cis.rn
FROM
LookupList cis
WHERE
cis.rn = 1
UNION ALL
-- -- My select 2
SELECT cis.DisplayInFilter AS ReturnString,
cis.rn
FROM
LookupList cis
JOIN RecursiveReplace rr
ON cis.rn = rr.rn + 1
)
SELECT TOP 1
rr.ReturnString
FROM
RecursiveReplace rr
ORDER BY
rr.rn DESC;
The dbo.Dim_Colour table has 519 rows
I use the same processes for size but that dim table has over 2500 rows and runs even slower
I did think about adding another parameter for size type but couldn't get it to work 🙁
The function was the one I used from a post here about a year ago but it's just to slow 🙁
Many thanks for any help with this issue 🙂

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

I cannot see a question here. What is the 'issue', exactly?

Can you provide some test data and expected results? That is, a working test harness, rather than just code fragments.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Northern Monkey (8/3/2016)
I have some dim tables for find and replace for colours and sizes as I have multiple columns in a table that could hold the value see below
,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)
WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)
WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)
WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)
WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)
WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)
WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)
WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)
WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)
WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL
THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)
END
The function cod is here
ALTER FUNCTION [dbo].[UTfn_LikeColourTableFilter]
(
@Value VARCHAR(8000)
)
RETURNS TABLE AS
RETURN
WITH ColourList AS ( -- replace this section with your Colour table when the time comes.
SELECT SCT.LookupValue,SCT.DisplayInFilter FROM dbo.Dim_Colour SCT
),
LookupList
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY LEN(cl.LookupValue) DESC) AS rn,-- prevents "Yellow" from butchering up "LightGoldenRodYellow"..
cl.LookupValue,cl.DisplayInFilter
FROM
dbo.Dim_Colour cl
WHERE
@Value LIKE '%' + cl.LookupValue + '%'
), RecursiveReplace AS (
-- -- My select 1
SELECT cis.DisplayInFilter AS ReturnString,
cis.rn
FROM
LookupList cis
WHERE
cis.rn = 1
UNION ALL
-- -- My select 2
SELECT cis.DisplayInFilter AS ReturnString,
cis.rn
FROM
LookupList cis
JOIN RecursiveReplace rr
ON cis.rn = rr.rn + 1
)
SELECT TOP 1
rr.ReturnString
FROM
RecursiveReplace rr
ORDER BY
rr.rn DESC;
The dbo.Dim_Colour table has 519 rows
I use the same processes for size but that dim table has over 2500 rows and runs even slower
I did think about adding another parameter for size type but couldn't get it to work 🙁
The function was the one I used from a post here about a year ago but it's just to slow 🙁
Many thanks for any help with this issue 🙂

We need some sample data. The function seems to be doing unnecessary work. Also, calling it up to 20 times per row is extremely ineffective.

Does the following function returns the same results?

ALTER FUNCTION [dbo].[UTfn_LikeColourTableFilter]

(

@Value VARCHAR(8000)

)

RETURNS TABLE AS

RETURN

WITH LookupList AS (

SELECT ROW_NUMBER() OVER (ORDER BY LEN(cl.LookupValue)) AS rn,-- prevents "Yellow" from butchering up "LightGoldenRodYellow"..

cl.DisplayInFilter

FROM dbo.Dim_Colour cl

WHERE @Value LIKE '%' + cl.LookupValue + '%'

)

SELECT DisplayInFilter AS ReturnString

FROM LookupList

WHERE rn = 1;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Northern Monkey Ten Centuries Points: 1017 More actions · Answer 3

Hi Phil

Sorry for the vague question and not making things clear

The main question is can I use a different method / code for the function as this one seems slow? it's checking for any matching text in the columns in the case statement lower down the page I've also included a small result set from using the function at the bottom of the post along with an extract from the table for the colours it looks for.

CREATE FUNCTION [dbo].[UTfn_LikeColourTableFilter]

(

@Value VARCHAR(8000)

)

RETURNS TABLE AS

RETURN

WITH ColourList AS ( -- replace this section with your Colour table when the time comes.

SELECT SCT.LookupValue,SCT.DisplayInFilter FROM dbo.Dim_Colour SCT

),

LookupList

AS

(

SELECT

ROW_NUMBER() OVER (ORDER BY LEN(cl.LookupValue) DESC) AS rn,-- prevents "Yellow" from butchering up "LightGoldenRodYellow"..

cl.LookupValue,cl.DisplayInFilter

FROM

dbo.Dim_Colour cl

WHERE

@Value LIKE '%' + cl.LookupValue + '%'

), RecursiveReplace AS (

-- -- My select 1

SELECT cis.DisplayInFilter AS ReturnString,

cis.rn

FROM

LookupList cis

WHERE

cis.rn = 1

UNION ALL

-- -- My select 2

SELECT cis.DisplayInFilter AS ReturnString,

cis.rn

FROM

LookupList cis

JOIN RecursiveReplace rr

ON cis.rn = rr.rn + 1

)

SELECT TOP 1

rr.ReturnString

FROM

RecursiveReplace rr

ORDER BY

rr.rn DESC;

Calling the function in the select to find if a column contains a colour name within the text and if it finds one it moves onto the next row to processes

I would like to know if I could do this any better please?

,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

END

Here is a sample from the dim_Colour table used in the function

return from the case with the source columns containing the data

SELECT top 1

SourceColuumnName = 'colour'

,SFA.colour

,SFA.model_number

,SFA.commission_group

,SFA.custom_1

,SFA.custom_2

,SFA.custom_3

,ResultFromFunction = CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL