Optimisation du code d'extraction

Version imprimable

Bonjour ,

je suis entrain de faire une extraction de données à partir de ma base Oracle sous Excel on cliquant sur un bouton extract. le problème que ça met beaucoup de temps pour faire une extraction de 60000 ligne et la Feuil Excel m'affiche toute en haut à gauche nom_de_fichier.xlsm (Ne répond pas) (sachant bien que ma requête et très longue et il y a beaucoup de jointure entre les tables et quand je fais Cells(10, 10).Value = Sql la requête que j'obtiens dans la cellule (10,10) je l’exécute sous SQLDEV et marche bien) ,il y a quelques jours l'extraction se fait en quelques minutes .
j'aimerai bien savoir si je pourrai optimiser le code (ci-joint) afin diminuer la durée d’extraction.
je vous remercie d'avance .

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
 
Private Sub CommandButton1_Click()
 
Dim intResult
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
 
'Dim query As String
Dim Sql As String
 
Dim d1 As String
Dim d2 As String
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
 
con.ConnectionString = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=XXXXXXXX)(PORT=xxxx))" & _
"(CONNECT_DATA=(SID=XXXX))); uid=XXXXX; pwd=XXXXX;"
con.Open
 
If (con.State = 1) Then
d1 = InputBox("Date au format YYYY/MM/DD", "Entrez Deb", Format(Date, "YYYY/MM/DD"))
d2 = InputBox("Date au format YYYY/MM/DD", "Entrez Fin", Format(Date, "YYYY/MM/DD"))
If d1 <> "" Then
  If Not IsDate(d1) Then MsgBox "Pas une date": Exit Sub
  Else
   MsgBox "Vous devez date": Exit Sub
 End If
 
If d2 <> "" Then
    If Not IsDate(d2) Then MsgBox "Pas une date": Exit Sub
  Else
   MsgBox "Vous devez date": Exit Sub
 End If
StartDate = Format(d1, "yyyymmdd")
EndDate = Format(d2, "yyyymmdd")
Sql = "select    requête ," & vbCrLf
Sql = Sql & "   requêterequêterequête," & vbCrLf
Sql = Sql & "  requêterequêterequêterequête," & vbCrLf
........
...........
...........
 
rs.Open Sql, con
 
If rs.State = 1 Then
                If Not (rs.EOF) Then
                       For Each qf In rs.Fields
                       Range("a5").Offset(0, coloffset).Value = qf.Name
                       coloffset = coloffset + 1
                       Next qf
                       Set Wks = Sheets("Feuil1")
                       Sheets("Feuil1").Range("A6").CopyFromRecordset rs
 
                End If
                rs.Close
        End If
        Else
            intResult = MsgBox("Could not connect to the database.  Check your user name and password." & vbCrLf & Error(Err), 16, "Oracle Connection Demo")
End If
 
con.Close
 
With Worksheets("Feuil1")
 
derl = .Range("A1048576").End(xlUp).Row
Tbl = .Range("A5:X" & derl)
 
End With
 
 
End Sub

13/04/2017, 14h06
Invité

bonjour,
il n' a pas de réduction possible au niveau du code, si ce n'est que purement esthétique!

en revanche vue que tu n'exposes pas toute la requête, fais tu du group by having?

EDIT:
j'ai repris ton ancien message; pas de group by ; pas de having!

merci pour la réponse j'ai pas mis ma requête vu que ça marche sur SqlDev ,elle est la :

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
 
Sql = "select    DRI_ACC.DRI_CODE," & vbCrLf
Sql = Sql & "   DRI_ACC.DRI_LINK," & vbCrLf
Sql = Sql & "   DRI_REF.DRI_SEC_SETTLT_SYST_REF AS REF_GSP," & vbCrLf
 
Sql = Sql & "   DRI_ACC.DRI_CLIENT_REF AS REF_CLIENT," & vbCrLf
 
Sql = Sql & "   SCO.SCO_CODE AS ISIN," & vbCrLf
Sql = Sql & "   SCO.SCO_NAME AS LIB_ISIN," & vbCrLf
Sql = Sql & "   DRI_ACC.DEP_CODE ," & vbCrLf
Sql = Sql & "   DEP.DEP_NAME ," & vbCrLf
Sql = Sql & "   DRI_ACC.SAC_CODE ," & vbCrLf
Sql = Sql & "   SAC.SAC_NAME ," & vbCrLf
Sql = Sql & "   DRI_ACC.STS_CODE, " & vbCrLf
 
Sql = Sql & "  (SELECT ICO_CODE FROM ITL_CONTEXT WHERE IL_LNG_ID = '001' AND ICO_PSEUDO = DRI_ACC.IL_CTX_ID) AS CONTEXTE," & vbCrLf
Sql = Sql & "      TO_CHAR (PFC_54X.DATE_CREATION, 'YYYY/MM/DD HH24:MI:SS') AS  DATE_CREATION_PFC_54X," & vbCrLf
Sql = Sql & "      TO_CHAR (PFC_54X.DATE_MODIFICATION,'YYYY/MM/DD HH24:MI:SS') AS DATE_MODIF_PFC_54X," & vbCrLf
Sql = Sql & "      TO_CHAR (DRI_ACC.DRI_CREATE_DATE,'YYYY/MM/DD HH24:MI:SS' ) AS DATE_CREATION_SIB," & vbCrLf
Sql = Sql & "      TO_CHAR (DRI_EV.DRI_INPUT_DATE,'YYYY/MM/DD HH24:MI:SS') AS DATE_EV_SIB," & vbCrLf
Sql = Sql & "      TO_CHAR (DRI_ACC.DRI_INPUT_DATE,'YYYY/MM/DD HH24:MI:SS' ) AS DATE_ACQ_SIB," & vbCrLf
Sql = Sql & "      TO_CHAR (PFC_548.DATE_CREATION,'YYYY/MM/DD HH24:MI:SS' ) AS DATE_CREATION_PFC_548" & vbCrLf
Sql = Sql & " FROM PFCMSGH PFC_54X, PFCMSGH PFC_548, DRI_HISTO DRI_ACC, DRI_HISTO DRI_EV, DEPOSITARY DEP, SECURITIES_ACCOUNT SAC, DRI_HISTO DRI_REF, SECURITY_CODIF SCO " & vbCrLf
Sql = Sql & " WHERE 1=1 " & vbCrLf
Sql = Sql & " AND (TRUNC(DRI_ACC.dri_create_date) >=  TRUNC(TO_DATE(" & StartDate & ",'YYYYMMDD HH:MI:SS'))" & vbCrLf
Sql = Sql & " AND TRUNC(DRI_ACC.dri_create_date) <= TRUNC(TO_DATE(" & EndDate & " ,'YYYYMMDD HH:MI:SS')))" & vbCrLf
Sql = Sql & "AND PFC_54X.ID = (SELECT MIN(ID) FROM LIEN_MESSAGE WHERE LME_APP_CODE = DRI_ACC.DRI_CODE||DRI_ACC.DRI_LINK AND IL_APP_ID = '001' AND IC_LME_SENS = '001') " & vbCrLf
Sql = Sql & " AND PFC_54X.IL_PFS_ID = '075'  " & vbCrLf
 
Sql = Sql & " AND SAC.SAC_CODE = DRI_ACC.SAC_CODE AND SAC.SAC_BAK_CODE = DRI_ACC.SAC_BAK_CODE" & vbCrLf
Sql = Sql & " AND DRI_ACC.SEC_CODE = SCO.SEC_CODE AND SCO.COF_CODE = 'ISIN'" & vbCrLf
Sql = Sql & " AND DRI_ACC.DEP_CODE = DEP.DEP_CODE" & vbCrLf
Sql = Sql & " AND PFC_548.ID = (SELECT MIN(ID) FROM LIEN_MESSAGE WHERE LME_APP_CODE = DRI_ACC.DRI_CODE||DRI_ACC.DRI_LINK AND IL_APP_ID = '001' AND IL_REP_ID IN ('193', '194', '195', '198', '201') AND LME_CREATION_DATE >= DRI_ACC.DRI_INPUT_DATE)" & vbCrLf
Sql = Sql & " " & vbCrLf
Sql = Sql & " AND PFC_548.IL_PFS_ID = '040'" & vbCrLf
Sql = Sql & " " & vbCrLf
Sql = Sql & " AND DRI_ACC.DRI_INPUT_DATE = (SELECT MIN(DRI_INPUT_DATE) FROM DRI_HISTO DRI_MIN WHERE DRI_MIN.DRI_CODE = DRI_ACC.DRI_CODE AND DRI_MIN.DRI_LINK = DRI_ACC.DRI_LINK AND DRI_MIN.IL_STA_ID = '009')" & vbCrLf
Sql = Sql & " AND DRI_ACC.IL_STA_ID = '009'" & vbCrLf
Sql = Sql & " AND DRI_REF.DRI_CODE = DRI_ACC.DRI_CODE AND DRI_REF.DRI_LINK = DRI_ACC.DRI_LINK" & vbCrLf
Sql = Sql & " AND DRI_REF.DRI_INPUT_DATE = (SELECT MIN(DRI_INPUT_DATE) FROM DRI_HISTO DRI_MIN WHERE DRI_MIN.DRI_CODE = DRI_ACC.DRI_CODE AND DRI_MIN.DRI_LINK = DRI_ACC.DRI_LINK AND DRI_MIN.DRI_SEC_SETTLT_SYST_REF IS NOT NULL)" & vbCrLf
Sql = Sql & " AND DRI_EV.DRI_CODE = DRI_ACC.DRI_CODE AND DRI_EV.DRI_LINK = DRI_ACC.DRI_LINK" & vbCrLf
Sql = Sql & " AND DRI_EV.DRI_INPUT_DATE = (SELECT MIN(DRI_INPUT_DATE) FROM DRI_HISTO DRI_MIN WHERE DRI_MIN.DRI_CODE = DRI_ACC.DRI_CODE AND DRI_MIN.DRI_LINK = DRI_ACC.DRI_LINK AND DRI_MIN.IL_STA_ID = '012')" & vbCrLf
Sql = Sql & " AND DRI_EV.IL_STA_ID = '012'" & vbCrLf
Sql = Sql & " AND NOT EXISTS (SELECT NULL FROM DRI_HISTO WHERE DRI_CODE = DRI_ACC.DRI_CODE AND IL_STA_ID IN ('001','007')" & vbCrLf
Sql = Sql & " UNION SELECT NULL FROM DRI_HISTO_RECENT WHERE DRI_CODE = DRI_ACC.DRI_CODE AND IL_STA_ID IN ('001','007'))" & vbCrLf
Sql = Sql & " AND TRUNC(PFC_54X.DATE_MODIFICATION) = TRUNC(PFC_548.DATE_CREATION) " & vbCrLf
Sql = Sql & " AND DRI_ACC.STS_CODE = 'T2S'"

13/04/2017, 17h32
achraf dev

dysorthographie ,
je dois rajouter group by et having ?dans ma requête ?
13/04/2017, 18h11
Invité

Non mais comme ça bouf de la ressource je me méfie de l'usage intempestifs du group by!

J'avoue que je marche les cheveux avec ta requête!

Il ya le contexte, ce quelle fait et son environnement en terme de tables.

Je me souviens t'avoir répondu sur ce genre de sujet par le passé. Ta requête est conforme mais l'encodage sql oracle est a la fois plus permissif et optimiser qu'ado je le crains.

Ado s'adresse a tout type alors qu'oracle Sql Dev est chez lui.

Bref ta requête n'est pas optimiser dans un logique sql universelle les liens entre table sont implicite.

Hormis pendre chaque sous requête et voir comment la lier avec les autre table inner join...

Ça fait plus de quinze ans que je n'ai pas pratiqué a ce niveau.

Je pourrais jouer le rôle du candide averti certe mais candide!
13/04/2017, 18h30
achraf dev

merci dysorthographie je vais essayer de voir
bonne soirée