April 6, 2006 at 9:40 am
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.
April 6, 2006 at 9:53 am
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
April 6, 2006 at 10:09 am
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.
April 6, 2006 at 10:12 am
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.
April 6, 2006 at 10:16 am
The column does not allow for nulls.
April 6, 2006 at 10:16 am
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
April 6, 2006 at 10:21 am
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.
April 6, 2006 at 10:21 am
So really I could reduce it to:
RTRIM(ERR)>''
(since > is faster than !=)
April 6, 2006 at 10:31 am
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.
April 6, 2006 at 2:37 pm
How many rows are we talking about here?
* Noel
April 6, 2006 at 3:55 pm
April 6, 2006 at 4:02 pm
Not when it is a CHAR field. That is true of VARCHAR.
I wasn't born stupid - I had to study.
April 6, 2006 at 4:22 pm
Try it first, than say.
_____________
Code for TallyGenerator
April 6, 2006 at 6:14 pm
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.
April 6, 2006 at 6:33 pm
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