UCanAccess / Discussion / Help: Performance issues

Hello everyone,

I got a performance issue when I execute some SQL queries using UCanAccess 3.0.7.
On my 2 projects (a big project and a little project with just the minimum required to execute queries using UCA), most of my queries run correctly. Which means :
- they run well
- they take 17-130 milliseconds to execute.
Here are some example of queries which work :

SELECT * FROM Livrable;

SELECT PlanificationLivraisons.IdLivraison AS PLANIFICATIONLIVRAISONS_ID, ProduitVersion.LibelleProduitVersion AS PRODUITVERSION_LIBELLE, PlanificationLivraisons.TitreDescription AS PLANIFICATIONLIVRAISONS_TITREDESCRIPTION, EtatLivraison.LibelleEtat AS ETATLIVRAISON_LIBELLE, ModeLivraison.ModeLivraison AS MODELIVRAISON_LIBELLE, IIf(IsNull(PlanificationLivraisons.IdClient),null,PlanificationLivraisons.IdClient) AS PLANIFICATIONLIVRAISONS_IDCLIENT, IIf(IsNull(Client.NomClient),'',Client.NomClient) AS CLIENT_NOM,  PlanificationLivraisons.NomsDestinataires AS PLANIFICATIONLIVRAISONS_NOMSDESTINATAIRES, PlanificationLivraisons.ReferencePatch AS PLANIFICATIONLIVRAISONS_REFERENCEPATCH, EtatQualification.LibelleEtat AS ETATQUALIFICATION_LIBELLE, PlanificationLivraisons.DateLivraisonSouhaitée AS PLANIFICATIONLIVRAISONS_DATELIVRAISONSOUHAITEE, PlanificationLivraisons.LivraisonUrgente AS PLANIFICATIONLIVRAISONS_LIVRAISONURGENTE, PlanificationLivraisons.DateLivraisonRéelle AS PLANIFICATIONLIVRAISONS_DATELIVRAISONREELLE, IIf(IsNull(PlanificationLivraisons.NuméroBL),'',PlanificationLivraisons.NuméroBL) AS PLANIFICATIONLIVRAISONS_NUMEROBL, PlanificationLivraisons.DateSignatureBL AS PLANIFICATIONLIVRAISONS_DATESIGNATUREBL, PlanificationLivraisons.Facturation AS PLANIFICATIONLIVRAISONS_FACTURATION, PlanificationLivraisons.AutresInfos AS PLANIFICATIONLIVRAISONS_AUTRESINFOS FROM (((((PlanificationLivraisons LEFT JOIN ProduitVersion ON PlanificationLivraisons.IdProduitVersion = ProduitVersion.IdProduitVersion) LEFT JOIN ModeLivraison ON PlanificationLivraisons.IdModeLivraison = ModeLivraison.IdModeLivraison) LEFT JOIN Client ON PlanificationLivraisons.IdClient = Client.IdClient) LEFT JOIN EtatLivraison ON PlanificationLivraisons.IdEtat = EtatLivraison.IdEtat) LEFT JOIN EtatQualification ON PlanificationLivraisons.IdStatutQualification = EtatQualification.IdEtat) LEFT JOIN LigneProduit ON ProduitVersion.IdLigneProduit = LigneProduit.IdLigneProduit WHERE  (  (((PlanificationLivraisons.IdEtat) <> 6) AND ((PlanificationLivraisons.IdEtat) <> 7) AND ((PlanificationLivraisons.IdEtat) <> 8))  )  ORDER BY IdLivraison DESC;

For a reason that I don't understand, I have also few queries which run, but not correctly. Which means :
- they run well
- but they take 9-26 seconds to execute (much more than my first times in milliseconds).
Here are some example of queries which return my performance issue :

SELECT Livrable.IdLivrable AS LIVRABLE_ID, Livrable.IdProduitVersion AS PRODUITVERSION_ID, ProduitVersion.LibelleProduitVersion AS PRODUITVERSION_LIBELLE, Livrable.IdEdition AS LIVRABLE_IDEDITION, IIf(IsNull(Edition.NomEdition),'',Edition.NomEdition) AS EDITION_NOM, Livrable.IdTypeLivrable AS TYPELIVRABLE_ID, TypeLivrable.LibelleTypeLivrable AS TYPELIVRABLE_LIBELLE, Livrable.NomLivrable AS LIVRABLE_NOM, Livrable.DateCreation AS LIVRABLE_DATECREATION, IIf(IsNull(Livrable.LivrableSujet),'',Livrable.LivrableSujet) AS LIVRABLE_SUJET, Livrable.LivrableImpacts AS LIVRABLE_IMPACTS, Livrable.TestsValidation AS LIVRABLE_TESTS_VALIDATION, IIf(IsNull(TypeLivrable2.LibelleTypeLivrable2),'',TypeLivrable2.LibelleTypeLivrable2) AS TYPELIVRABLE2_LIBELLE, Livrable.LivrableDescription AS LIVRABLE_DESCRIPTION, Livrable.ListeProgrammes AS LIVRABLE_LISTEPROGRAMMES, Livrable.ListeStructures AS LIVRABLE_LISTESTRUCTURES, Livrable.ListeFichiers AS LIVRABLE_LISTEFICHIERS, Livrable.Liste_JCL_MVS AS LIVRABLE_LISTE_JCL_MVS, Livrable.Liste_CLP_AS400 AS LIVRABLE_LISTE_CLP_AS400, Livrable.Liste_SHELL_UNIX AS LIVRABLE_LISTE_SHELL_UNIX, Livrable.PatchsDependants AS LIVRABLE_PATCHSDEPENDANTS, IIf(IsNull(Livrable_1.NomLivrable),'',Livrable_1.NomLivrable) AS LIVRABLE_PATCHASSOCIE, IIf(IsNull(Edition_1.NomEdition),'',Edition_1.Nomedition) AS EDITION_NOMPREREQUIS, Livrable.Authorized AS LIVRABLE_AUTHORIZED, IIf(([Livrable]![Authorized]=0 AND (IIf(IsNull(Livrable_1.NomLivrable),'',Livrable_1.NomLivrable))=''),1,0) AS PTCORREC_NON_AUTORISE, Livrable.ListeCorrection AS LIVRABLE_LISTECORRECTION, Livrable.ListeDocuments AS LIVRABLE_LISTE_DOCUMENTS, Livrable.ContenuVersion AS LIVRABLE_CONTENU_VERSION, Livrable.PatchsEnginePrerequisObligatoire AS LIVRABLE_LIVRABLEPREREQUIS_ENGINE_OBLIG, Livrable.PatchsEnginePrerequisFacultatif AS LIVRABLE_LIVRABLEPREREQUIS_ENGINE_FACULT, Livrable.PatchsProcessPrerequisObligatoire AS LIVRABLE_LIVRABLEPREREQUIS_PROCESS_OBLIG, Livrable.PatchsProcessPrerequisFacultatif AS LIVRABLE_LIVRABLEPREREQUIS_PROCESS_FACULT, Livrable.PatchsDesignPrerequisObligatoire AS LIVRABLE_LIVRABLEPREREQUIS_DESIGN_OBLIG, Livrable.PatchsDesignPrerequisFacultatif AS LIVRABLE_LIVRABLEPREREQUIS_DESIGN_FACULT, Livrable.InfoSup AS LIVRABLE_INFOSUP, IIf(IsNull(Theme.LibelleTheme),'',Theme.LibelleTheme) AS THEME_LIBELLE, Livrable.ListeProduits AS LIVRABLE_LISTEPRODUITSCATALOGUE, Intervenant.NomIntervenant & ' ' & Intervenant.PrenomIntervenant AS INTERVENANT_NOMPRENOM, Livrable.Warning AS LIVRABLE_WARNING FROM ((((((((TypeLivrable RIGHT JOIN Livrable ON TypeLivrable.IdTypeLivrable = Livrable.IdTypeLivrable) LEFT JOIN Edition ON Livrable.IdEdition = Edition.IdEdition) LEFT JOIN [Livrable&LivrablesAssocies] ON Livrable.IdLivrable = [Livrable&LivrablesAssocies].IdLivrableAssocie) LEFT JOIN Livrable AS Livrable_1 ON [Livrable&LivrablesAssocies].IdLivrable = Livrable_1.IdLivrable) LEFT JOIN Edition AS Edition_1 ON Livrable.IdEditionPrerequise = Edition_1.IdEdition) LEFT JOIN Theme ON Livrable.IdTheme = Theme.IdTheme) LEFT JOIN ProduitVersion ON Livrable.IdProduitVersion = ProduitVersion.IdProduitVersion) LEFT JOIN Intervenant ON Livrable.IdCorrecteur = Intervenant.IdIntervenant) LEFT JOIN TypeLivrable2 ON Livrable.IdTypeLivrable2 = TypeLivrable2.IdTypeLivrable2 WHERE  ( ((Livrable.IdProduitVersion)<>3 AND (Livrable.IdProduitVersion)<>56)  AND  ((ProduitVersion.IdLigneProduit) = 1)  AND  ((Livrable.IdProduitVersion) = 78)  AND  ((Livrable.IdTypeLivrable) = 4)  ) ORDER BY Livrable.IdOrder DESC;

SELECT Livrable.IdLivrable AS LIVRABLE_ID, Livrable.IdProduitVersion AS PRODUITVERSION_ID, ProduitVersion.LibelleProduitVersion AS PRODUITVERSION_LIBELLE, Livrable.IdTypeLivrable AS TYPELIVRABLE_ID, TypeLivrable.LibelleTypeLivrable AS TYPELIVRABLE_LIBELLE, Livrable.IdEdition AS EDITION_ID, Edition.NomEdition AS EDITION_NOM, Livrable.IdEditionPrerequise AS EDITION_IDPREREQUIS, IIf(IsNull(Edition_1.NomEdition),'',Edition_1.NomEdition) AS EDITION_NOMPREREQUIS, Livrable.IdProduitVersionSource AS LIVRABLE_IDPRODUITVERSIONSOURCE, Livrable.DateCreation AS LIVRABLE_DATECREATION, IIf(IsNull(Livrable.NomLivrable),'',Livrable.NomLivrable) AS LIVRABLE_NOM, Livrable.IdTheme AS THEME_ID, Livrable.IdTypeLivrable2 AS TYPELIVRABLE2_ID, IIf(IsNull(Livrable.LivrableSujet),'',Livrable.LivrableSujet) AS LIVRABLE_SUJET, Livrable.LivrableDescription AS LIVRABLE_DESCRIPTION, Livrable.LivrableImpacts AS LIVRABLE_IMPACTS, Livrable.TestsValidation AS LIVRABLE_TESTS_VALIDATION, Livrable.IdCorrecteur AS LIVRABLE_IDCORRECTEUR, Livrable.InfoSup AS LIVRABLE_INFOSUP, Livrable.ListeCorrection AS LIVRABLE_LISTECORRECTION, Livrable.ListeProgrammes AS LIVRABLE_LISTEPROGRAMMES, Livrable.ListeStructures AS LIVRABLE_LISTESTRUCTURES, Livrable.ListeFichiers AS LIVRABLE_LISTEFICHIERS, Livrable.Liste_JCL_MVS AS LIVRABLE_LISTE_JCL_MVS, Livrable.Liste_CLP_AS400 AS LIVRABLE_LISTE_CLP_AS400, Livrable.Liste_SHELL_UNIX AS LIVRABLE_LISTE_SHELL_UNIX, Livrable.ListeScriptsDB2 AS LIVRABLE_LISTESCRIPTSDB2, Livrable.ListeScriptsOracle AS LIVRABLE_LISTESCRIPTSORACLE, Livrable.ListeScriptsAS400 AS LIVRABLE_LISTESCRIPTSAS400, Livrable.PointDeCorrection AS LIVRABLE_POINTDECORRECTION, IIf(IsNull(Livrable_1.IdLivrable),null,Livrable_1.IDLivrable) AS LIVRABLE_IDPATCHASSOCIE, IIf(IsNull(Livrable_1.NomLivrable),'',Livrable_1.NomLivrable) AS LIVRABLE_PATCHASSOCIE, IIf(([Livrable]![Authorized]=0 AND (IIf(IsNull(Livrable_1.NomLivrable),'',Livrable_1.NomLivrable))=''),1,0) AS PTCORREC_NON_AUTORISE, IIf(IsNull(Livrable.PatchsEnginePrerequisObligatoire),'',Livrable.PatchsEnginePrerequisObligatoire) AS LIVRABLE_LIVRABLEPREREQUIS_ENGINE_OBLIG, IIf(IsNull(Livrable.PatchsEnginePrerequisFacultatif),'',Livrable.PatchsEnginePrerequisFacultatif) AS LIVRABLE_LIVRABLEPREREQUIS_ENGINE_FACULT, IIf(IsNull(Livrable.PatchsProcessPrerequisObligatoire),'',Livrable.PatchsProcessPrerequisObligatoire) AS LIVRABLE_LIVRABLEPREREQUIS_PROCESS_OBLIG, IIf(IsNull(Livrable.PatchsProcessPrerequisFacultatif),'',Livrable.PatchsProcessPrerequisFacultatif) AS LIVRABLE_LIVRABLEPREREQUIS_PROCESS_FACULT, IIf(IsNull(Livrable.PatchsDesignPrerequisObligatoire),'',Livrable.PatchsDesignPrerequisObligatoire) AS LIVRABLE_LIVRABLEPREREQUIS_DESIGN_OBLIG, IIf(IsNull(Livrable.PatchsDesignPrerequisFacultatif),'',Livrable.PatchsDesignPrerequisFacultatif) AS LIVRABLE_LIVRABLEPREREQUIS_DESIGN_FACULT, Livrable.Warning AS LIVRABLE_WARNING, Livrable.PatchsDependants AS LIVRABLE_PATCHSDEPENDANTS, Livrable.IdEtat AS LIVRABLE_IDETAT, IIf(IsNull(Livrable.PatchProcessPrecedent),'',Livrable.PatchProcessPrecedent) AS LIVRABLE_PATCH_PROCESS_PRECEDENT, IIf(IsNull(Livrable.PatchDesignPrecedent),'',Livrable.PatchDesignPrecedent) AS LIVRABLE_PATCH_DESIGN_PRECEDENT, Livrable.ListeDocuments AS LIVRABLE_LISTE_DOCUMENTS, Livrable.ContenuVersion AS LIVRABLE_CONTENU_VERSION FROM (((((Livrable LEFT JOIN TypeLivrable ON TypeLivrable.IdTypeLivrable = Livrable.IdTypeLivrable) LEFT JOIN [Livrable&LivrablesAssocies] ON Livrable.IdLivrable = [Livrable&LivrablesAssocies].IdLivrableAssocie) LEFT JOIN Livrable AS Livrable_1 ON [Livrable&LivrablesAssocies].IdLivrable = Livrable_1.IdLivrable) LEFT JOIN Edition ON Livrable.IdEdition = Edition.IdEdition) LEFT JOIN Edition AS Edition_1 ON Livrable.IdEditionPrerequise = Edition_1.IdEdition) LEFT JOIN ProduitVersion ON Livrable.IdProduitVersion = ProduitVersion.IdProduitVersion WHERE (((Livrable.IdLivrable)=588254941));

(sorry, there are auto-constructed access queries and names are in french, so they're quite stodgy)

I have to notice some things :
- The table Livrable have 18 000 entries (but I don't think it's a problem because I have no problem when I run SELECT * FROM Livrable)
- The last query doesn't react alike in my big and my little project. In my little project the run is too long (as I explained before), but in my big project it's return this error :

Etat HTTP 500 - javax.servlet.ServletException: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.7 java.lang.OutOfMemoryError: Java heap space

- I am using Java 6u45 and a .accdb dbfile

I did some research about this error, but I didn't find any solution which works for my problem.
I will try to create a query in Access to make fewer JOIN in my queries. But if you have some idea to solve my issue, I am interested.

Thanks ! ;)

Performance issues

A pure Java JDBC driver for Microsoft Access database files

Forums

Help

Performance issues

Performance issues

A pure Java JDBC driver for Microsoft Access database files

Forums

Help

Performance issues document.SUBSCRIPTION_OPTIONS = { "thing": "topic", "subscribed": false, "url": "subscribe", "icon": { "css": "fa fa-envelope-o" } };

Performance issues