Subject Re: does firebird always go fastest way?
Author martinknappe
thanx, the links did make join clearer but now i'm wondering whether
join is indeed the right tool to get my query right (maybe my table
structure is just a bit too extravagant for a beginner; dunno); see,
join as i understand is useful to fetch fields from various tables at
a time and into the same result set, right?
now, actually, i don't want my query i only want to fetch
fields from one table (dicentries) only the selection criterion for
which records to pick makes the query so long

imagine three tables as follows(only search-relevant fields mentioned): (primary key)
dskrptlink_dicentries.id_dicentry (foreign key on field above)
dskrptlink_dicentries.id_dskrpt (foreign key on field below) (primary key)
dskrpts.asdskrpt (search-relevant string)

as this makes clear, a record in dicentries can have various
corresponding records in dskrpts
now, the user of my app would enter one or more such search-relevant
strings(e.g.: 'abc', 'def', and 'ghi') into the mask after which a
query should be generated fetching all those records from dicentries
which are linked VIA table dskrptlink_dicentries to all of the
following three records in dskrpts:

r1: dskrpts.asdskrpt = 'abc'
r2: dskrpts.asdskrpt = 'def'
r3: dskrpts.asdskrpt = 'ghi'

would someone please show me how to formulate this particular example
in a time-efficient query just to put me on the right track?



--- In, "Martijn Tonies"
<m.tonies@...> wrote:
> > ok, i figure the answer to my initial question is then no..i've read
> > about 'join' before but i never got too familiar with it so i just
> > stuck to composing my sql-queries of combinations of "select * from
> > (select ...)"-statements because i thought join was only there for the
> > sake of readability (which i don't really care about) but if it really
> > makes my search faster then it seems i'd indeed better do some reading
> > up :-)
> Well, let me explain a bit more ...
> In the ideal case of a DBMS, you don't have to tell it "how" to get your
> data, but only "what". This is part of the whole idea behind a DBMS.
> Between your application and the physical data (on disk), there's a
> series of layers.
> One of these, is called an "optimizer". What this should do, is turn
> query (whatever it is, JOINs, SELECT FROM ( SELECT FROM )) into
> something that it thinks is the most efficient way of getting your data.
> In theory, this works fine.
> In practice, it's very hard to write an optimizer that's fool proof with
> regard to what uses to get your data AND to make it fast.
> So, in theory, both a well-written JOIN statement and yours should
> be able to return the same data in pretty much the same time.
> In practice, it's more common to write a query statement that's readable
> and as short as possible in order to simply the optimizers job.
> That being said, the SQL language has several constructs to do what
> you want to get as a result. Read up on JOIN and get to understand
> what it does, it's very useful.
> Here's a quick online reference:
> Martijn Tonies
> Database Workbench - development tool for Firebird and more!
> Upscene Productions
> My thoughts:
> Database development questions? Check the forum!