SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Witch query is more efficient??


Witch query is more efficient??

Author
Message
ahmed.net
ahmed.net
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 43
Witch query is more efficient??
Table Structure:
CREATE TABLE [dbo].[tbl_test](
[ID] [int] NOT NULL,
[Title] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tbl_test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
Query1:
SELECT ID, Title
FROM tbl_test
WHERE (ID = 1) OR (Title LIKE '%abc%')
Query2:
SELECT ID, Title
FROM tbl_test
WHERE (Title LIKE '%abc%') OR (ID = 1)

I just want to know
Whether or not changing the order of columns in where clause can effect performance(using OR in where)?

comparing ID field is faster than the like statement on Title column. so if the first statement in OR is true next statement should not be checked..
I think Query1 is faster as Like statement on title will not always be checked. and in query2 Like statement on title will always be checked...
What u say ??
winash
winash
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2024 Visits: 1883
I don't recommend relying on short-circuiting by the query optimizer - i.e. assuming that an "OR" or an "AND" condition is always going to be evaluated left to right. The order of evaluation is up to the query optimizer which in turn depends on the query, indexes, data distribution, choice of query plan etc.

You can try to use a CASE statement to see if that allows for a short-circuit. The rules for the CASE statement evaluation are documented as follows:

Simple CASE expression:
The simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.
Allows only an equality check.
Evaluates input_expression, and then in the order specified, evaluates input_expression = when_expression for each WHEN clause.
Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.
If no input_expression = when_expression evaluates to TRUE, the SQL Server Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.


Searched CASE expression:
Evaluates, in the order specified, Boolean_expression for each WHEN clause.
Returns result_expression of the first Boolean_expression that evaluates to TRUE.
If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.




GilaMonster
GilaMonster
SSC Guru
SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)

Group: General Forum Members
Points: 116080 Visits: 45520
ahmed.net (10/9/2010)

Whether or not changing the order of columns in where clause can effect performance(using OR in where)?


Nope.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)

Group: General Forum Members
Points: 116239 Visits: 41430
ahmed.net (10/9/2010)
Witch query is more efficient??
Table Structure:
CREATE TABLE [dbo].[tbl_test](
[ID] [int] NOT NULL,
[Title] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tbl_test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
Query1:
SELECT ID, Title
FROM tbl_test
WHERE (ID = 1) OR (Title LIKE '%abc%')
Query2:
SELECT ID, Title
FROM tbl_test
WHERE (Title LIKE '%abc%') OR (ID = 1)

I just want to know
Whether or not changing the order of columns in where clause can effect performance(using OR in where)?

comparing ID field is faster than the like statement on Title column. so if the first statement in OR is true next statement should not be checked..
I think Query1 is faster as Like statement on title will not always be checked. and in query2 Like statement on title will always be checked...
What u say ??


What I say is... trust no one on such a thing. Not even the "pros" even though they're mostly right ;-). Instead, do a test with a million rows and find out for yourself. Many good things will come from that...

1. You'll know the truth for sure because you will have seen it with your own eyes.
2. You'll remember the correct answer longer because you will have seen it with your own eyes.
3. You'll know how to build a million row test table.

:-D

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
harag
harag
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 84
Hi

Though I'm no expert my initial thought is, that they might not be any difference in the two selects when using an OR where, because SQL would have to check the ID field to see if it's a 1, and no matter if it is or not it would still need to check the title field for the like condition.

Now if it was an AND clause then maybe it might make a difference, especially if you have indexed the ID column but not the title column.

If you have ID=1 AND title LIKE '%text%' then if the first condition returns false then why would SQL need to check the next condition?

I've not tested this so if you do decide to create 1,000,000 rows of data to check the OR condition as suggested, then you might as well check it as an AND to see.

hope this helps.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)

Group: General Forum Members
Points: 116080 Visits: 45520
harag (10/11/2010)
Now if it was an AND clause then maybe it might make a difference, especially if you have indexed the ID column but not the title column.

If you have ID=1 AND title LIKE '%text%' then if the first condition returns false then why would SQL need to check the next condition?


If ID is indexed and the title is not then (ID = 1 and title LIKE %text%') and (title LIKE '%text%' and ID=1) will be treated exactly the same way. An index seek on the index with ID, matching rows get the title column looked up and then compared.

Order of evaluation (when columns are involved) is based on what indexes exist and which conditions the optimiser thinks will be quicker to evaluate/reduce row count faster, not on the position of the condition in the where clause.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search