query optimisation

Question

query optimisation

ramuvanparti

SSCrazy

Points: 2368
More actions
September 9, 2009 at 4:31 am

#204359

select ltrim(rtrim(col1)) from tab
where col2 = 12
or
select col1 from tab
where col2 = 12
clustered index is present on col2 and non-clustered on col1
which query is more optimal.

Viewing 14 posts - 1 through 13 (of 13 total)

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

Silverfox SSC-Insane Points: 23150 More actions · Answer 1

the easiest thing to do is to look at the query plan. if you need help with that, just attach them here

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

sureshr69 SSC Veteran Points: 253 More actions · Answer 2

You will notice that the second approach is faster when you are running this query for selecting a larger recordset.

Thanks

Suresh Ramamurthy

Suresh B. SSC-Insane Points: 22986 More actions · Answer 3

ramuvanparti (9/9/2009)
select ltrim(rtrim(col1)) from tab
where col2 = 12
or
select col1 from tab
where col2 = 12
clustered index is present on col2 and non-clustered on col1
which query is more optimal.

Both are equal as clustered index seek will happen.

ltrim(rtrim(col1)) will not make much difference.

ramuvanparti SSCrazy Points: 2368 More actions · Answer 4

ramuvanparti

SSCrazy

Points: 2368

September 9, 2009 at 4:46 am

#1050968

please find the attached

Silverfox SSC-Insane Points: 23150 More actions · Answer 5

looking at them now

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

Then trim version is longer by an estimated 0.0000001 cpu cost.

Whats your real issue ?

Clear Sky SQL
My Blog[/url]

Silverfox SSC-Insane Points: 23150 More actions · Answer 7

Both of them use the clustered index, and both use a clustered index seek.

execution plan2 is marginally faster and doesnt have a separate operation for Compute Scalar.

So not much between them but query 2 would be better. If you had to make the distinction.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 8

Silverfox (9/9/2009)
If you had to make the distinction

Although IMO , its better to base the distinction on functional need. 🙂

If you NEED to trim trailing and leading spaces , then use ltrim/rtrim , if you dont , then dont.

Clear Sky SQL
My Blog[/url]

Silverfox SSC-Insane Points: 23150 More actions · Answer 9

Dave Ballantyne (9/9/2009)
Silverfox (9/9/2009)
If you had to make the distinction
Although IMO , its better to base the distinction on functional need. 🙂
If you NEED to trim trailing and leading spaces , then use ltrim/rtrim , if you dont , then dont.

Only answering the question that was asked 😛

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 10

Both queries are same so the plan should be same .But since you are trimming the spaces it will add some overhead .That extra overhead will be in the form of compute Scalar which will be at the cost of CPU.

Regards

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

ramuvanparti SSCrazy Points: 2368 More actions · Answer 11

from this i came to conclusion that if i have more than 1 million record and use first query it will cost more compared to second one 🙂

thanks for the reply

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 12

ramuvanparti (9/9/2009)
select ltrim(rtrim(col1)) from tab
where col2 = 12
or
select col1 from tab
where col2 = 12
clustered index is present on col2 and non-clustered on col1
which query is more optimal.

I'd recommend fixing the underlying problem of storing leading and trailing spaces. Do an UPDATE on the column to remove both so you don't have to worry about such things nor the impact that leading spaces has on JOIN criteria.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramuvanparti SSCrazy Points: 2368 More actions · Answer 13

ya you are right, if there leading and trailing spaces it would more efficient and overhead of trim function usage is not required