![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
![]() |
Thu, Jul 24 2008 9:52 AM | Permanent Link |
Pierre du Plessis | 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 |
Thu, Jul 24 2008 10:04 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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] |
Thu, Jul 24 2008 10:06 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Thu, Jul 24 2008 10:09 AM | Permanent Link |
Pierre du Plessis | > 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! |
Thu, Jul 24 2008 10:18 AM | Permanent Link |
Pierre du Plessis | > 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... |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |