Subject Selectable stored procedures?
Author petesouthwest

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?


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 =
join tblexam e on ce.examid = e.examid
(not exists (select 1 from tblcontact c2
join tblcontact_correspondence cc on c2.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