query

Hi
a) I Have table(tmarcs) with fields
id, marc_id,ind1, indeks, teks
Lets say i have values in this table
1,245,4,'The Sun', 'is shining'
1,245,0,'Sun',''
b) on ind1 field is length of article(prefix)
c) user wants to search books by title and enters only Sun
Query must return both titles, beacause The_ must be ignored on search
My query looks something like this
select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
'Sun'
but this query is veeeeery slow.(of course query is more complex but when to
remove this "replicate", query is very fast)
Any ideas how to build better query ;)?
Regards;
MexHi
I'm not sure that you gain here performance try how does it work?
create table #t
(
c varchar(50)
)
insert into #t values ('The Sun')
insert into #t values ('The_ Sun')
insert into #t values ('The_Sun')
insert into #t values ('The_Sun_')
insert into #t values ('The Moon')
declare @.d varchar(50)
set @.d='Sun'
select * from #t where c not LIKE '%[_]%' and c like '%'+@.d+'%'
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
> Hi
> a) I Have table(tmarcs) with fields
> id, marc_id,ind1, indeks, teks
>
> Lets say i have values in this table
> 1,245,4,'The Sun', 'is shining'
> 1,245,0,'Sun',''
> b) on ind1 field is length of article(prefix)
> c) user wants to search books by title and enters only Sun
> Query must return both titles, beacause The_ must be ignored on search
> My query looks something like this
> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
> 'Sun'
> but this query is veeeeery slow.(of course query is more complex but when
> to remove this "replicate", query is very fast)
> Any ideas how to build better query ;)?
>
> Regards;
> Mex
>|||Meelis,
Your prefix is precooking how big a part of the title to strip off, but what
if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or 4 for
"Friend"?
For the data as you show it, you could use
... WHERE indeks IS LIKE '%Sun%'
However, just as your replicate code will not use an index on indeks, this
still will not use an index since the prefix is wild.
This need might be better met by fulltext indexing of the indeks column.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
> Hi
> a) I Have table(tmarcs) with fields
> id, marc_id,ind1, indeks, teks
>
> Lets say i have values in this table
> 1,245,4,'The Sun', 'is shining'
> 1,245,0,'Sun',''
> b) on ind1 field is length of article(prefix)
> c) user wants to search books by title and enters only Sun
> Query must return both titles, beacause The_ must be ignored on search
> My query looks something like this
> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
> 'Sun'
> but this query is veeeeery slow.(of course query is more complex but when
> to remove this "replicate", query is very fast)
> Any ideas how to build better query ;)?
>
> Regards;
> Mex
>|||The ind1 is ment for aritcles in the bginning of title.
for english The, German Der,Das,Die aso.(length from 0 to 9)
The Sun (in english)
Die Sonne(in german)
in both cases ind1=4
if user enters for search Sonne, query must return also Die Sonne
because ind1=4 and first 4 chars are ignored
Mex
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23uJFd%23WZHHA.4368@.TK2MSFTNGP06.phx.gbl...
> Meelis,
> Your prefix is precooking how big a part of the title to strip off, but
> what if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or
> 4 for "Friend"?
> For the data as you show it, you could use
> ... WHERE indeks IS LIKE '%Sun%'
> However, just as your replicate code will not use an index on indeks, this
> still will not use an index since the prefix is wild.
> This need might be better met by fulltext indexing of the indeks column.
> RLF
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> a) I Have table(tmarcs) with fields
>> id, marc_id,ind1, indeks, teks
>>
>> Lets say i have values in this table
>> 1,245,4,'The Sun', 'is shining'
>> 1,245,0,'Sun',''
>> b) on ind1 field is length of article(prefix)
>> c) user wants to search books by title and enters only Sun
>> Query must return both titles, beacause The_ must be ignored on search
>> My query looks something like this
>> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
>> 'Sun'
>> but this query is veeeeery slow.(of course query is more complex but when
>> to remove this "replicate", query is very fast)
>> Any ideas how to build better query ;)?
>>
>> Regards;
>> Mex
>>
>|||its much slower, because a '%'+@.d+'%'
Ill show you my original query :)
select top 500 t.id, 0, t.indeks, t.yhik_id, t.pealkiri, t.autor, t.aasta,
t.muud ,(select id from kohaviidad where teavik_id=t.id and kogu_id=37) ,
count(distinct e.id) from (TEAVIKUD t inner join eksemplarid e on
(e.teavik_id=t.id and e.asukoht_id not in(10,11) )) inner join (select
teavik_id from tmarcid where (marc_id in (210,222,240,242,245,246,740)) and
indeks like N'' + (select replicate('_',char(ind2))) +N'tuba number 10%')
tm1 on tm1.teavik_id=t.id where kustut=0 group by t.id, t.indeks,
t.yhik_id, t.pealkiri, t.autor, t.aasta, t.muud order by 5,6,7,8
Mex
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23W7U85WZHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Hi
> I'm not sure that you gain here performance try how does it work?
>
> create table #t
> (
> c varchar(50)
> )
> insert into #t values ('The Sun')
> insert into #t values ('The_ Sun')
> insert into #t values ('The_Sun')
> insert into #t values ('The_Sun_')
> insert into #t values ('The Moon')
>
> declare @.d varchar(50)
> set @.d='Sun'
> select * from #t where c not LIKE '%[_]%' and c like '%'+@.d+'%'
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> a) I Have table(tmarcs) with fields
>> id, marc_id,ind1, indeks, teks
>>
>> Lets say i have values in this table
>> 1,245,4,'The Sun', 'is shining'
>> 1,245,0,'Sun',''
>> b) on ind1 field is length of article(prefix)
>> c) user wants to search books by title and enters only Sun
>> Query must return both titles, beacause The_ must be ignored on search
>> My query looks something like this
>> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
>> 'Sun'
>> but this query is veeeeery slow.(of course query is more complex but when
>> to remove this "replicate", query is very fast)
>> Any ideas how to build better query ;)?
>>
>> Regards;
>> Mex
>>
>|||Meelis,
One common approach (in the US) for removing the article from the sort
criteria is to reform the title as:
1,245,4,'Sun, The', 'is shining'
1,245,0,'Sun',''
This means that the overhead is at update time when the code takes "The Sun"
and turns it into "Sun, The". Of course, if you want to display the title
as originally typed, the SELECT will need to turn it back around. In this
case, ind1 would be the length of the suffix.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:OCIcXEXZHHA.348@.TK2MSFTNGP02.phx.gbl...
> The ind1 is ment for aritcles in the bginning of title.
> for english The, German Der,Das,Die aso.(length from 0 to 9)
> The Sun (in english)
> Die Sonne(in german)
> in both cases ind1=4
> if user enters for search Sonne, query must return also Die Sonne
> because ind1=4 and first 4 chars are ignored
> Mex
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23uJFd%23WZHHA.4368@.TK2MSFTNGP06.phx.gbl...
>> Meelis,
>> Your prefix is precooking how big a part of the title to strip off, but
>> what if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or
>> 4 for "Friend"?
>> For the data as you show it, you could use
>> ... WHERE indeks IS LIKE '%Sun%'
>> However, just as your replicate code will not use an index on indeks,
>> this still will not use an index since the prefix is wild.
>> This need might be better met by fulltext indexing of the indeks column.
>> RLF
>> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
>> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> a) I Have table(tmarcs) with fields
>> id, marc_id,ind1, indeks, teks
>>
>> Lets say i have values in this table
>> 1,245,4,'The Sun', 'is shining'
>> 1,245,0,'Sun',''
>> b) on ind1 field is length of article(prefix)
>> c) user wants to search books by title and enters only Sun
>> Query must return both titles, beacause The_ must be ignored on search
>> My query looks something like this
>> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
>> 'Sun'
>> but this query is veeeeery slow.(of course query is more complex but
>> when to remove this "replicate", query is very fast)
>> Any ideas how to build better query ;)?
>>
>> Regards;
>> Mex
>>
>>
>