Optimisation Traitement ADO

Version imprimable

bonjour,

Voici mon problème, je doit traiter plusieurs millions d'enregistrement d'une BDD.

Voici mon code, il n'est pas propre je sais mais je l'ai développé en 10 minutes pour commencer le traitement le plus vite possible.

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
 
Private Sub Command1_Click()
    Dim cnx_sql As ADODB.Connection
    Dim rc As ADODB.Recordset
    Dim rc2 As ADODB.Recordset
    Dim rc3 As ADODB.Recordset
    Dim idaffi As Long
    Dim idtva As Integer
    Dim i, i2 As Long
 
    Set cnx_sql = New ADODB.Connection
    cnx_sql.ConnectionString = "DRIVER={SQL Server};server=SQL;uid=;pwd=;database=Transmission; "
    cnx_sql.Open
    Set rc = New ADODB.Recordset
    rc.CacheSize = 300
    rc.Open "select * from table_2003 where date_trans='10301'", cnx_sql, adOpenStatic, adLockOptimistic, -1
    i = 0
    i2 = rc.RecordCount
    prog.Max = i2
    Label1.Caption = i & " sur " & i2
    Do Until rc.EOF
        'sélection du l'id_affilié
        Set rc2 = New ADODB.Recordset
        idaffi = 0
        rc2.Open "select id_affilies from affilies where code_trans='" & rc!code & "'", cnx_sql, adOpenStatic, adLockOptimistic, -1
        idaffi = rc2!id_affilies
        rc2.Close
        'Sélection du code TVA
        'Set rc2 = New ADODB.Recordset
        'rc2.Open "select ID_TVA from Ligne_code_tva where id_affilies= '" & idaffi & "' and code_tva_affilie='" & rc!tva & "'", cnx_sql, adOpenStatic, adLockOptimistic, -1
        'idtva = rc2!id_tva
        'rc2.Close
        cnx_sql.Execute ("insert into ligne_transmission (id_affilies,id_tva,cip,paht,pvht,stock,quantite,date_transmission) values ('" & idaffi & "','" & rc!tva & "','" & rc!CIP & "','" & rc!PA & "','" & rc!PV & "','" & rc!stock & "','" & rc!quantité & "','" & Replace(rc!Date_trans, "103", "2003") & "')")
        i = i + 1
        prog.Value = i
        Label1.Caption = i & " sur " & i2
        DoEvents
        rc.MoveNext
    Loop
    rc.Close
 
 
End Sub

Ma question est quelles sont les moyens d'optimiser les requêtes action et le parcours de mon recordset "rc", j'ai mis un cachesize à 300 mais ça n'a pas l'air d'aller beaucoup plus vite.

19/04/2005, 13h58
bidou
il faut :
- changer le curseur RC2 pour le mettre cotè serveur, readonly, en avant seulement
- Enlever les notation bang (rc!TVA) pour des notation strictes rc.Fields(NuméroChamp).Value
- Changer le curseur rc, mettre coté serveur, keyset, readonly
- utiliser une commande paramétrée plutot que faire de la concaténation de chaine
19/04/2005, 14h11
adjava

et avec cela tu penses que ja vais gagner pas mal de temps?

A voir.

Dans l'hypothèse où ta table affiliés ne opeut avoir qu'une ligne par code_trans. Ta première requète peut se transformer ainsi.
Code:

1 2 3 Select t.* ,id_affilies from table_2003 t inner join affilies a on a.code_trans=t.code where date_trans='10301'
De préférence avec énumération des champs.
Du coup ton code pourrait se simplifier ainsi :
Code:

1 2 3 4 5 insert into ligne_transmission (id_affilies,id_tva,cip,paht,pvht,stock,quantite,date_transmission) Select id_affilies, t.tva, t.CIP, t.PA, T.PV, T.STOCK, T.quantite, '200301' from table_2003 t inner join affilies a on a.code_trans=t.code where date_trans='10301'
Du coup tu fais toute l'opération en une requete, mais là ce n'est pas du VB mais du SQL.
A+
Soazig

19/04/2005, 14h19
bidou

gagner du temps c'est sur. Maintenant ca ne change rien au fait qu'il y aura des millions d'enregistrements à traiter.
19/04/2005, 14h31
adjava

Bah oui je suis trop bête moi, au début j'avais des traitements plus complexes à réaliser mais maintenant j'ai juste une donnée à récupérer en fonction d'une autre et je me fait ch*** à faire du VB alors qu'avec du SQL c'est plus rapide et beaucoup plus simple

Bon allez hop un petit coup d'analyseur de requête SQL

Merci beaucoup Soazig.

Je ne met pas résolu au cas ou j'aurais encore quelques soucis. Je le metterais une fois fini

Je lui ai mis ça comme requête SQL mais visiblement il n'en veux pas
Code:

1 2 3 4 5 6 insert into ligne_transmission (id_affilies,id_tva,cip,paht,pvht,stock,quantite,date_transmission) Select id_affilies, t.tva, t.CIP, t.PA, T.PV, T.STOCK, T.quantite, '200301' from table_2003 t inner join affilies a on a.code_trans=t.code where date_trans='10301'
Erreur : Syntaxe incorrecte vers le mot clé 'Select'

C'est vrai que ça me parait bizarre de faire un select dans une clause insert into. Je précise que je suis sous SQLServer 2000, mais bon ça se voyait dans mon code

19/04/2005, 14h44
adjava

Autant pour moi, la requête de soazig fonctionne nickel, je ne savais pas que l'on pouvais faire ce genre de requête, je pensait qu'il fallait absolument mettre values (...)

Merci soazig
19/04/2005, 14h48
adjava

39 secondes de traitement au lieu d'une heure pour 1 million d'enregistrements, :o

bah ça va permettre de gagner pas mal de temps tout ça sachant que je doit traiter environ 30 millions d'enregistrements