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.SomeColumnName = 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,987987,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.
lqlaurenquantrell wrote:

Quote:

Originally Posted by

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.SomeColumnName = 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,987987,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')|||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:

Quote:

Originally Posted by

laurenquantrell wrote:
>

Quote:

Originally Posted by

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.SomeColumnName = 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,987987,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')

|||I need a way to improve performace of the query with the existing table

Quote:

Originally Posted by

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

"laurenquantrell" <laurenquantrell@.hotmail.comwrote in message
news:1159628053.900888.278690@.m7g2000cwm.googlegro ups.com...

Quote:

Originally Posted by

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:

Quote:

Originally Posted by

>laurenquantrell wrote:
>>

Quote:

Originally Posted by

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.SomeColumnName = 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,987987,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')

>

|||Dan,
Thanks.

Dan Guzman wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

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
>
"laurenquantrell" <laurenquantrell@.hotmail.comwrote in message
news:1159628053.900888.278690@.m7g2000cwm.googlegro ups.com...

Quote:

Originally Posted by

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:

Quote:

Originally Posted by

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.SomeColumnName = 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,987987,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')