Java Reference
In-Depth Information
If the start and end dates represent the year 2013, this loop will insert 261 rows into the
STOCKPRICE table (via the first call to the executeUpdate() method) and 1,305 rows into
the STOCKOPTIONPRICE table (in the inner for loop). In the default autocommit mode,
that means 1,566 separate transactions, which will be quite expensive.
Better performance will be achieved if autocommit mode is disabled and an explicit commit
is performed at the end of the loop:
Connection c = DriverManager . getConnection ( URL , user , pw );
c . setAutoCommit ( false
false );
...
while
while (! curDate . after ( endDate )) {
...
}
c . commit ();
From a logical point of view, that probably makes sense as well: the database will end up
with either an entire year's worth of data, or no data.
If this loop is repeated for multiple stocks, there is a choice of committing all the data at once
or committing a year's worth of data at a time:
Connection c = DriverManager . getConnection ( URL , user , pw );
c . setAutoCommit ( false
false );
for
for ( int
int i = 0 ; i < numStocks ; i ++) {
curDate = startDate ;
while
while (! curdate . after ( endDate )) {
...
}
//c.commit(); // To commit a year at a time
}
c . commit ();
// To commit all the data at once
Committing all the data at once offers the fastest performance, which is why the other option
is commented out. In this example, though, there is a reasonable case where the application
semantics might dictate that each year of data be committed individually. Sometimes, other
requirements intrude on attempts to get the best performance.
Each time the executeUpdate() method is executed in the above code, a remote call is
made to the database and some work must be performed. In addition, some locking will oc-
cur when the updates are made (to ensure, at least, that another transaction cannot insert a re-
Search WWH ::




Custom Search