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 :
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 ! ;)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Have you verified that the Access database has indexes on each of the columns used in joins? That is, for the last query you gave, the columns that should be indexed are:
Thank you for the response.
I have added indexes on few columns that you have notice me and it seems to works better, I have better results (160 milliseconds) now.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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 :
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 :
(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
Livrablehave 18 000 entries (but I don't think it's a problem because I have no problem when I runSELECT * 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 :
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 ! ;)
Have you verified that the Access database has indexes on each of the columns used in joins? That is, for the last query you gave, the columns that should be indexed are:
Thank you for the response.
I have added indexes on few columns that you have notice me and it seems to works better, I have better results (160 milliseconds) now.