Hi Pete!

Unfortunately, I don't think you're on the right track - I don't see
how this query would benefit from being put in a stored procedure.
What I do see is problems in the query itself - you use a left join,
and then you put something from the right side in the where clause.
Hence, advice #1 would be to drop LEFT OUTER (that gives the optimizer
a greater choice). Advice #2 may or may not be appropriate. Excepting
the EXISTS, all your criteria use CONTAINING. CONTAINING cannot use an
index, hence your plan will contain at least one NATURAL. Try to see
if some of the CONTAININGs can be replaced by STARTING. Advice #3 is
to take a look at the plan for the subselect - that one should
probably only contain the indexes for C2.CONTACT_ID and CC.CONTACT_ID.
If any other indexes are used, they will probably increase the time
your query takes to execute (assuming CONTACT_ID is pretty selective).
Advice #4 is to take this question to firebird-support, it is
off-topic for this list.

HTH,
Set

--- In IBObjects@yahoogroups.com, "petesouthwest" wrote:
> Hi
>
> I am in the process of trying to re-create our old contact
> management database that was in MS Access using firebird and delphi.
>
> I have one query (below) that is used to find contacts that havent
> been sent a mailshot for particular products (shown below). It
> returns a few rows within a second, but when using this as the basis
> of a report takes several seconds to complete (ie producing under
> 200 letters)
>
> I have not used stored procedures before, (still learning about
> client server dbs!) but I was wondering whether I would get any
> benifits (especially performance), and whether it was possible, to
> use a selectable stored procedure. Helen's book seems to suggest
> there may be a performance benifit (p617) but before I spend a lot
> of time working out how they work I would value some opinions on
> whether I'm on the right track?
>
> Presumably I would then use a IB_StoredProc to comunicate my
> parameters and get the results to and from my frontend?
>
> Thanks
> Pete
>
>
> The Query:
> SELECT * from tblcontact c1
> join tblorganisation o on o.organisationid = c1.organisationid
> left outer join tblcontact_exam ce on c1.contactid = ce.contactid
> join tblorganisationtype ot on o.organisationtypeid =
> ot.organisationtypeid
> join tblexam e on ce.examid = e.examid
> where
> (not exists (select 1 from tblcontact c2
> join tblcontact_correspondence cc on c2.contactid=
> cc.contactid
> where c1.contactid = c2.contactid
> and cc.correspondenceid = :corrid
> and cc.correspondencedate>:beforedate ))
> and e.subject containing :subject
> and e.examlevel containing :exam
> and e.examboard containing :examboard
> and e.specno containing :specno
> and ot.organisationtype containing :orgtype