|
|
Posted by Dan Guzman on 10/01/06 13:29
> 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]
|