In simple terms the first rule with SQL is first do optimisation based on
your WHERE clauses as this is supposed to reduce the number of records to
use in the join.
Then of the records left over from the WHERE - do the join.
In this case you could change the optimisation path by specifying the PLAN
for the select to use.
You can also ask IB to show the optimisation plan it is using in a select.
(SET PLAN)
Otherwise if you look at the IB installation of documentation, there is a
helpfile on how IB does it's Optimisation.
Myles.
> -----Original Message-----
> From: Neven MacEwan [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, 16 December 1999 09:49
> To: Multiple recipients of list delphi
> Subject: [DUG]: Interbase execution plans
>
> Hiya All
>
> I went to a propective client recently and this is what i found
>
> A chance to exercise your indepth knowledge of IB
>
> for a a statement like
>
> Select * from Area a join Job b on a.AreaID = b.AreaID
> where b.Date between 1/9/99 and 30/9/99
>
> Now both AreaID and Date are indexed on Date and Area (single field non
> unique indices)
>
> The execution plan shown that for b it is using both indicies (the area
> and
> date)
>
> Now..how does IB uses these indexes? It needs the AreaID for the join but
> what benefit does it get from the Date
> index?
>
> e.g if there are 100 areas and 100 month ranges in a table of 10,000
> evenly
> distibuted records the Area fk restricts that to 100 records, the date
> range would also restrict it to 100 record the union of these two sets is
> 1
> record
>
> Would it not be more efficient to linearly search the 100 area selected
> records for a date in the date range or does IB have some magical method
> of
> determining the union of 2 sets (how does it do this?). I assume that IB
> has applied a general method but in this case the general method is
> inefficient since it would have to fetch 200 records (100 in each set) to
> find the union (1 record)
>
> The obvious 'tuning' this DB requires is a AreaID/Date index (I left the
> current IT Consultant with this thought)
> I may be wrong but in the above case this should lead to a 200 fold
> increase
> in performance.
>
> Which leads me to an interesting point
>
> Their whole IB/Delphi system was based around cached updates on the
> results
> from a stored proc because a 5 table simple join query took 10-20 seconds
> to return a result set of 400 records (the statments were so simple they
> should have been virtually instant) and
> vitually none of their indices had more that a single column (unless it
> was
> a pk)
>
> The point is that the abstraction that an SQL server offers can be a two
> edged sword since it will work in most circumstances but it hides you from
> efficiency details that severly effect performance - someone coding the
> above in a ISAM would have seen the need for an
> AreaID/Date index immediately
>
> This whole error had lead the 'IT Consultant ' to the conclusion that
> normalisation was evil and inefficient in DB Design
>
> I know its not Friday but we haven't had a good discussion in ages (bar
> Nic
> W's diatribes on SQL server)
>
> Regards
> Neven
>
>
>
> --------------------------------------------------------------------------
> -
> New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
