Post reply

Has to Be a Better Way

  • January 11, 2013 at 8:26 am

    #287713

    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%')

    I am not looking for a "column" named like %%

    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

  • January 11, 2013 at 8:29 am

    #1576342

    Full text indexing would be an option using the contains clause

    SELECT ..... FROM SomeTable

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

  • January 11, 2013 at 8:40 am

    #1576353

    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.

  • January 11, 2013 at 9:26 am

    #1576395

    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

  • January 11, 2013 at 11:25 am

    #1576461

    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

  • January 14, 2013 at 2:34 am

    #1576794

    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;-)

  • January 15, 2013 at 9:37 am

    #1577400

    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'...

  • January 15, 2013 at 6:58 pm

    #1577580

    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; Theyll drag you down to their level and beat you with experience

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply