Optimisation de traitements SQL sous VBA

**C_Kloug** · 14/08/2009, 16h36

Bonjour à tous,

J'ai une table donnant une matrice diagonale de distance entre objets (3 champs numériques: IDa, IDd, Dist), c-à-d. qu'en faisant un tableau croisé dynamique, on obtiendrait une matrice triangulaire avec des données sur la diagonale et au-dessous (ou au dessous selon la requête).
Pour mon analyse, je dois obtenir une matrice carrée (une requête croisée donnerait alors une matrice complète).
Sur cette matrice, je calcul la moyenne des distances pour un ID avec tous les autres (y compris lui-même).
Jusqu'à présent, j'obtenais cela en faisant une requête union (cf. ci dessous)pour obtenir la matrice que je sauvegardais comme une table temporaire sur laquelle je travaillais après.

Code SQL :

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

1
2
3
4
5
Select temp.IDa, temp.IDb, temp.Dist
from Temp
Union Select temp.IDb, temp.IDa, temp.Dist
from Temp
where temp.IDa <> temp.IDb

Or, cela pouvait aller tant que le nombre d'ID n'était pas trop grand.
En effet, 100 ID donne une table de 100*100=10000 enregistrements après transformation pour avoir une matrice carré; ce qui peut aller.
Mais maintenant, j'ai plus de 10000 individus (soit 10000*10000=1.10^8 enregistrements

) et la table avant transformation fait déjà plus de 1,4 Go; je ne peux donc pas faire la transformation car je dépasserais la taille maximale allouée à Access 2007 (2 Go).

Pour pouvoir faire mon analyse, j'ai essayé de traiter chaque ID séparemment en via VBA (mais je suis novice pour cela).
Cela marche, mais ça prend près de 5 minutes par individu et à ce rythme, mon portable devrait tourner pendant près de 40 jours

.... avant que je puisse enfin avoir l'ensemble de mes résultats.
Pour optimiser, je pensais ajouter des index à la table de données, mais même cela je ne peux pas à cause de la taille.
Dans le code VBA, j'ai inséré des instructions SQL pour réaliser les analyses, quelqu'un aurait-il une idée pour que je puisse l'optimiser et ainsi réduire le temps de calcul ?
Je pensais supprimer les données de la table intiale au fûr et à mesure pour en réduire la taille et ainsi accélérer graduellement les requêtes, mais c'est un peu bidouillard et l'analyse risque quand même de prendre beaucoup de temps ...

Merci par avance ...

Le code VBA actuel est le suivant:

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
Function UPDT_GeneticParameters()
'Updates individual genetic paramaters from KinInbCoef's output file
'Requires the tables:
'   - list with the fields ID (long), ID_ECWP (text), F (single), N (long) and Mk (single)
'   - Out (as provided by KinInbCoef, the family is not requested)
Dim mydbs As Database
Dim rstSource1 As Recordset
Dim rstSource2 As Recordset
Dim rstSource3 As Recordset
Dim rstSource4 As Recordset
Dim rstResult As Recordset
Dim strN1 As Long
Dim strID As Long
Dim strID_ECWP As String
Dim strF As Single
Dim strN As Long
Dim strMk As Single
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
 
' Count the remaining number of individuals to update
strSQL1 = "SELECT Count(ID) as N1 from List where N is null"
 
'Provides the local ID (ID_ECWP) corresponding to the pedigree ID
strSQL2 = "SELECT First([List].[ID]) AS ID, First([Pedigree].[Stud_ID]) AS [Stud_ID]"
strSQL2 = strSQL2 & "FROM List INNER JOIN [Pedigree] ON [List].[ID] = [Pedigree].[ID]"
strSQL2 = strSQL2 & "WHERE ((([List].[N]) Is Null))"
 
' Provides the inbreeding for the first individual to update
strSQL3 = "SELECT First([Out].[Kinship]) AS [F]"
strSQL3 = strSQL3 & "FROM [List] INNER JOIN [Out] ON ([List].[ID] = [Out].[IDb]) AND ([List].[ID] = [Out].[IDa])"
strSQL3 = strSQL3 & "WHERE ((([List].[F]) Is Null))"
 
' Provides N (number of related individuals in the analysis, including itself)
' and Mk (the mean kinship of the individual with the rest of the population, including itself
 
strSQL4 = "SELECT [temp2].[ID], Count([temp2].[IDb]) AS [N], Avg([temp2].[Kinship]) AS [Mk]"
strSQL4 = strSQL4 & "FROM (SELECT [temp].[ID], [Out].[IDb], [Out].[Kinship]"
strSQL4 = strSQL4 & "FROM (SELECT First([List].[ID]) AS [ID]"
strSQL4 = strSQL4 & "FROM [List]"
strSQL4 = strSQL4 & "WHERE ((([List].[N]) Is Null))) AS [temp] INNER JOIN [Out] ON [temp].[ID] = [Out].[IDa]"
strSQL4 = strSQL4 & "Union SELECT [temp].[ID], [Out].[IDa], [Out].[Kinship]"
strSQL4 = strSQL4 & "FROM (SELECT First([List].[ID]) AS [ID]"
strSQL4 = strSQL4 & "FROM [List]"
strSQL4 = strSQL4 & "WHERE ((([List].[N]) Is Null))) AS [temp] INNER JOIN [Out] ON [temp].[ID] = [Out].[IDb]) AS [temp2]"
strSQL4 = strSQL4 & "GROUP BY [temp2].[ID]"
 
Set mydbs = CurrentDb
 
Set rstSource1 = mydbs.OpenRecordset(strSQL1, dbOpenDynaset)
strN1 = rstSource1("N1").Value
While strN1 > 0
 
    Set rstSource2 = mydbs.OpenRecordset(strSQL2, dbOpenDynaset)
    strID = rstSource2("ID").Value
    strID_ECWP = rstSource2("Stud_ID").Value
 
    Set rstSource3 = mydbs.OpenRecordset(strSQL3, dbOpenDynaset)
    strF = rstSource3("F").Value
 
    Set rstSource4 = mydbs.OpenRecordset(strSQL4, dbOpenDynaset)
    strN = rstSource4("N").Value
    strMk = rstSource4("Mk").Value
 
    Set rstResult = mydbs.OpenRecordset("List", dbOpenTable)
    rstResult.Index = "ID"
    rstResult.Seek "=", strID
    rstResult.Edit
    If rstResult("ID") = strID Then
        rstResult("ID_ECWP") = strID_ECWP
        rstResult("F") = strF
        rstResult("N") = strN
        rstResult("Mk") = strMk
        rstResult.Update
    End If
 
Wend
 
rstSource1.Close
rstSource2.Close
rstSource3.Close
rstSource4.Close
rstResult.Close
Set mydbs = Nothing
 
End Function

**loufab** · 18/08/2009, 10h33

Bonjour,

La plupart de tes questions pourront surement trouver une réponse dans le tuto sur l'optimisation des bases ACCESS (sur ma page perso) ; utilité des index quand et ou, contourner les problèmes de tailles de fichier, optimisation sql en tout genre...

Si Jet est sous dimensionné pour ton application, tu peux toujours passer sur un moteur plus robuste. (SQL Server, Oracle, MySQL...)

Concernant le code que tu fournis :
Sauf si tu n'est pas sur Jet je te conseille de passer par le générateur de requete, non pas que je pense que tu ne sais pas les écrire mais plutot que le générateur à tendance (dans la grande majorité des cas) à optimiser la requête pour mieux répondre au spécif de Jet.

Il est certain que la méthode DAO, (edit, update) est BEAUCOUP PLUS LONGUE que la méthode full queries. Intéresse-toi à l'opérateur logique In() il peut surement te venir en aide sur ce problème.

Concernant l'idée de suppression de données cela ne fait qu'ajouter des traitement supplémentaire donc couteux pour le moteur.

Cordialement,

**nico84** · 18/08/2009, 11h42

Bonjour,

J'ai pas tout compris

mais amha il faut chercher une solution qui évite la table intermédiaire. Par exemple, est-il possible d'écrire directement une fonction qui calcule la distance moyenne d'un individu aux autres et si oui combien de temps ça prend ?

Un truc du genre :

Code :

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

1
2
3
4
Public Function Get_moyenne(id)
  Set rst = CurrentDb.OpenRecordset("select sum(dist)/count(*) as x from matable where id=" & id ";")
  Get_moyenne=rst!x
End Function

Question subsidiaire : comment ont été documentées les 10^8 données ???

**loufab** · 19/08/2009, 19h55

Envoyé par nico84

Bonjour,

J'ai pas tout compris

mais amha il faut chercher une solution qui évite la table intermédiaire. Par exemple, est-il possible d'écrire directement une fonction qui calcule la distance moyenne d'un individu aux autres et si oui combien de temps ça prend ?

C'est qui amha ?

**C_Kloug** · 21/08/2009, 16h19

Bonjour Loufab, Nico84,

Merci à tous les deux pour vos conseils, je suis les prends en compte pour faire des modifs et vous informerais des résultats.

Toutes les requêtes sont faites via le générateur de requêtes (ne serait-ce que pour vérifier les résultats obtenus). Loufab, pour l'opérateur in(), tu parles bien de requêtes du type suivant à la place de sous-requêtes? Si c'est le cas, j'ai testé et cela me paraît plus long sur mes données.

Code SQL :

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

1
2
3
4
5
SELECT Out.IDa, Out.IDb, Out.Kinship
FROM Out
WHERE (((Out.IDa) In (SELECT First(Results.ID) AS PremierDeID
FROM Results
WHERE (((Results.N)=0))))))

Nico84, pour le moment, je n'ai pas encore de table avec 10^8 enregistrements car elle serait trop grande. La table fournie par le logiciel que j'utilse contient 5^7 enregistrement stockés dans un fichier texte (cela correspond à la matrice triangulaire pour 10000 individus). C'est ce fichier que j'importe et que je transforme ensuite pour obtenir une matrice carrée. Cela réponds t-il à ta question?

Cordialement