Optimisation de traitements SQL sous VBA
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:
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 :vomi:) 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 :arf: .... 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:
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 |
