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

Question

Post reply

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

Will1922

SSCertifiable

Points: 7189
More actions
April 6, 2006 at 9:40 am

#171159

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
Will Summers

Viewing 15 posts - 1 through 15 (of 26 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

Just create a calculated column on the table and then set an index on it

your query will end up been like:

select ...... calculated_column > 0

Cheers,

* Noel

Will1922 SSCertifiable Points: 7189 More actions · Answer 2

I tried that and it didn't speed anything up. The run times are the same and the query plan say that queries take equal resources.

It does simplify my query, but doesn't help performance.

Live to Throw
Throw to Live
Will Summers

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

Does the column allow for NULLs? You could check for that and see if it is quicker...

I wasn't born stupid - I had to study.

Will1922 SSCertifiable Points: 7189 More actions · Answer 4

The column does not allow for nulls.

Live to Throw
Throw to Live
Will Summers

Eddie Wuerch SSChampion Points: 12532 More actions · Answer 5

Note that if the value you are testing is blank or just spaces, then either LTRIM or RTRIM will reduce it to a zero-length string. You don't need to do both.

-Eddie

Eddie Wuerch
MCM: SQL

Farrell Keough SSCoach Points: 17414 More actions · Answer 6

Can you make it allow NULLs? That would require fewer functions to be implemented for checking...

I wasn't born stupid - I had to study.

Will1922 SSCertifiable Points: 7189 More actions · Answer 7

So really I could reduce it to:

RTRIM(ERR)>''

(since > is faster than !=)

Live to Throw
Throw to Live
Will Summers

Will1922 SSCertifiable Points: 7189 More actions · Answer 8

If I allow for nulls it will slow down my updates and editing.

If there are any nulls in a table then an update is done as a delete-then-insert.

If there are no nulls in a table then the update is done in place.

Updates in place are suppose to be much faster.

I am pretty sure I got that from BOL, but I can't remember for sure.

Live to Throw
Throw to Live
Will Summers

noeld SSC Guru Points: 96590 More actions · Answer 9

How many rows are we talking about here?

* Noel

Sergiy SSC Guru Points: 110206 More actions · Answer 10

LEN(RTRIM(LTRIM(ERR))) > 0

is equal to

LEN(ERR) > 0

_____________
Code for TallyGenerator

Farrell Keough SSCoach Points: 17414 More actions · Answer 11

Not when it is a CHAR field. That is true of VARCHAR.

I wasn't born stupid - I had to study.

Sergiy SSC Guru Points: 110206 More actions · Answer 12

Try it first, than say.

_____________
Code for TallyGenerator

Farrell Keough SSCoach Points: 17414 More actions · Answer 13

Sergiy, you are correct! I had always been under the impression that char fields padded their entries... And I am using Simple SQL db at my home.

CREATE TABLE #Character( Column1 char(20))

INSERT INTO #Character VALUES( 'TEST')

INSERT INTO #Character VALUES( '')

CREATE TABLE #VarCharacter( Column1 varchar(20))

INSERT INTO #VarCharacter VALUES( 'TEST')

INSERT INTO #VarCharacter VALUES( '')

SELECT LEN( Column1) AS 'Len Char', Column1 FROM #Character

SELECT LEN( Column1) AS 'Len VarChar', Column1 FROM #VarCharacter

DROP TABLE #Character

DROP TABLE #VarCharacter

I wasn't born stupid - I had to study.

noeld SSC Guru Points: 96590 More actions · Answer 14

Well I have to say.... that is not really exact:

select Len ( ' Space in the begining '), Len (rtrim(ltrim(' Space in the begining ')))

Those two numbers are not equal

* Noel