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;
Mex
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,
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...
>
|||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...
>
|||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...
>

QUery

For retreiving a particular result I can write a query in this way-
Select t1.person_id,t2.age from table1 t1,table2 t2
where t1.person_id=t2.person_id

Or
I can write the same query in a different manner like this-
Select t1.person_id,t2.age from table1 t1
inner join table2 t2
on t1.person_id=t2.person_id

I want to know is there any particular effect between these two processes?Which one is supposed to follow?

SubhasishThere is no difference in effects for the both, but the 'inner join' statement complies with the latest ANSI standard and the other one is the old way.|||The second version is the ANSI way of writing a join. It's also far easier to understand IMHO.

To make that query even simpler to follow, you could use a USING clause:

SELECT
t1.person_id
, t2.age
FROM
table1 t1
INNER JOIN
table2 t2
USING ( person_id )

Have a look at this ANSI join article at Database Journal (http://www.databasejournal.com/features/oracle/article.php/2209301).

Hope this helps,

Matt. :)|||subhasishray,

the reason it make no difference for a simple query such as yours is because MSSQL Server will change WHERE logic to JOIN logic prior to processing whenever possible. For complex queries the optimizer may not be able to do this, and the WHERE logic can affect performance.

use JOIN logic whenever you can. it logically separates table linking from query criteria and filters.

blindman

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...
>|||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...
>|||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...
>