|
|
Posted by laurenquantrell on 09/30/06 14:54
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.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]
|