Need help SQL performance issue.

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

SQL Server 2008 Forums

Transact-SQL (2008)

Need help SQL performance issue.

Author

Topic

gauravsyntel
Starting Member

4 Posts

Posted - 2011-05-02 : 01:49:42

Please advive which one is better to use?

@name as varvhar(10) (Input para)
@flag as bit (Input para)
if @flag = 1 then
@name = left(@name,3)
end

which query will be more faster
select * from tblA where tblA.name = @name and @flag = 1
union
select * from tblA where left(tblA.name,3) = @name and @flag = 0

OR

select * from tblA
where @name =
CASE when @flag = 1
then tblA.name
else left(tblA.name,3)
END

Sachin.Nand

2937 Posts

Posted - 2011-05-02 : 02:35:04

Better in what sense ? Performance,maintainability?

PBUH

gauravsyntel
Starting Member

4 Posts

Posted - 2011-05-02 : 02:40:56

For both.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-02 : 03:36:23

I would like to use first one, because functions in WHERE clause will not help optimizer to select proper index.
http://connectsql.blogspot.com/2010/12/cautions-to-be-taken-for-where-clause.html

--------------------------
http://connectsql.blogspot.com/

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-05-02 : 10:17:23

quote:
Originally posted by lionofdezert

I would like to use first one, because functions in WHERE clause will not help optimizer to select proper index.
http://connectsql.blogspot.com/2010/12/cautions-to-be-taken-for-where-clause.html

--------------------------
http://connectsql.blogspot.com/

Although it is a good point, that is really about applying functions to columns in any type of search criteria. I don't think in this sense, the first one is better, in both queries, there is a function on column involved in a search criteria, just that it is placed differently. Otherwise, from what you are saying we should all go out and convert our slow queries that have where clauses that involve functions on columns and convert them into case statements and they'd be fast!

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-05-02 : 10:28:09

quote:
Originally posted by gauravsyntel

Please advive which one is better to use?

I'm not sure but on thing to make the first query faster would be to change Union to Union All - here is an article on the topic: http://blog.sqlauthority.com/2007/03/10/sql-server-union-vs-union-all-which-is-better-for-performance/.

I think what would make the most sense would be to write it the 'usual' way:

select * from tblA
where 
  (tblA.name = @name and @flag = 1)
  or
  (left(tblA.name,3) = @name and @flag = 0)

I think it's easier to read and I doubt if either of the 2 queries you wrote would be any faster.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-05-02 : 11:39:42

quote:
Originally posted by gauravsyntel

Please advive which one is better to use?

@name as varvhar(10) (Input para)
@flag as bit (Input para)
if @flag = 1 then
@name = left(@name,3)
end

which query will be more faster
select * from tblA where tblA.name = @name and @flag = 1
union
select * from tblA where left(tblA.name,3) = @name and @flag = 0

OR

select * from tblA
where @name =
CASE when @flag = 1
then tblA.name
else left(tblA.name,3)
END

@name as varchar(10) (Input para)
@flag as bit (Input para)

if @flag = 1 select * from tblA where tblA.name = left(@name, 3)
else select * from tblA where tblA.name like left(@name, 3) + '%' and len(@name) <= 3

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources