Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Has to Be a Better Way Expand / Collapse
Author
Message
Posted Friday, January 11, 2013 8:26 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:22 PM
Points: 958, Visits: 3,257
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
Post #1406074
Posted Friday, January 11, 2013 8:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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
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

Post #1406078
Posted Friday, January 11, 2013 8:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 2:39 PM
Points: 1,564, Visits: 2,366
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.
Post #1406089
Posted Friday, January 11, 2013 9:26 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
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 2008, MVP
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

Post #1406134
Posted Friday, January 11, 2013 11:25 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:22 PM
Points: 958, Visits: 3,257
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
Post #1406207
Posted Monday, January 14, 2013 2:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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
Post #1406608
Posted Tuesday, January 15, 2013 9:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 3:27 PM
Points: 115, 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'...
Post #1407338
Posted Tuesday, January 15, 2013 6:58 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 3,731, Visits: 7,069
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"
Post #1407542
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse