--- In IBObjects@yahoogroups.com, "petesouthwest" wrote:

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

Well, for the time being...

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

True, those without an item in tblContact_Exam will not be returned.
But they won't be returned in your original SQL either since you
tblexam is in your WHERE clause - a table which you already JOIN to
tblContact_Exam.

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

You have to change your SQL a bit to return records even if you want
to return records which doesn't exist in tblContack_exam:

SELECT * from tblcontact c1
join tblorganisation o on o.organisationid = c1.organisationid
join tblorganisationtype ot
on o.organisationtypeid = ot.organisationtypeid
left join tblcontact_exam ce on c1.contactid = ce.contactid
left join tblexam e on ce.examid = e.examid
and e.subject containing :subject
and e.examlevel containing :exam
and e.examboard containing :examboard
and e.specno containing :specno
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 ot.organisationtype containing :orgtype

But I'm not certain this is what you want, this will also return
records whether or not there exists any corresponding record in
tblexam. If you don't want this, then you'll have to find some other
table than tblcontact_exam to join tblexam to.

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

Well, you could do this in a stored procedure... Or build your SQL
dynamically on the client. I think IBO even have some search
components that may be helpful in your case (though I haven't used
them much).

> <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?

Yes, we may work in two completely different settings. I generally
work with tables with about 1000000 rows, and then indexes are pretty
useful. For small tables, using CONTAINING is fine.

> <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?

A plan is what the optimizer builds when deciding how to find the rows
you request. The plan is generated at prepare time. Most interactive
tools have some way of showing plans, I generally use IB_SQL (freeware
from www.ibobjects.com), but others may be used as well. Simplifying
my advice: Do you have an index on correspondenceid or
correspondencedate? If so, do they contain lots of duplicates in the
tblcontact_correspondence table? If your answer is no, then this is
not an issue in your case. If it is yes, then it may be worth furter
investigation (though maybe not if you only have small tables with a
few thousand records in each).

HTH,
Set

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