Excellent suggestions Paul!

I changed the argument varchars from MAX to 1200 and that improved performance marginally ~5%. I switched the collation type and selected the top 60,000 again but found it still took between 30 and 35 seconds. However, when I selected INTO a temp table I saw that performance had jumped by a factor of 4! I went from 26 seconds to just 6!

This means I can clean the 13 million rows in about 25 minutes now. Ideally I would like to cut this in half, but it's still a big win. I guess I have to read up on collations now!

Carl

Here's the updated code for anyone interested:

CREATE FUNCTION dbo.IF_CleanWithReplace

(

@SomeText VARCHAR(1200)

)

RETURNS TABLE

WITH SCHEMABINDING

AS RETURN

SELECT

cleaned =

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

@SomeText COLLATE LATIN1_GENERAL_BIN,

char(0),''), char(1),''), char(2),''), char(3),''), char(4),''),

char(5),''), char(6),''), char(7),''), char(8),''), char(9),''),

char(10),''), char(11),''), char(12),''), char(13),''), char(14),''),

char(15),''), char(16),''), char(17),''), char(18),''), char(19),''),

char(20),''), char(21),''), char(22),''), char(23),''), char(24),''),

char(25),''), char(26),''), char(27),''), char(28),''), char(29),''),

char(128),'');

GO

PS> I replaced the SPACE(0) with '' - it didn't seem to change performance. What's the idea behind SPACE(0)??