Replace Multiple LIKEs

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • laurenquantrell
    #1

    Replace Multiple LIKEs

    I have a query below that performs horribly:

    @KeywordOne char(6),
    @KeywordTwo char(6),
    @KeywordThree char(6),
    @KeywordFour char(6),
    @KeywordFive char(6)


    SELECT
    c.Something
    FROM
    dbo.tblStuff c
    WHERE
    c.SomeColumnNam e = 0
    AND (c.Keyword LIKE '%' + @KeywordOne + '%' OR @KeywordOne is Null)
    AND (c.Keyword LIKE '%' + @KeywordTwo + '%' OR @KeywordTwo is Null)
    AND (c.Keyword LIKE '%' + @KeywordThree + '%' OR @KeywordThree is
    Null)
    AND (c.Keyword LIKE '%' + @KeywordFour + '%' OR @KeywordFour = is
    Null)
    AND (c.Keyword LIKE '%' + @KeywordFive + '%' OR @KeywordFive = is
    Null)

    The contents of column c.Keyword looks like this:
    Row1: 123456,321654,9 87987,345987
    Row2:
    Row3: 123456,987987
    etc.

    What can I do to get this to perform reasonably? I cannot use full-text
    search.
    Any help is appreciated.
    lq

    Tags: None
    • Ed Murphy
      #2
      Re: Replace Multiple LIKEs

      laurenquantrell wrote:
      I have a query below that performs horribly:
      >
      @KeywordOne char(6),
      @KeywordTwo char(6),
      @KeywordThree char(6),
      @KeywordFour char(6),
      @KeywordFive char(6)
      >
      >
      SELECT
      c.Something
      FROM
      dbo.tblStuff c
      WHERE
      c.SomeColumnNam e = 0
      AND (c.Keyword LIKE '%' + @KeywordOne + '%' OR @KeywordOne is Null)
      AND (c.Keyword LIKE '%' + @KeywordTwo + '%' OR @KeywordTwo is Null)
      AND (c.Keyword LIKE '%' + @KeywordThree + '%' OR @KeywordThree is
      Null)
      AND (c.Keyword LIKE '%' + @KeywordFour + '%' OR @KeywordFour = is
      Null)
      AND (c.Keyword LIKE '%' + @KeywordFive + '%' OR @KeywordFive = is
      Null)
      >
      The contents of column c.Keyword looks like this:
      Row1: 123456,321654,9 87987,345987
      Row2:
      Row3: 123456,987987
      etc.
      >
      What can I do to get this to perform reasonably? I cannot use full-text
      search.
      Normalize tblStuff by removing column Keyword and replacing it with a
      second table tblStuffKeyword . For instance, instead of

      create table tblStuff (
      StuffKey int,
      StuffField1 varchar(50),
      StuffField2 varchar(50),
      Keyword text
      )

      insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
      values (1, 'A', 'B', '123456,321654, 987987,345987')
      insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
      values (2, 'C', 'D', '')
      insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
      values (3, 'E', 'F', '123456,987987' )

      do this:

      create table tblStuff (
      StuffKey int,
      StuffField1 varchar(50),
      StuffField2 varchar(50)
      )

      create table tblStuffKeyword (
      StuffKey int,
      Keyword varchar(50)
      )

      insert into tblStuff (StuffKey, StuffField1, StuffField2)
      values (1, 'A', 'B')
      insert into tblStuff (StuffKey, StuffField1, StuffField2)
      values (2, 'C', 'D')
      insert into tblStuff (StuffKey, StuffField1, StuffField2)
      values (3, 'E', 'F')

      insert into tblStuffKeyword (StuffKey, Keyword)
      values (1, '123456')
      insert into tblStuffKeyword (StuffKey, Keyword)
      values (1, '321654')
      insert into tblStuffKeyword (StuffKey, Keyword)
      values (1, '987987')
      insert into tblStuffKeyword (StuffKey, Keyword)
      values (1, '345987')
      insert into tblStuffKeyword (StuffKey, Keyword)
      values (3, '123456')
      insert into tblStuffKeyword (StuffKey, Keyword)
      values (3, '987987')

        Comment

        • laurenquantrell
          #3
          Re: Replace Multiple LIKEs

          Ed,
          Thanks. However, rebuilding the database architecture is currently not
          an option.
          I need a way to improve performace of the query with the existing table
          structure.
          lq


          Ed Murphy wrote:
          laurenquantrell wrote:
          >
          I have a query below that performs horribly:

          @KeywordOne char(6),
          @KeywordTwo char(6),
          @KeywordThree char(6),
          @KeywordFour char(6),
          @KeywordFive char(6)


          SELECT
          c.Something
          FROM
          dbo.tblStuff c
          WHERE
          c.SomeColumnNam e = 0
          AND (c.Keyword LIKE '%' + @KeywordOne + '%' OR @KeywordOne is Null)
          AND (c.Keyword LIKE '%' + @KeywordTwo + '%' OR @KeywordTwo is Null)
          AND (c.Keyword LIKE '%' + @KeywordThree + '%' OR @KeywordThree is
          Null)
          AND (c.Keyword LIKE '%' + @KeywordFour + '%' OR @KeywordFour = is
          Null)
          AND (c.Keyword LIKE '%' + @KeywordFive + '%' OR @KeywordFive = is
          Null)

          The contents of column c.Keyword looks like this:
          Row1: 123456,321654,9 87987,345987
          Row2:
          Row3: 123456,987987
          etc.

          What can I do to get this to perform reasonably? I cannot use full-text
          search.
          >
          Normalize tblStuff by removing column Keyword and replacing it with a
          second table tblStuffKeyword . For instance, instead of
          >
          create table tblStuff (
          StuffKey int,
          StuffField1 varchar(50),
          StuffField2 varchar(50),
          Keyword text
          )
          >
          insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
          values (1, 'A', 'B', '123456,321654, 987987,345987')
          insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
          values (2, 'C', 'D', '')
          insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
          values (3, 'E', 'F', '123456,987987' )
          >
          do this:
          >
          create table tblStuff (
          StuffKey int,
          StuffField1 varchar(50),
          StuffField2 varchar(50)
          )
          >
          create table tblStuffKeyword (
          StuffKey int,
          Keyword varchar(50)
          )
          >
          insert into tblStuff (StuffKey, StuffField1, StuffField2)
          values (1, 'A', 'B')
          insert into tblStuff (StuffKey, StuffField1, StuffField2)
          values (2, 'C', 'D')
          insert into tblStuff (StuffKey, StuffField1, StuffField2)
          values (3, 'E', 'F')
          >
          insert into tblStuffKeyword (StuffKey, Keyword)
          values (1, '123456')
          insert into tblStuffKeyword (StuffKey, Keyword)
          values (1, '321654')
          insert into tblStuffKeyword (StuffKey, Keyword)
          values (1, '987987')
          insert into tblStuffKeyword (StuffKey, Keyword)
          values (1, '345987')
          insert into tblStuffKeyword (StuffKey, Keyword)
          values (3, '123456')
          insert into tblStuffKeyword (StuffKey, Keyword)
          values (3, '987987')

            Comment

            • Dan Guzman
              #4
              Re: Replace Multiple LIKEs

              I need a way to improve performace of the query with the existing table
              structure.
              There isn't much you can do because of the leading '%' in the LIKE
              expressions. The only approach I can think of is to add a covering index on
              the SomeColumnName, Keyword and Something columns. At least this will limit
              the scan to the rows matching the SomeColumnName value specified.

              Consider this a lesson on one of the many reasons why one shouldn't store a
              delimited list in a relational table column.

              --
              Hope this helps.

              Dan Guzman
              SQL Server MVP

              "laurenquantrel l" <laurenquantrel l@hotmail.comwr ote in message
              news:1159628053 .900888.278690@ m7g2000cwm.goog legroups.com...
              Ed,
              Thanks. However, rebuilding the database architecture is currently not
              an option.
              I need a way to improve performace of the query with the existing table
              structure.
              lq
              >
              >
              Ed Murphy wrote:
              >laurenquantrel l wrote:
              >>
              I have a query below that performs horribly:
              >
              @KeywordOne char(6),
              @KeywordTwo char(6),
              @KeywordThree char(6),
              @KeywordFour char(6),
              @KeywordFive char(6)
              >
              >
              SELECT
              c.Something
              FROM
              dbo.tblStuff c
              WHERE
              c.SomeColumnNam e = 0
              AND (c.Keyword LIKE '%' + @KeywordOne + '%' OR @KeywordOne is Null)
              AND (c.Keyword LIKE '%' + @KeywordTwo + '%' OR @KeywordTwo is Null)
              AND (c.Keyword LIKE '%' + @KeywordThree + '%' OR @KeywordThree is
              Null)
              AND (c.Keyword LIKE '%' + @KeywordFour + '%' OR @KeywordFour = is
              Null)
              AND (c.Keyword LIKE '%' + @KeywordFive + '%' OR @KeywordFive = is
              Null)
              >
              The contents of column c.Keyword looks like this:
              Row1: 123456,321654,9 87987,345987
              Row2:
              Row3: 123456,987987
              etc.
              >
              What can I do to get this to perform reasonably? I cannot use full-text
              search.
              >>
              >Normalize tblStuff by removing column Keyword and replacing it with a
              >second table tblStuffKeyword . For instance, instead of
              >>
              >create table tblStuff (
              > StuffKey int,
              > StuffField1 varchar(50),
              > StuffField2 varchar(50),
              > Keyword text
              >)
              >>
              >insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
              > values (1, 'A', 'B', '123456,321654, 987987,345987')
              >insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
              > values (2, 'C', 'D', '')
              >insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
              > values (3, 'E', 'F', '123456,987987' )
              >>
              >do this:
              >>
              >create table tblStuff (
              > StuffKey int,
              > StuffField1 varchar(50),
              > StuffField2 varchar(50)
              >)
              >>
              >create table tblStuffKeyword (
              > StuffKey int,
              > Keyword varchar(50)
              >)
              >>
              >insert into tblStuff (StuffKey, StuffField1, StuffField2)
              > values (1, 'A', 'B')
              >insert into tblStuff (StuffKey, StuffField1, StuffField2)
              > values (2, 'C', 'D')
              >insert into tblStuff (StuffKey, StuffField1, StuffField2)
              > values (3, 'E', 'F')
              >>
              >insert into tblStuffKeyword (StuffKey, Keyword)
              > values (1, '123456')
              >insert into tblStuffKeyword (StuffKey, Keyword)
              > values (1, '321654')
              >insert into tblStuffKeyword (StuffKey, Keyword)
              > values (1, '987987')
              >insert into tblStuffKeyword (StuffKey, Keyword)
              > values (1, '345987')
              >insert into tblStuffKeyword (StuffKey, Keyword)
              > values (3, '123456')
              >insert into tblStuffKeyword (StuffKey, Keyword)
              > values (3, '987987')
              >

                Comment

                • laurenquantrell
                  #5
                  Re: Replace Multiple LIKEs

                  Dan,
                  Thanks.

                  Dan Guzman wrote:
                  I need a way to improve performace of the query with the existing table
                  structure.
                  >
                  There isn't much you can do because of the leading '%' in the LIKE
                  expressions. The only approach I can think of is to add a covering index on
                  the SomeColumnName, Keyword and Something columns. At least this will limit
                  the scan to the rows matching the SomeColumnName value specified.
                  >
                  Consider this a lesson on one of the many reasons why one shouldn't store a
                  delimited list in a relational table column.
                  >
                  --
                  Hope this helps.
                  >
                  Dan Guzman
                  SQL Server MVP
                  >
                  "laurenquantrel l" <laurenquantrel l@hotmail.comwr ote in message
                  news:1159628053 .900888.278690@ m7g2000cwm.goog legroups.com...
                  Ed,
                  Thanks. However, rebuilding the database architecture is currently not
                  an option.
                  I need a way to improve performace of the query with the existing table
                  structure.
                  lq


                  Ed Murphy wrote:
                  laurenquantrell wrote:
                  >
                  I have a query below that performs horribly:

                  @KeywordOne char(6),
                  @KeywordTwo char(6),
                  @KeywordThree char(6),
                  @KeywordFour char(6),
                  @KeywordFive char(6)


                  SELECT
                  c.Something
                  FROM
                  dbo.tblStuff c
                  WHERE
                  c.SomeColumnNam e = 0
                  AND (c.Keyword LIKE '%' + @KeywordOne + '%' OR @KeywordOne is Null)
                  AND (c.Keyword LIKE '%' + @KeywordTwo + '%' OR @KeywordTwo is Null)
                  AND (c.Keyword LIKE '%' + @KeywordThree + '%' OR @KeywordThree is
                  Null)
                  AND (c.Keyword LIKE '%' + @KeywordFour + '%' OR @KeywordFour = is
                  Null)
                  AND (c.Keyword LIKE '%' + @KeywordFive + '%' OR @KeywordFive = is
                  Null)

                  The contents of column c.Keyword looks like this:
                  Row1: 123456,321654,9 87987,345987
                  Row2:
                  Row3: 123456,987987
                  etc.

                  What can I do to get this to perform reasonably? I cannot use full-text
                  search.
                  >
                  Normalize tblStuff by removing column Keyword and replacing it with a
                  second table tblStuffKeyword . For instance, instead of
                  >
                  create table tblStuff (
                  StuffKey int,
                  StuffField1 varchar(50),
                  StuffField2 varchar(50),
                  Keyword text
                  )
                  >
                  insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
                  values (1, 'A', 'B', '123456,321654, 987987,345987')
                  insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
                  values (2, 'C', 'D', '')
                  insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
                  values (3, 'E', 'F', '123456,987987' )
                  >
                  do this:
                  >
                  create table tblStuff (
                  StuffKey int,
                  StuffField1 varchar(50),
                  StuffField2 varchar(50)
                  )
                  >
                  create table tblStuffKeyword (
                  StuffKey int,
                  Keyword varchar(50)
                  )
                  >
                  insert into tblStuff (StuffKey, StuffField1, StuffField2)
                  values (1, 'A', 'B')
                  insert into tblStuff (StuffKey, StuffField1, StuffField2)
                  values (2, 'C', 'D')
                  insert into tblStuff (StuffKey, StuffField1, StuffField2)
                  values (3, 'E', 'F')
                  >
                  insert into tblStuffKeyword (StuffKey, Keyword)
                  values (1, '123456')
                  insert into tblStuffKeyword (StuffKey, Keyword)
                  values (1, '321654')
                  insert into tblStuffKeyword (StuffKey, Keyword)
                  values (1, '987987')
                  insert into tblStuffKeyword (StuffKey, Keyword)
                  values (1, '345987')
                  insert into tblStuffKeyword (StuffKey, Keyword)
                  values (3, '123456')
                  insert into tblStuffKeyword (StuffKey, Keyword)
                  values (3, '987987')

                    Comment

                    Previous template Next
                    Working...