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

  • 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

  • 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

  • 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.

  • The column does not allow for nulls.


    Live to Throw
    Throw to Live
    Will Summers

  • 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

  • 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.

  • So really I could reduce it to:

     

    RTRIM(ERR)>''

     

    (since > is faster than !=)


    Live to Throw
    Throw to Live
    Will Summers

  • 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

  • How many rows are we talking about here?

     


    * Noel

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

    is equal to

    LEN(ERR) > 0

    _____________
    Code for TallyGenerator

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

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

  • Try it first, than say.

    _____________
    Code for TallyGenerator

  • 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.

  • 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

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

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