Hi Kevin!
>I've got a query that I threw together to serve as a search in a web app.
>My first cut of it used "containing" operators for the user's search terms and
>was
>taking 15-20 seconds to run, which didn't surprise me. I figured I would have
>to do
>something smarter than "containing". As I began to play around with
>optimization, I
>came to find that the "containing" operators weren't my problem. My problem
>was my
>"distinct" and my joins.
>
>I prowled through the mailing list last Friday looking for leads, tried a lot
>of
>things, but never could get just the joins, without search criteria, running
>in much
>under about 14 seconds.
>
>select distinct s.StudentSeq, s.LastName, s.FirstName,
> s.BirthDate, s.MiddleName, p.StudentID
>from student s
>join PrfStuID p on s.StudentSeq=p.StudentSeq
>join AliasStu a on s.StudentSeq=a.StudentSeq
>join SchlHist h on s.StudentSeq=h.StudentSeq
>join Facility f on h.FacilityID=f.FacilityID
>join District d on f.DistrictCode=d.DistrictCode
>
>This database is old and hoary. I'll include the DDL, at least for the
>important
>fields and indexes. There are no primary keys on any tables, although every
>table has
>a single unique index. The plan looks like this:
>
>PLAN SORT (JOIN (F NATURAL, D INDEX (DISTRICT_), H INDEX
>(SCHLHIST_FACILITYID),
>S INDEX (STUDENT_), P INDEX (PRFSTUID_), A INDEX (ALIASSTU_STUDENTSEQINDEX)))
The first impression is that this is a fair query with a decent plan, or at
least as decent as it gets without any WHERE clause. However, many web apps
desire to show only a fraction of the records on screen, and that can be done
without distinct (so that you do not have to select all records):
select s.StudentSeq, s.LastName, s.FirstName, s.BirthDate, s.MiddleName,
p.StudentID
from student s
join PrfStuID p on s.StudentSeq=p.StudentSeq
where exists(select * from AliasStu a
join SchlHist h on a.StudentSeq=h.StudentSeq
join Facility f on h.FacilityID=f.FacilityID
join District d on f.DistrictCode=d.DistrictCode
where s.StudentSeq=a.StudentSeq)
ROWS 1 TO 20
(you may want to add ORDER BY and possibly use FIRST rather than ROWS if you
use an old Firebird version, of course 1 TO 20 was randomly chosen, use
whatever fits your need).
However, if you want all rows returned, then I don't think my suggestion will
help you anything.
HTH,
Set
