Transaction Longue VB.Net et Oracle

**jlm22** · 21/07/2011, 09h27

Bonjour à tous,

j'ai un programme VB qui exécute une transaction dans laquelle se trouve un delete puis un insert, et tant que l'insert ne s'est pas bien déroulé je ne commit pas les requêtes.
Mon problème : Lorsque l'insert et le delete sont dans une instance de transaction différente le programme s'exécute en environ 3 min, en mettant le delete et l'insert dans la même transaction (ce qui est le but pour éviter les pertes de données) le programme dure environ 10 min.

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
 
 Dim sql_transaction As OracleTransaction = conx.BeginTransaction
 
        MycommandSuppressionZARTFABOracle = New OracleCommand()
        MycommandSuppressionZARTFABOracle.Connection = conx
        MycommandSuppressionZARTFABOracle.Transaction = sql_transaction
        MycommandSuppressionZARTFABOracle.CommandType = CommandType.Text
        MycommandSuppressionZARTFABOracle.CommandText = "delete from ZARTFAB2"
        MycommandSuppressionZARTFABOracle.ExecuteNonQuery()
        sql_transaction.Commit()
        sql_transaction.Dispose()
        MsgBox("to")
        MycommandSuppressionZARTFABOracle.Dispose()
        sql_transaction = conx.BeginTransaction()
 
        Try
            While readerDataZARTFABprogressODBC.Read()
 
                Dim daPrm_spc As New OracleCommand("insert into zartfab2 (" & _
                                    "csoc, cetab, cart, cdepot, nbjcons, nbjdluo, nbjgares, ancrecmax, qualmode " & _
                                        ", taffect, iper, ccouts, tofcout, destock " & _
                                        ", tach, tfab,dfabd,dfabf, cartent, cartsor, typfluxof, txfr, crgesof " & _
                                        ", tmpdes, trupdp, trupcbn, tepdp, tecri, tfabjour,heurfin, qdelai,cuqdel, decaldeb, cartref " & _
                                        ", nbjgarss, qualite, durcon, cstatutblo, cstatutdis, cstatuthl " & _
                                        ", tpfass, tdebloman, trecate " & _
                                        ") values " & _
                                        "(" & _
                                        ":csoc, :cetab, :cart, :cdepot, :nbjcons, :nbjdluo, :nbjgares, :ancrecmax, :qualmode " & _
                                        ", :taffect, :iper, :ccouts, :tofcout, :destock " & _
                                        ", :tach, :tfab, :dfabd, :dfabf, :cartent, :cartsor, :typfluxof, :txfr, :crgesof " & _
                                        ", :tmpdes, :trupdp, :trupcbn, :tepdp, :tecri, :tfabjour,'29/11/2010', :qdelai, :cuqdel, :decaldeb, :cartref " & _
                                        ", :nbjgarss, :qualite, :durcon, :cstatutblo, :cstatutdis, :cstatuthl " & _
                                        ", :tpfass, :tdebloman, :trecate " &
                                        ")", conx)
 
                daPrm_spc.Transaction = sql_transaction
 
                daPrm_spc.Parameters.Add(New OracleParameter("csoc", OleDbType.VarChar)).Value = UCase(readerDataZARTFABprogressODBC.Item("csoc"))
                daPrm_spc.Parameters.Add(New OracleParameter("cetab", OleDbType.VarChar)).Value = UCase(readerDataZARTFABprogressODBC.Item("cetab"))
                daPrm_spc.Parameters.Add(New OracleParameter("cart", OleDbType.VarChar)).Value = UCase(readerDataZARTFABprogressODBC.Item("cart"))
                daPrm_spc.Parameters.Add(New OracleParameter("cdepot", OleDbType.VarChar)).Value = UCase(readerDataZARTFABprogressODBC.Item("cdepot"))
                daPrm_spc.Parameters.Add(New OracleParameter("nbjcons", OleDbType.VarChar)).Value = nbjcons
                daPrm_spc.Parameters.Add(New OracleParameter("nbjdluo", OleDbType.VarChar)).Value = nbjdluo
                daPrm_spc.Parameters.Add(New OracleParameter("nbjgares", OleDbType.VarChar)).Value = nbjgares
                daPrm_spc.Parameters.Add(New OracleParameter("ancrecmax", OleDbType.VarChar)).Value = ancrecmax
                daPrm_spc.Parameters.Add(New OracleParameter("qualmode", OleDbType.VarChar)).Value = UCase(readerDataZARTFABprogressODBC.Item("qualmode"))
                daPrm_spc.Parameters.Add(New OracleParameter("taffect", OleDbType.VarChar)).Value = IIf(readerDataZARTFABprogressODBC.Item("taffect") = True, 1, 0)
                daPrm_spc.Parameters.Add(New OracleParameter("iper", OleDbType.VarChar)).Value = UCase(readerDataZARTFABprogressODBC.Item("iper"))
                daPrm_spc.Parameters.Add(New OracleParameter("ccouts", OleDbType.VarChar)).Value = UCase(readerDataZARTFABprogressODBC.Item("ccouts"))
                daPrm_spc.Parameters.Add(New OracleParameter("tofcout", OleDbType.VarChar)).Value = IIf(readerDataZARTFABprogressODBC.Item("tofcout") = True, 1, 0)
                daPrm_spc.Parameters.Add(New OracleParameter("destock", OleDbType.VarChar)).Value = UCase(readerDataZARTFABprogressODBC.Item("destock"))
                daPrm_spc.Parameters.Add(New OracleParameter("tach", OleDbType.VarChar)).Value = IIf(readerDataZARTFABprogressODBC.Item("tach") = True, 1, 0)
                daPrm_spc.Parameters.Add(New OracleParameter("tfab", OleDbType.VarChar)).Value = IIf(readerDataZARTFABprogressODBC.Item("tfab") = True, 1, 0)
                daPrm_spc.Parameters.Add(New OracleParameter("dfabf", OleDbType.VarChar)).Value = readerDataZARTFABprogressODBC.Item("dfabf")
                daPrm_spc.Parameters.Add(New OracleParameter("dfabd", OleDbType.VarChar)).Value = readerDataZARTFABprogressODBC.Item("dfabd")
                daPrm_spc.Parameters.Add(New OracleParameter("cartent", OleDbType.VarChar)).Value = UCase(readerDataZARTFABprogressODBC.Item("cartent"))
                daPrm_spc.Parameters.Add(New OracleParameter("cartsor", OleDbType.VarChar)).Value = UCase(readerDataZARTFABprogressODBC.Item("cartsor"))
                daPrm_spc.Parameters.Add(New OracleParameter("typfluxof", OleDbType.VarChar)).Value = UCase(readerDataZARTFABprogressODBC.Item("typfluxof"))
                daPrm_spc.Parameters.Add(New OracleParameter("txfr", OleDbType.VarChar)).Value = txfr
                daPrm_spc.Parameters.Add(New OracleParameter("crgesof", OleDbType.VarChar)).Value = UCase(readerDataZARTFABprogressODBC.Item("crgesof"))
                daPrm_spc.Parameters.Add(New OracleParameter("tmpdes", OleDbType.VarChar)).Value = IIf(readerDataZARTFABprogressODBC.Item("tmpdes") = True, 1, 0)
                daPrm_spc.Parameters.Add(New OracleParameter("trupdp", OleDbType.VarChar)).Value = IIf(readerDataZARTFABprogressODBC.Item("trupdp") = True, 1, 0)
                daPrm_spc.Parameters.Add(New OracleParameter("trupcbn", OleDbType.VarChar)).Value = IIf(readerDataZARTFABprogressODBC.Item("trupcbn") = True, 1, 0)
                daPrm_spc.Parameters.Add(New OracleParameter("tepdp", OleDbType.VarChar)).Value = IIf(readerDataZARTFABprogressODBC.Item("tepdp") = True, 1, 0)
                daPrm_spc.Parameters.Add(New OracleParameter("tecri", OleDbType.VarChar)).Value = IIf(readerDataZARTFABprogressODBC.Item("tecri") = True, 1, 0)
                daPrm_spc.Parameters.Add(New OracleParameter("tfabjour", OleDbType.VarChar)).Value = IIf(readerDataZARTFABprogressODBC.Item("tfabjour") = True, 1, 0)
                daPrm_spc.Parameters.Add(New OracleParameter("qdelai", OleDbType.VarChar)).Value = qdelai
                daPrm_spc.Parameters.Add(New OracleParameter("decaldeb", OleDbType.VarChar)).Value = readerDataZARTFABprogressODBC.Item("decaldeb")
                daPrm_spc.Parameters.Add(New OracleParameter("cuqdel", OleDbType.VarChar)).Value = UCase(readerDataZARTFABprogressODBC.Item("cuqdel"))
                daPrm_spc.Parameters.Add(New OracleParameter("cartref", OleDbType.VarChar)).Value = UCase(readerDataZARTFABprogressODBC.Item("cartref"))
                daPrm_spc.Parameters.Add(New OracleParameter("nbjgarss", OleDbType.VarChar)).Value = nbjgarss
                daPrm_spc.Parameters.Add(New OracleParameter("qualite", OleDbType.VarChar)).Value = IIf(readerDataZARTFABprogressODBC.Item("qualite") = True, 1, 0)
                daPrm_spc.Parameters.Add(New OracleParameter("durcon", OleDbType.VarChar)).Value = durcon
                daPrm_spc.Parameters.Add(New OracleParameter("cstatutblo", OleDbType.VarChar)).Value = readerDataZARTFABprogressODBC.Item("cstatutblo")
                daPrm_spc.Parameters.Add(New OracleParameter("cstatutdis", OleDbType.VarChar)).Value = readerDataZARTFABprogressODBC.Item("cstatutdis")
                daPrm_spc.Parameters.Add(New OracleParameter("cstatuthl", OleDbType.VarChar)).Value = readerDataZARTFABprogressODBC.Item("cstatuthl")
                daPrm_spc.Parameters.Add(New OracleParameter("tpfass", OleDbType.VarChar)).Value = IIf(readerDataZARTFABprogressODBC.Item("tpfass") = True, 1, 0)
                daPrm_spc.Parameters.Add(New OracleParameter("tdebloman", OleDbType.VarChar)).Value = IIf(readerDataZARTFABprogressODBC.Item("tdebloman") = True, 1, 0)
                daPrm_spc.Parameters.Add(New OracleParameter("trecate", OleDbType.VarChar)).Value = IIf(readerDataZARTFABprogressODBC.Item("tdebloman") = True, 1, 0)
                daPrm_spc.ExecuteNonQuery()
 
            End While
            sql_transaction.Commit()
 
        Catch ex As Exception
            sql_transaction.Rollback()
        Finally
            sql_transaction.Dispose()
            conx.Close()
            conx.Dispose()
            conx = Nothing
 
            Cnx_Base.Close()
            Cnx_Base.Dispose()
            Cnx_Base = Nothing
            readerDataZARTFABprogressODBC.Dispose()
        End Try

**Pol63** · 21/07/2011, 09h57

si tu fais un insert d'une table à une autre, ca ne sert à rien de passer par vb, tu peux faire insert into table1 [champs] select [champs de table2 et/ou valeurs fixées depuis vb] from table2
déjà avec ca tu devrais gagner pas mal de temps

**jlm22** · 21/07/2011, 10h00

C'est pour un ETL, donc je passe par VB

**SaumonAgile** · 21/07/2011, 10h16

Envoyé par jlm22

C'est pour un ETL, donc je passe par VB

Ce qu'il veut dire, c'est qu'au lieu de passer par un data reader puis d'executer les insert un par un en reprenant les données du reader, tu pourrais envisager de faire quelque chose comme ça:

Code :

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

1
2
INSERT INTO zartfab2 [CHAMPS]
SELECT [CHAMPS] FROM [TABLE]

Code :

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

SELECT [DATA] FROM [TABLE]

représente la requête de ton reader initial

**jlm22** · 21/07/2011, 10h35

Excusez-moi mais sa ne répond pas à ma question ! Pourquoi faisant un delete dans ma transaction je me retrouve avec 10 min d'exécution et en faisant un dispose entre les 2 j'ai seulement 2 min.

**Pol63** · 21/07/2011, 10h44

si les données viennent de 2 bases, il doit être possible de les lier pour ne pas passer par vb (gain de temps) (insert into base1.table1 from base2.table1) (c'est possible sur sql server, voir sur le forum oracle pour demander)

certes ca ne répond toujours pas à ta question ^^
enfin qu'une transaction allonge le temps d'exécution ce n'est pas nouveau, et je ne connais pas oracle spécialement, donc je ne sais pas si ce écart plutôt conséquent est logique

**jlm22** · 21/07/2011, 10h47

Je me demande si sa ne viendrait pas du fait que le delete enregistre toutes la table dans un tablespace afin de pouvoir faire un rollback du coup il fait en gros 2 fois l'insert. ce qui expliquerait aussi que lorsque je fait un truncate je retombe à 3 min car il ne fait pas d'enregistrement.
un avis ?

**Pol63** · 21/07/2011, 10h52

le principe d'une transaction est en effet de pouvoir faire un rollback, donc tout est enregistré, il y a aussi des verrous de posés de partout (les verrous ralentissent/bloquent les autres requetes extérieures)

si tu n'as pas besoin de rollbacker, ne fait pas de transaction
si tu n'as pas besoin de rollbacker le delete, fait le avant de démarrer la transaction
je pense que le truncate table ne peut pas être rollbacké (à tester)

sur sql server, truncate table ne journalise pas les suppressions, ce qui donc plus rapide qu'un delete (truncate table même de millions de lignes est instantané)

**jlm22** · 21/07/2011, 10h58

En gros il faudrait que puisse :

Code :

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

1
2
3
4
5
6
 
Delete
 
Insert
          si insert a fonctionner on commit tout
          sinon on fait un rollback

c'est possible sans transaction de faire un rollback du delete ? (je suis obligé de passé par VB)

**Pol63** · 21/07/2011, 11h16

tu mets tout ce qu'il y a dans la table dans une autre
tu delete dans la vraie
tu insert
si ok détruire/vider table "temporaire"
si pas ok remettre depuis table "temporaire"

mais il faut faire gaffe sur la sécurisation
avec une transaction tu évites tous les problèmes, perte de connexion, perte de courant électrique etc...
parce que là si tu déplace les données dans un table temporaire et que tu coupes le courant, la table temporaire n'existera plus

donc garde la transaction

je suis obligé de passer par VB

c'est bien dommage car pas logique ...