|
Posted by Ed Murphy on 11/23/23 11:59
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')
Navigation:
[Reply to this message]
|