|
|
Posted by laurenquantrell on 10/02/06 01:08
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
>
> "laurenquantrell" <laurenquantrell@hotmail.com> wrote in message
> news:1159628053.900888.278690@m7g2000cwm.googlegroups.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.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]
|