CODEWITHSUNDEEP
sqlms-accessquery-performance
dan
dan

Reputation: 3519

SQL Access query speed

I have the following query in my Access 2003 database:

SELECT 
    Projet.OTP AS OTP, 
    NumeroDA, 
    SUM(Quantite*PrixReelCommande) AS PrixTotal, 
    FIRST(Fournisseur1) AS Fournisseur, 
    FIRST(Projet.NumeroCommandeReservation) AS NumeroCommande, 
    FIRST(Projet.GestionContrat) AS GestionContrat, 
    FIRST(Projet.Acheteur) AS Acheteur, 
    MIN(DateLivraisonContractuelle) AS DateLivraisonContrat, 
    MAX(DateFournisseurLivraison) AS DateLivraisonFournisseur, 
    FIRST(InfoProjet.NomInstallation) AS NomInstallation, 
    FIRST(InfoProjet.TitreMandat) AS TitreMandat
FROM Projet LEFT JOIN InfoProjet ON Projet.OTP=InfoProjet.OTP
WHERE NumeroDA Like "#*" And NumeroDA IN (
                                            SELECT NumeroDA FROM Projet 
                                            WHERE NumeroCommandeReservation="" Or NumeroCommandeReservation Is Null Or NumeroCommandeReservation="0"
                                        )
GROUP BY Projet.OTP, Projet.NumeroDA
ORDER BY Projet.OTP, Projet.NumeroDA

The table Projet has ~2500 rows and InfoProjet has only 200 rows. Opening either of this table in Access takes less than 1 second. However, executing the above query takes more than 5 seconds.

I would like to know if there is anything I can do to improve the performance of this query. Is there something in the query that I should avoid performance-wise? Or am I just under Access limitations? I guess that using Like in the subquery doesn't help, but there must be something else that slows down the query.

Upvotes: 0

Views: 142

Answers (2)

PowerUser
PowerUser

Reputation: 11791

Since you're not using any Distincts in the subquery, could you simplify it a little by taking that part out? (I can't test this right now though, so I'm not entirely sure it would give the same results)

SELECT 
    Projet.OTP AS OTP, 
    NumeroDA, 
    SUM(Quantite*PrixReelCommande) AS PrixTotal, 
    FIRST(Fournisseur1) AS Fournisseur, 
    FIRST(Projet.NumeroCommandeReservation) AS NumeroCommande, 
    FIRST(Projet.GestionContrat) AS GestionContrat, 
    FIRST(Projet.Acheteur) AS Acheteur, 
    MIN(DateLivraisonContractuelle) AS DateLivraisonContrat, 
    MAX(DateFournisseurLivraison) AS DateLivraisonFournisseur, 
    FIRST(InfoProjet.NomInstallation) AS NomInstallation, 
    FIRST(InfoProjet.TitreMandat) AS TitreMandat
FROM Projet LEFT JOIN InfoProjet ON Projet.OTP=InfoProjet.OTP
WHERE NumeroDA Like "#*" And (
     NumeroCommandeReservation="" Or 
     NumeroCommandeReservation Is Null Or 
     NumeroCommandeReservation="0")

GROUP BY Projet.OTP, Projet.NumeroDA
ORDER BY Projet.OTP, Projet.NumeroDA

Upvotes: 1

bendataclear
bendataclear

Reputation: 3850

Try running this and see how many rows it returns:

SELECT COUNT(*)
FROM Projet LEFT JOIN InfoProjet ON Projet.OTP=InfoProjet.OTP
WHERE NumeroDA Like "#*" And NumeroDA IN (
                                        SELECT NumeroDA FROM Projet 
                                        WHERE NumeroCommandeReservation="" 
                                        Or NumeroCommandeReservation Is Null 
                                        Or NumeroCommandeReservation="0"
                                    )

Reason: Join may be returning more rows that you'd expect, but as you have only MAX/MIN/FIRST Aggregates you may not notice.

Upvotes: 0

Related Questions