Hi there,

I have this query:

SELECT P.*, VC.COLOR as COLOR, VS.STRAIN as STRAIN, VST.STATUS as STATUS,
IFNULL(S.BAND, 'Unknown', S.BAND) AS SIREID,
IFNULL(D.BAND, 'Unknown', D.BAND) AS DAMID
FROM Pigeon P, validcolor VC, validstrain VS, validstatus VST
LEFT JOIN Pigeon D on P.DAM_ID=D.ID
LEFT JOIN Pigeon S on P.SIRE_ID=S.ID
WHERE P.COLOR_ID=VC.ID and P.STRAIN_ID=VS.ID and P.STATUS_ID=VST.ID and SELECTED
ORDER BY P.VRing, P.BAND

It runs fine with not a lot of data, however, having 500 records in Pigeon, takes 4 to 5
minutes to complete.  All link fields are indexed.  Note that the Pigeon Table is joined
to itself twice.

Question: Is there anyway to make this run much faster?

Many thanks,
Pierre

Pierre


My first guess would be to move as much as possible of the WHERE clause to JOINs.

Can you post the execution plan for the query please?

Roy Lambert [Team Elevate]

Pierre,

<< It runs fine with not a lot of data, however, having 500 records in
Pigeon, takes 4 to 5 minutes to complete.  All link fields are indexed.
Note that the Pigeon Table is joined to itself twice.

Question: Is there anyway to make this run much faster? >>

This is covered in the DBISAM migration section of the manual:

http://www.elevatesoft.com/manual?action=mantopic&id=edb2&product=d&version=7&category=2&topic=21

"ElevateDB does not optimize join expressions in the WHERE clause, otherwise
known as SQL-89 style joins. You must use the JOIN clause in order to have
ElevateDB optimize the joins."

If you change the WHERE clause joins that you have to actual JOIN
expressions in the FROM clause, it will be much faster.

--
Tim Young
Elevate Software
www.elevatesoft.com

> If you change the WHERE clause joins that you have to actual JOIN
> expressions in the FROM clause, it will be much faster.

I'll give it a go, thanks Tim!

> If you change the WHERE clause joins that you have to actual JOIN
> expressions in the FROM clause, it will be much faster.

Wow, from 5 mins to a fraction of a second!  Thanks again...