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
