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