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

Re-order table after many inserts

I have a stored proc that is long and performs many separate intserts into my table. Is there a way that I can reorder the records themselves physically in the table after the inserts are done?

Hello,

http://weblogs.sqlteam.com/tarad/archive/2004/07/30/1834.aspx

I think this will help you!

Happy coding...

|||

Hi,

I don′t know if the article below is applying to your case, but this can be only done if you have a clustered key based on the tables, which is defining the physical o′rder of a key, and therefore the physical order of the table (based on x columns). But if you only want to sort the tables via a specific column, then you have to look a bit deeper in the specifications of a table. Its a unordered set of data, unless you don′t have a clustered sorted index on a it, you will only get the orderd data with querying the data with the ORDER clause.

HTH, Jens Suessmeyer.