Re: Slow Batch Utility

Subject: Re: Slow Batch Utility

From: Steven Mark McCraw <email@hidden>

Date: Fri, 25 May 2007 10:26:27 -0400

Delivered-to: email@hidden

Working directly at the JDBC level is really quick, particularly if you use connection pooling. Apache's dbcp library has worked great for me, and it's fairly easy to use (although configuration is a little bit of a pain). Here's how I use it:

In your application, create a ConnectionPool (you can configure this any way you want):

GenericObjectPool connectionPool = new GenericObjectPool(null);

connectionPool.setMaxWait((long)5000);

connectionPool.setWhenExhaustedAction(GenericObjectPool.WHEN_EXHAUSTED_GROW);

connectionPool.setTimeBetweenEvictionRunsMillis(60000);

connectionPool.setMaxActive( 20);

connectionPool.setMaxIdle( 10);

connectionPool.setMinEvictableIdleTimeMillis( 60000 );

ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(url, username, password);

PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,true);

dataSource = new PoolingDataSource(connectionPool);

To make sure all the connections close properly when the application exits, I override _terminateOrResetTimer() as well:

public void _terminateOrResetTimer() {

try {

connectionPool.close();

} catch (Exception e) {

}

System.exit(0);

}

Finally, create a convenience method to get a connection from the pool:

public static Connection getConnection() {

try {

return dataSource.getConnection();

} catch (Exception e) {

NSLog.out.appendln(ErrorStackTrace.toString(e));

}

return null;

}

Now whenever you need to do things with the database, you just need to get a connection from the pool, create a statement with it, and then use that statement to execute your queries/updates:

Connection connection = null;

Statement statement = null;

ResultSet result = null;

try {

connection = Application.getConnection();

Statement statement = connection.createStatement();

String sql = "update something set something where something";

ResultSet result = statement.executeQuery(sql);

if (!result.first()) {

sql = "select max(" + column + ")" + column + " from " + table;

result = statement.executeQuery(sql);

result.first();

if (result.getInt(column) == 0) {

next = startValue;

} else {

next = result.getInt(column) + 1;

}

sql = "insert into something values('" + someValues + "', 0)";

statement.executeUpdate(sql);

}

} catch (SQLException e) {

...

} finally {

try {

result.close();

statement.close();

// closing the connection frees it back to the pool

connection.close();

} catch (SQLException f) {

...

}

This code is mostly from memory, so no guarantees, but I believe the concepts are all right.

I find it kind of frustrating and poorly designed that for every single thing you do throws a SQL exception that you either have to throw yourself or handle in some way. I can appreciate the reasoning behind it (database connection are probably more prone to fail than most things), but in my experience, failure rarely occurs and it makes coding a bit of a pain. You can write subclasses of Connection, Statement, and ResultSet that handle the exceptions in a consistent way if you like without having to catch exceptions all over the place.

Hope this helps.

Mark

On May 25, 2007, at 9:59 AM, Guido Neitzer wrote:

On 25.05.2007, at 01:22, Ted Archibald wrote:

I have a webobjects accounts receivable application that I have a batch utility that changes certain fields everynight. I'm having issues with the speed of the batch utility and I suspect the slowness is due to having to access the full EO.

Is there a way to change the value of a field in an EO WITHOUT having access to the full EO? I know you can do this through a stored procedure, but I would rather not do that. Is there a way to do this in WebObjects?

In addition to what Kieran said:

You can also use raw SQL. Look at EOUtilities.rawRowsForSQL for this. You can fetch raw rows, but you can also execute standard SQL inserts or updates.

You can also go directly to the JDBC level and work there ...

cug
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/email@hidden

This email sent to email@hidden

_______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (email@hidden) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/email@hidden This email sent to email@hidden

References:

>Slow Batch Utility (From: "Ted Archibald" <email@hidden>)

>Re: Slow Batch Utility (From: Guido Neitzer <email@hidden>)