Hi

Thanks for the info it will save me spending time finding out how they
work!

I appreciate the advice regarding the SQL, as I'm very much a
'learner' in that area. I'm not sure about the 'theory' but in
practice most things I tried didnt return the records I needed or
expected. The query was very much built from finding out what returned
the records i needed/expected rather than developed from good
understanding of SQL!

However:
<snip>

> 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).

Unfortunatly I dont think I can drop the LEFT OUTER as not all the
records I need the query to return, will have corresponding records in
the tblContact_Exam, I found that without the LEFT OUTER these
records were not returned. I hadnt thought about the fact that I was
using the right hand table in the WHERE clause.

The parameters are set as the result of combobox changes on my
frontend and depending on the contacts we want, sometimes they are all
empty strings. That acted like a wildcard in the 'Containing' line, I
had been assuming that this would return all records even those
without records in tblContact_exam? But now I'm having doubts. Maybe I
would be better to only add the respective lines of SQL if the
corresponding combo is chosen? Cant see an easy way of doing that
though without it getting messy as there are 6 combo's and the user
could require any or all of them.


<snip>
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.

This seems ok, but presumably I would need to make the fields indexes
for there to be a benifit?

<snip>
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).

Sorry, you have lost me on this one :( Whats a plan? I'n not sure I
understand what you mean about containing other indexes?


<snip>

> Advice #4 is to take this question to firebird-support, it is
> off-topic for this list.
>

Maybe, except its not just the SQL that I think I need advice about!
Ideally I guess I should have two posts, one about improving the SQL
(that I thought was fine :( ) and then one about how to implement that
improved SQL using IBO.

Thanks again for the help, its very appreciated

Pete

> 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