this query takes a couple seconds with dicentries containing some
100000 entries!

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> At 07:32 PM 17/05/2006, you wrote:
> >hello
> >i have a very rudimentary knowledge of sql as it's only one part of
> >the project im in right now and in order to find records in my
> >database i have my delphi application generate sometimes very awkward
> >queries and like for example
> >
> >select * from (select * from (select * from (select * from (select *
> >from dicentries where id in (select id_dicentry from
> >dskrptlink_dicentries where id_dskrpt = (select id from dskrpts where
> >asdskrpt = 'ios'))) where id in (select id_dicentry from
> >dskrptlink_dicentries where id_dskrpt = (select id from dskrpts where
> >asdskrpt = 'iop'))) where id in (select id_dicentry from
> >dskrptlink_dicentries where id_dskrpt = (select id from dskrpts where
> >asdskrpt = 'ois'))) where id < 2067 order by id descending rows 20)
> >order by id ascending
> >
> >(for some clarity: dskrptlink_dicentries.id_dicentry = foreign key on
> >dicentries.id; dskrptlink_dicentries.id_dskrpt = foreign key on
> >dskrpts.id => dskrptlink_dicentries is table establishing a
> >many-to-many link between tables dicentries and dskrpts...so this
> >query would ask for the last 20 of all those records in dicentries
> >that are linked via dskrptlink_dicentries to ALL three records in
> >dskrpts where dskrpts.asdskrpt = 'ois', asdskrpt = 'iop', asdskrpt =
> >'ios' and then order them ascendingly)
> >
> >i'm pretty sure if i had better knowledge of sql there would be an
> >easier way to express the same thing but my question is: is it worth
> >it? will it make my searches faster or can i assume that firebird
> >always looks for the fastest way possible?
>
> Your question comes into the category "How long is a piece of
> string?" If this query actually runs at all (doubtful) it will
> probably take a week or two to complete.
>
> Is it worth understanding the SQL language to use Firebird? Yes.
> (understatement!!).
>
> I think I might add your posting to my forthcoming book, "Essential
> SQL for Skateboard Riders". :-)
>
> Get your best SQL textbook and look up the keyword JOIN. If you
> don't own an SQL textbook and you have a birthday coming soon, put it
> at the top of your wish-list. If you attend an IT school, visit the
library.
>
> ./heLen
>