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


Has to Be a Better Way


Has to Be a Better Way

Author
Message
AndrewSQLDBA
AndrewSQLDBA
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1766 Visits: 3427
Good Morning Everyone
I am performing a code review before sending any thing into QA and on up. I ran across this bunch of crap written by a DB2 programmer, trying to write SQL Server code. He does not understand SLQ Server at all.

I am thinking there has to be a much better way to write this mess:


WHERE
(<ColumnName> LIKE '%abc%')
OR
(<ColumnName> LIKE '%def%')
OR
(<ColumnName> LIKE '%ghi%')
OR
(<ColumnName> LIKE '%jkl%')
OR
(<ColumnName> LIKE '%mno%')
OR
(<ColumnName> LIKE '%pqr%')
OR
(<ColumnName> LIKE '%stu%')
OR
(<ColumnName> LIKE '%vw%')
OR
(<ColumnName> LIKE '%xy%')
OR
(<ColumnName> LIKE '%z%')



[color=#FF0000]I am not looking for a "column" named like %%[/color]

It is simple syntax example using the <ColumnName> as a place holder for a ColumnName, what ever it may be. The name of the column is irrelevant

I am seeing this in a couple queries this person has written.

I would like to get suggestions or advice on how I can re-write this to be so much better performing. Not having to seek the entire table all those times.

Thank you in advance for all your help, suggestions and advice.

Andrew SQLDBA
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10078 Visits: 6324
Full text indexing would be an option using the contains clause


SELECT ..... FROM SomeTable
WHERE CONTAINS(ColumnName, 'abc or def or ghi ....... ')



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Greg Snidow
Greg Snidow
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2121 Visits: 2486
AndrewSQLDBA
I ran across this bunch of crap written by a DB2 programmer, trying to write SQL Server code. He does not understand SLQ Server at all


Andrew, I often have to deal with DB2 stuff that seems less efficient than what I would expect from a good SQL programmer. However, DB2 and SQL Server are two different birds. The best DB2 access path may be the worst SQL Server execution plan. My advice, and this is coming from experience in working with mainframers, is to sit down with the guy who wrote that code, and determine why he wrote it that way. It could be it was best for DB2, and he may not realize there is a difference. You should compare the hows and whys of each platform, and make sure he understands the difference. He very well could turn out to be a great SQL Server/DB2 person, and that is a valuable skill set. That's just my two cents.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87107 Visits: 45267
AndrewSQLDBA (1/11/2013)
I would like to get suggestions or advice on how I can re-write this to be so much better performing. Not having to seek the entire table all those times.


That will execute as a single table scan. None of those are SARGable, so no seeks possible.

What's this supposed to do?

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


AndrewSQLDBA
AndrewSQLDBA
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1766 Visits: 3427
HI Gail
This is just in there WHERE clause, just as shown in the example. Since no list tables are being used, all this data in this one column is coming in from a user entered front-end. It is like asking users to type in the state name where they list. Most will get the spelling correct. Some will not.

Andrew SQLDBA
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5236 Visits: 4076
anthony.green (1/11/2013)
Full text indexing would be an option using the contains clause


SELECT ..... FROM SomeTable
WHERE CONTAINS(ColumnName, 'abc or def or ghi ....... ')
+ 1

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
ACinAZ
ACinAZ
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 957
AndrewSQLDBA (1/11/2013)
It is like asking users to type in the state name where they list. Most will get the spelling correct. Some will not.


And there's no way to limit what they can type in? Maybe change the text box to a combo to select a limited (validated) set of values? I'd rather have someone select 'KS' rather than typing in 'Kan'. Or 'OptA' rather than 'I think I have option A'...
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6668 Visits: 7394
ACinKC (1/15/2013)
AndrewSQLDBA (1/11/2013)
It is like asking users to type in the state name where they list. Most will get the spelling correct. Some will not.


And there's no way to limit what they can type in? Maybe change the text box to a combo to select a limited (validated) set of values? I'd rather have someone select 'KS' rather than typing in 'Kan'. Or 'OptA' rather than 'I think I have option A'...
Yes, this is the way to go: relatively simple change in the front-end. Create a lookup table in SQL, validate from the front-end what was entered, fetch the value from the lookup table.

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
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