where clause tuning - 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

where clause tuning

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2011-05-20 : 04:01:06

hi can anybody tune this query:


declare @import varchar(100)='ALL'
if @import='ALL'
@import='%'

Select * from importdetail where importid like @import

how to replace like condition?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-20 : 04:42:34

declare @import varchar(100)='ALL'
if @import='ALL'
SET @import='%'
exec ('Select * from importdetail where importid like '''+ @import+'''')

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-05-20 : 14:29:24

[code]
select * from importdetail where importid like case when @import='ALL' then '%' else @import end
[/code]

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-05-20 : 16:22:07

Does tuning here mean Gain in Performance? Or it means to write the code in less no of lines ?

Cheers
MIK

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2011-05-20 : 16:44:41

hi like,case will degrade the performance..

any other way like colasce or isnull function

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-20 : 16:49:49

Ummm, CASE is not going to degrade performance, at least no more than ISNULL or COALESCE. What's degrading performance is the LIKE clause and possible lack of indexes. By the way, is ImportID a varchar column? If not, then LIKE is useless for it and should not be used.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-05-20 : 17:35:06

If "All" means to pull all rows of the table then where clause is of no use

If @import='All'
select * from importdetail

Also it would be better if you replace the * with the required columns.

Cheers
MIK

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2011-05-21 : 10:06:25

By it's nature a - '%suffix' will scan rather than seek.An different approach is to store another column with the REVERSE() of the existing column, then querying with the REVERSE() of the search string, 'xiffus%'. This could be achieved with an indexed view, saving the space in the base table. Query string manipulation is required

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources