carl.anderson-1037280 (4/2/2010)
I see. So if you want to remove the full set of 37 unprintable characters, you would have to create 37 levels of nesting with REPLACE. I guess the computer scientist in me is concerned about 37 full string scans per value when Jeff's code only does one.
REPLACE is pretty fast, especially when a binary or SQL collation is used.
However, Jeff also uses the PATINDEX function and double wildcards, so I guess it says something about the performance of PATINDEX if the nested REPLACE functions are still faster.
Seems like there should be a better way! CLR maybe?
Maybe a better question is, why is a deeply nested REPLACE so much faster than Jeff's solution
The answer is that optimizing the replacement operation is much less important than optimizing the overall query plan. Let me explain that...
The nested REPLACE uses in in-line table-valued function, which is logically just a parameterized view. The definition of the 'function' is expanded in-line into the query plan of the calling statement before SQL Server optimization occurs. This is the reason that the whole operation can be performed with a single scan and a Compute Scalar.
Scalar T-SQL functions cannot be in-lined in this way, and are invoked once per row using a relatively slow interface. There overheads are such as to completely dominate the overall cost.
A CLR scalar function uses a much faster invocation path, but the overhead of passing the values row-by-row to and from the hosted environment again dominate the overall cost.
Believe me (and Jeff will attest to this) when I say I am very keen on appropriate use of SQLCLR integration. Unfortunately, an in-line function will out-perform a CLR scalar function every time in this sort of task. The difference can be made small (and CLR is much faster than a T-SQL scalar function) but never faster than the fully in-lined solution.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi