Gestion Stock et Requêtes

**Tibros** · 01/08/2016, 13h35

Bonjour,

J'ai construit une base de données inspiré du tuto sur la création d'une base de gestion de stock :

http://claudeleloup.developpez.com/.../gestion-de-stock/

J'ai modifié les formules de stock pour passer par des requêtes SQL au lieu des DSum pour augmenter la rapidité. Et j'ai ajouté des critères supplémentaires pour avoir le stock en fonction de son N° de Lot et de son état de facturation.

Cependant, quand je veux exploiter ces données avec des tables entrées et sorties qui font au moins 1800 lignes chacunes. Lorsque que je lance une requête de création de table pour avoir le stock et que dans cette requête je mets une colonne avec la fonction concerné en indiquant comme critères de formule le champ ID produit, la date de jour, le champ N° de lot et le champ facturation. La requête est assez longue à exécuter

. J'aimerais connaitre un moyen d'augmenter la vitesse. Je ne sais pas si c'est la formule ou les regroupements qui ralentissent la requête.

Faut-il que je revois mon architecture pour intégrer une table stock afin d'incrémenter les entrées et sorties ?

Merci par avance pour votre aide.

**martii843** · 01/08/2016, 15h03

Salut,

Tu dois avoir quelque chose dans tes requêtes qui t'augmentes le temps de traitement.

Peut être as tu utilisé trop de fonction de domaine ?

Ensuite tu dis "Lorsque que je lance une requête de création de table", c'est à dire ?

**Tibros** · 01/08/2016, 15h16

Envoyé par martii843

Salut,

Tu dois avoir quelque chose dans tes requêtes qui t'augmentes le temps de traitement.

Peut être as tu utilisé trop de fonction de domaine ?

Ensuite tu dis "Lorsque que je lance une requête de création de table", c'est à dire ?

La requête de sélection sans création de table prend moins de temps qu'avec la création de la table ce qui est normal je suppose. J'ai peut-être mal construit cette requête.

Voici la version SQL Access de ce que je fais :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT CStr([T_PDTS]![CIP_PK])+CStr([T_STOCK_ENTREE]![EntreeLot]) AS NumCle, 
T_PDTS.CIP_PK, 
T_PDTS.LIBELLE, 
T_PDTS.[CDT° STOCKAGE], 
T_STOCK_ENTREE.EntreeLot, 
T_STOCK_ENTREE.EntreePeremption, 
StockLotADate([T_PDTS]![CIP_PK],Date(),[T_STOCK_ENTREE]![EntreeLot]) AS [Stock Lot], 
Last(R_PDTS_COMMENT_SNA.EntreeCommentaires) AS DernierDeEntreeCommentaires 
 
INTO T_SNA
 
FROM (T_PDTS INNER JOIN T_STOCK_ENTREE ON T_PDTS.CIP_PK = T_STOCK_ENTREE.CIP_FK) 
 
LEFT JOIN R_PDTS_COMMENT_SNA ON (T_STOCK_ENTREE.CIP_FK = R_PDTS_COMMENT_SNA.CIP_PK) AND (T_STOCK_ENTREE.EntreeLot = R_PDTS_COMMENT_SNA.EntreeLot)
 
GROUP BY T_PDTS.CIP_PK, T_PDTS.LIBELLE, T_PDTS.[CDT° STOCKAGE], T_STOCK_ENTREE.EntreeLot, T_STOCK_ENTREE.EntreePeremption
 
HAVING (((StockLotADate([T_PDTS]![CIP_PK],Date(),[T_STOCK_ENTREE]![EntreeLot]))<>0));

**Tibros** · 01/08/2016, 15h29

Autre précision voici le code de la fonction de StockLotADate utilisé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
 
Option Explicit
 
   Dim rst As DAO.Recordset
   Dim strSql As String
   Dim strTable As String
   Dim strChamp As String
   Dim strDateWhere As String
   Dim strLotWhere As String
   Dim strFact As String
 
Public Function EntreesLotADate(Article As Long, DateAng As Date, Lot As String) As Variant
    On Error GoTo GestError
 
    ' ===== affectation =====
 
   strTable = "T_STOCK_ENTREE"
   strChamp = "EntreeQTE"
   strDateWhere = "EntreeDate"
   strLotWhere = "EntreeLot"
 
    strSql = "SELECT Sum(" & strChamp & ") AS Somme " _
            & "FROM " & strTable & " " _
            & "WHERE " & strDateWhere & "<=#" & DateAng & "# AND " _
                       & "  CIP_FK=" & Article & " AND " _
                       & "" & strLotWhere & "=" & Chr(34) & Lot & Chr(34) & ";"
 
    Set rst = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
    If Not rst.EOF Then
 
    EntreesLotADate = Nz(rst.Fields("Somme"), 0)
 
    Else
 
    EntreesLotADate = 0
 
    End If
 
    ' ===== libération =====
    rst.Close
    Set rst = Nothing
    Exit Function
 
GestError:
 
    If Err.Number = 3061 Then
        MsgBox "Une des donnée entrée en paramètre est erronée", vbOKOnly + vbExclamation
    End If
 
End Function
 
 
Public Function SortiesLotADate(Article As Long, DateAng As Date, Lot As String) As Variant
    On Error GoTo GestError
 
    ' ===== affectation =====
 
   strTable = "T_STOCK_SORTIE"
   strChamp = "SortieQTE"
   strDateWhere = "SortieDate"
   strLotWhere = "SortieLot"
 
    strSql = "SELECT Sum(" & strChamp & ") AS Somme " _
            & "FROM " & strTable & " " _
            & "WHERE " & strDateWhere & "<=#" & DateAng & "# AND " _
                       & "  CIP_FK=" & Article & " AND " _
                       & "" & strLotWhere & "=" & Chr(34) & Lot & Chr(34) & ";"
 
    Set rst = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
    If Not rst.EOF Then
 
    SortiesLotADate = Nz(rst.Fields("Somme"), 0)
 
    Else
 
    SortiesLotADate = 0
 
    End If
 
    ' ===== libération =====
    rst.Close
    Set rst = Nothing
    Exit Function
 
GestError:
 
    If Err.Number = 3061 Then
        MsgBox "Une des donnée entrée en paramètre est erronée", vbOKOnly + vbExclamation
    End If
 
End Function
 
 
Public Function StockLotADate(Article As Long, DateAng As Date, Lot As String) As Single
 
StockLotADate = EntreesLotADate(Article, DateAng, Lot) - SortiesLotADate(Article, DateAng, Lot)
End Function

**martii843** · 01/08/2016, 15h49

Pourquoi construis tu une autre table ?

**Tibros** · 01/08/2016, 16h05

Envoyé par martii843

Pourquoi construis tu une autre table ?

C'est un moyen que j'ai trouvé pour créer une table temporaire lors du lancement de l'application, mais je perds en temps dès que je la mets à jour suite à une modification sur la fermeture du formulaire qui exploite cette table temporaire.

Au départ je n'utilisais que la requête comme source du formulaire mais vu le temps de traitement j'ai pensé qu'un lancement au démarrage de l'application serait plus efficace mais ce que je gagne à un endroit je le perds à un autre

.

La seule solution s'est de retravailler la requête en la scindant en plusieurs étapes peut-être ?

**martii843** · 01/08/2016, 17h13

A mon avis tu as arriveras quasiment au même résultat en scindant ta requête.

Combien de temps, à peu près, met ton traitement à se faire ?

Peux tu mettre ton application en PJ ?

**Tibros** · 02/08/2016, 09h24

Le traitement de la requête sans création de table met 16s ce qui est beaucoup.

Malheureusement je ne peux pas mettre l'application en pièce jointe : données confidentielles de mon entreprise.

**martii843** · 02/08/2016, 11h20

D'accord, c'est lent, tu as un soucis dans ta requête.

Tu peux copier coller ta requête sélection STP ?

**Tibros** · 02/08/2016, 11h28

La voici en SQL :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT T_PDTS.CIP_PK, T_PDTS.LIBELLE, T_PDTS.[CDT° STOCKAGE], T_STOCK_ENTREE.EntreeLot, T_STOCK_ENTREE.EntreePeremption, T_STOCK_ENTREE.EntreeFacturation, StockLotFactADate([T_STOCK_ENTREE]![CIP_FK],Date$(),[T_STOCK_ENTREE]![EntreeLot],[T_STOCK_ENTREE]![EntreeFacturation]) AS [Stock Total], Last(T_STOCK_ENTREE.EntreeDate) AS DernierDeEntreeDate, Last(R_PDTS_COMMENT_SNA.EntreeDate) AS [Date Commentaires], Last(R_PDTS_COMMENT_SNA.EntreeCommentaires) AS DernierDeEntreeCommentaires, r_Stk_Select.CIP_FK
FROM ((T_PDTS INNER JOIN T_STOCK_ENTREE ON T_PDTS.CIP_PK = T_STOCK_ENTREE.CIP_FK) LEFT JOIN R_PDTS_COMMENT_SNA ON (T_STOCK_ENTREE.CIP_FK = R_PDTS_COMMENT_SNA.CIP_PK) AND (T_STOCK_ENTREE.EntreeLot = R_PDTS_COMMENT_SNA.EntreeLot)) LEFT JOIN r_Stk_Select ON (T_STOCK_ENTREE.CIP_FK = r_Stk_Select.CIP_FK) AND (T_STOCK_ENTREE.EntreeLot = r_Stk_Select.EntreeLot)
GROUP BY T_PDTS.CIP_PK, T_PDTS.LIBELLE, T_PDTS.[CDT° STOCKAGE], T_STOCK_ENTREE.EntreeLot, T_STOCK_ENTREE.EntreePeremption, T_STOCK_ENTREE.EntreeFacturation, StockLotFactADate([T_STOCK_ENTREE]![CIP_FK],Date$(),[T_STOCK_ENTREE]![EntreeLot],[T_STOCK_ENTREE]![EntreeFacturation]), r_Stk_Select.CIP_FK
HAVING (((StockLotFactADate([T_STOCK_ENTREE]![CIP_FK],Date$(),[T_STOCK_ENTREE]![EntreeLot],[T_STOCK_ENTREE]![EntreeFacturation]))<>0))
ORDER BY T_PDTS.LIBELLE;

J'ai déjà réussi à gagner en rapidité en mettant des index ce que je n'avais pas fait au départ.

**martii843** · 02/08/2016, 11h39

Merci,

Attention avec "Last" il peut y avoir des problèmes de récupération de données et de lenteur dans le traitement.

Peux tu aussi, STP, envoyer un Impr Ecran de ta requête en mode création pour voir tes tables ?

**Tibros** · 02/08/2016, 11h44

Pour info dans la table T_STOCK_ENTREE les champs CMUP et EntreePU ne sont pas utilisés.

Nom : Requete Mode Création.PNG
Affichages : 375
Taille : 31,3 Ko

**Tibros** · 04/08/2016, 09h24

Je pense avoir trouver l'essentiel du problème il s'agit de cette fonction de stock qui est une fonction DSum au format SQL qui ralentie le système quand je l'utilises sur la Table T_STOCK_ENTREE qui est la table source qui sert pour les calculs.

Pouvez-vous m'aider à l'optimiser ?

Merci,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
Option Explicit
 
   Dim rst As DAO.Recordset
   Dim strSql As String
   Dim strTable As String
   Dim strChamp As String
   Dim strDateWhere As String
   Dim strLotWhere As String
   Dim strFact As String
 
Public Function EntreesLotADate(Article As Long, DateAng As Date, Lot As String) As Variant
    On Error GoTo GestError
 
    ' ===== affectation =====
 
   strTable = "T_STOCK_ENTREE"
   strChamp = "EntreeQTE"
   strDateWhere = "EntreeDate"
   strLotWhere = "EntreeLot"
 
    strSql = "SELECT Sum(" & strChamp & ") AS Somme " _
            & "FROM " & strTable & " " _
            & "WHERE " & strDateWhere & "<=#" & DateAng & "# AND " _
                       & "  CIP_FK=" & Article & " AND " _
                       & "" & strLotWhere & "=" & Chr(34) & Lot & Chr(34) & ";"
 
    Set rst = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
    If Not rst.EOF Then
 
    EntreesLotADate = Nz(rst.Fields("Somme"), 0)
 
    Else
 
    EntreesLotADate = 0
 
    End If
 
    ' ===== libération =====
    rst.Close
    Set rst = Nothing
    Exit Function
 
GestError:
 
    If Err.Number = 3061 Then
        MsgBox "Une des donnée entrée en paramètre est erronée", vbOKOnly + vbExclamation
    End If
 
End Function
 
 
Public Function SortiesLotADate(Article As Long, DateAng As Date, Lot As String) As Variant
    On Error GoTo GestError
 
    ' ===== affectation =====
 
   strTable = "T_STOCK_SORTIE"
   strChamp = "SortieQTE"
   strDateWhere = "SortieDate"
   strLotWhere = "SortieLot"
 
    strSql = "SELECT Sum(" & strChamp & ") AS Somme " _
            & "FROM " & strTable & " " _
            & "WHERE " & strDateWhere & "<=#" & DateAng & "# AND " _
                       & "  CIP_FK=" & Article & " AND " _
                       & "" & strLotWhere & "=" & Chr(34) & Lot & Chr(34) & ";"
 
    Set rst = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
    If Not rst.EOF Then
 
    SortiesLotADate = Nz(rst.Fields("Somme"), 0)
 
    Else
 
    SortiesLotADate = 0
 
    End If
 
    ' ===== libération =====
    rst.Close
    Set rst = Nothing
    Exit Function
 
GestError:
 
    If Err.Number = 3061 Then
        MsgBox "Une des donnée entrée en paramètre est erronée", vbOKOnly + vbExclamation
    End If
 
End Function
 
 
Public Function StockLotADate(Article As Long, DateAng As Date, Lot As String) As Single
 
StockLotADate = EntreesLotADate(Article, DateAng, Lot) - SortiesLotADate(Article, DateAng, Lot)
End Function

**Tibros** · 04/08/2016, 09h29

Je viens de tester une requete en indiquant de mettre uniquement le calcul de la fonction StockLotADate() que quand ils sont différents de 0, en utilisant le critère : <>0. Et la c'est le drame

cela ralentie grandement la requête.

Je vais essayer d'utiliser autre chose pour filtrer et n'afficher que les lignes qui ont du stock.

**Tibros** · 04/08/2016, 15h58

Afin d'accélérer les requêtes j'ai transposé la formule VBA de calcul de stock en plusieurs requêtes Access qui vont bien plus vite. Je l'ai par la suite intégré dans les requête de création de table. Et ça a marché

Merci pour votre aide.

Gestion Stock et Requêtes [AC-2007]

Access

Discussions similaires

Partager

Partager