SQL Access query speed

Question

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.

Do you have index on [OTP] on both table? If so, does index include column NumeroDA. Do you have index on NumeroCommandeReservation, as well as include NumeroDA? — ljh
– ljh, Commented Mar 25, 2013 at 17:07
No index on OTP, there are multiple rows with the same OTPs. But we can set non-unique index in Access, do you think it would help in this case? Same for NumeroDA, there are multiple rows with the same NumeraDA and NumeroCommandeReservation. The PKs are NumeroListe and NumeroArticle, both indexed with duplicate support. — dan
– dan, Commented Mar 25, 2013 at 17:12
It doesn't have to be unique, non-unique index can also help query, if you can create composite index or covering index, which can help you more. — ljh
– ljh, Commented Mar 25, 2013 at 17:15

PowerUser · Accepted Answer · 2013-03-25 17:08:47Z

1

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

answered Mar 25, 2013 at 17:08

PowerUser

11.8k20 gold badges67 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dan Over a year ago

It does work and gives me the same result in less than a second. So the problem is the subquery with the IN() function. Seems like Access doesn't like it. I will accept your answer as it does solve my problem, I will be more careful with this now. This query probably still coulb be improved.

PowerUser Over a year ago

In the interest of improving the query, those First() statements make me a little nervous. They could be hiding something important. I recommend getting rid of those First() statements and instead putting those fields in the Group By statement. Make sense?

bendataclear · Accepted Answer · 2013-03-25 17:02:36Z

0

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.

answered Mar 25, 2013 at 17:02

bendataclear

3,8574 gold badges34 silver badges58 bronze badges

3 Comments

dan Over a year ago

The original query only returns 23 rows, which is okay. I just tried your query, which is as long as my original query, and it returns a count of 111.

bendataclear Over a year ago

@dnLL in this case I think the only things that will help are adding indexes as ljh suggested or re-writing the query to remove the sub-query and move it to a join or the where section.

dan Over a year ago

PowerUser's solution actually fixed my problem, but I will look into adding indexes also.

Collectives™ on Stack Overflow

SQL Access query speed

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related