SQL Server Performance Forum – Threads Archive

speed up LEN(RTRIM(LTRIM(ERR))) > 0????

ERR is a column of char(255) How can I speed up this reference? LEN(RTRIM(LTRIM(ERR))) > 0 I need to find out if there is anything in ERR that is not blanks. This query is going to be ran thousands of times a day on nonstatic data, so precalculating to another table is not a valid option. I haven’t tried to convert ERR to a varchar(255), would this speed up LEN(RTRIM(LTRIM(ERR))) > 0?? Thanks in advance for any responses to this post.
Live to Throw
Throw to Live

Something like this, perhaps? DECLARE @Err CHAR(255)
SET @Err = REPLICATE(‘ ‘, 255) SELECT *
FROM tbl
WHERE ERR <> @Err

I tried that and it didn’t speed anything up. The queryplan and the actual runtimes were the same. Live to Throw
Throw to Live

Try this select * from yourTable where Err like ‘[^ ]%’ Madhivanan Failing to plan is Planning to fail

Clever one!

The fastest thing that I have found is to evaluate the expression down to this: RTRIM(ERR)>” This is because I only want to find out if there is any text in the string. I don’t really care about the length, just if there is text in the string. Live to Throw
Throw to Live

Okay, why didn’t I check this before … WHERE LEN(ERR) > 0 As soon as there is a character other than a space, LEN will return the position of the last character. No need to trim anything. If ERR is null, then LEN(ERR) returns null as well, and the criteria would not be satisfied.

Which is faster LEN or RTRIM? They show up as the same if you do a queryplan. Live to Throw
Throw to Live

Not sure,, if this will speed up your query, but
WHERE CHECKSUM(your_column) > 0 will also work. Also, I would consider changing to VARCHAR(255). This will save you some storage space, so that more rows fit on a single page and SQL Server thus has to perform less reads to satisfy the query. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

use the VARCHAR as suggested and if you can make sure that you only populate the column if there’s an actual error else place null, then you can use the IS NULL then which takes less read. In DBMS principle, the less hard disk read, the faster (i.e. LEFT(data,1) <> ” is faster than LEN(data) since the function only takes Q(n) = 1 compared to LEN Q(n) = n where n is unit of space* allocated in memory/disk. In another point-of-view, why don’t run a stored procedure/trigger instead of checking a table for n times and see if there is an error. Sometimes effectiveness is better than efficiency.

]]>