Optimizing Queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Optimizing Queries

Author

Topic

Legend1673
Starting Member

6 Posts

Posted - 2010-03-21 : 21:18:34

Is there a way to specify which parameter in a query is executed first?

For example (very simplified):
Select * from test where name='BOB' and salesdate>'2010-01-01'

Can I specify the query to first look at the salesdate and then the name?

Hope this makes sense.

Thanks.

rickymartin06
Starting Member

3 Posts

Posted - 2010-03-21 : 23:31:50

umm... i think that will work is that what you ment?

Select * from test where salesdate>'2010-01-01' and name='BOB'

Legend1673
Starting Member

6 Posts

Posted - 2010-03-21 : 23:36:59

I don't know, does SQL always execute parameters from left to right?

Kristen
Test

22859 Posts

Posted - 2010-03-22 : 03:40:55

"i think that will work "

It won't make any actual difference to the query plan / query execution.

"does SQL always execute parameters from left to right?"

No

In particular SQL will NOT do operator short-circuiting

WHERE ColumnEquals1=1 OR ColumnEquals1/0

(assuming that [ColumnEquals1] contains "1") may give an error in SQL because it because the Query Plan which is created must work for all values of ColumnEquals1

This type of construction will not give an error in most procedural languages.

Why are you concerned about what order the parameters are tested?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-22 : 04:07:04

The query optimizer will choose to execute the where (or join) conditions in the order that is the most effective, given the information (statistics) it has about indexes, index/data selectivity and data distribution. It is possible to force the query optimizer to use a specific execution plan and you can create this entirely by yourself, but it's generally advised strongly against unless you have *very* good control over what's going on. Back in sql server 2000 and earlier version it was recommended to put the join and where conditions in the same order as they were in the index but this is no longer the case.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-22 : 05:16:54

<<
Can I specify the query to first look at the salesdate and then the name?
>>

Try derived table

select * from
(
Select * from test where salesdate>'2010-01-01'
) as t
where name='BOB'

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-22 : 05:25:45

I'm sorry to say madhi but even though your query does do what the OP requests, I don't think it's a good advice. Forcing the execution order like this "removes" the purpose of the query optimizer and even though the developer might at some point be smarter than the optimizer this is rarely the case.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-22 : 05:36:22

I think madhi clearly mentioned the part of question and given the answer for that question not for whole problem.
his answer is specific for specific question.

Vaibhav T

Kristen
Test

22859 Posts

Posted - 2010-03-22 : 08:00:07

Indeed, but I think it is worth asking why the O/P wants to do this as I can't think of any good reason (in normal operation).

There is a reasonable chance that the O/P has a misunderstanding, and if my assumption is correct we would be doing a better service explaining that, rather than blindly providing a solution to the question as stated

If someone with thousands of posts asks a bizarre question I answer it. Someone with less posts I question why they want to do it that way.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-22 : 10:01:21

I thought OP asked that question becuase of the behaviour changes

Example

Find out data that has only numbers

Usual method

declare @t table(data varchar(100))
insert into @t
select 'h93' union all
select '8713' union all
select '12d4' union all
select '893' union all
select 'jh9'
select * from @t
where data not like '%[^0-9]%'
and data >100

This works in versions prior to 2005
From version 2005 onwards the order is not forced and would throw error
In such cases derived table with forced order may be helpful

declare @t table(data varchar(100))
insert into @t
select 'h93' union all
select '8713' union all
select '12d4' union all
select '893' union all
select 'jh9'
select data from
(
select case when data not like '%[^0-9]%' then data else null end as data from @t
where data not like '%[^0-9]%'
) as t
where data >100

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-03-22 : 10:17:03

Interesting, I thought I had had that (i.e. error raised because conditions not executed left-to-right) in SQL 2000 with

WHERE IsDate(SomeStringColumn) AND CONVERT(datetime, SomeStringColumn) > @SomeDateTime

but its too long ago now so I can't remember for sure.

If O/P question is related to testing, e.g., if a string is a number, and then using the string as a number, it would have been better to post a representative query - "where name='BOB' and salesdate>'2010-01-01'" isn't going to misbehave whichever way you write it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-22 : 11:25:21

quote:
Originally posted by madhivanan

I thought OP asked that question becuase of the behaviour changes

Example

Find out data that has only numbers

Usual method

declare @t table(data varchar(100))
insert into @t
select 'h93' union all
select '8713' union all
select '12d4' union all
select '893' union all
select 'jh9'
select * from @t
where data not like '%[^0-9]%'
and data >100

This works in versions prior to 2005
From version 2005 onwards the order is not forced and would throw error
In such cases derived table with forced order may be helpful

declare @t table(data varchar(100))
insert into @t
select 'h93' union all
select '8713' union all
select '12d4' union all
select '893' union all
select 'jh9'
select data from
(
select case when data not like '%[^0-9]%' then data else null end as data from @t
where data not like '%[^0-9]%'
) as t
where data >100

Madhivanan

Failing to plan is Planning to fail

That was a good point

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-22 : 14:28:11

quote:
Originally posted by madhivanan

From version 2005 onwards the order is not forced and would throw error
In such cases derived table with forced order may be helpful

declare @t table(data varchar(100))
insert into @t
select 'h93' union all
select '8713' union all
select '12d4' union all
select '893' union all
select 'jh9'
select data from
(
select case when data not like '%[^0-9]%' then data else null end as data from @t
where data not like '%[^0-9]%'
) as t
where data >100

I still don't think you can guarantee that this query will not fail. Can you? Or does the CASE statement guarantee that?

I've seen instances in the past where the optimizer tries to.. well.. optimize and will combine the derived table and the outer where clause.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources