Solved

ADO: do I need to explicitly run a "commit" statement during or after a transaction?

Posted on 2009-07-10
1
324 Views
Last Modified: 2013-12-19
Hi Experts:

    In a piece of Python code using ADO, I execute a sql statement execution command inside a transaction for 500000 times like the following code. Theoretically I guess the changes will  be saved after the transaction without explicitly running con.Execute("commit") during or after the transaction.

1. Is my guess correct true?
2. Will there be any problem caused by this "LARGE" transaction if I do it this way?

Thank you!


# Python code 1:
# con is an ADO connection object, i is an integer
con.BeginTrans()
i = 0
while (i<500000):
    con.Execute("insert into table_name (field_a, field_b) values (1,2)")
    i = i + 1
con.CommitTrans()

Open in new window

0
Comment
Question by:huangs3
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 500 total points
ID: 24827537
I see you have con.CommitTrans() already. I do not know ADO / Python so well, but if that is what I think it is, there is no need for an explicit "commit". Actually all APIs I know have a Commit() function or overall transaction API, you never have to say "commit"

One large transaction is fine, especially if the operation is supposed to be atomic. It depends on what you want to happen if you get an error (roll back?).

Too many commits will degrade performance, but too large / long running transactions can also use UNDO space if there are other queries running against the dataset that is being changed. I could commit in batches of say 50000, but I would just leave it alone and commit at the end unless you otherwise see some sort of problem.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Checking for column width 8 48
python - user input - set 4 77
Oracle SQL Developer - SubString 2 49
Query for non-UTF8 characters in a column in Oracle 11 30
The purpose of this article is to demonstrate how we can upgrade Python from version 2.7.6 to Python 2.7.10 on the Linux Mint operating system. I am using an Oracle Virtual Box where I have installed Linux Mint operating system version 17.2. Once yo…
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question