speeding up a program

[redgambit]

Hello,
I have a program that queries an MS Access DB then fills in the
approperate info in the program. I'm using the standard jdbc.odbc
bridge right now. And it is very slow. I was wondering if there is
anyway to speed it up.

String OComp = dbu.get("Company", "Database", "Zip", "'" + zipcode + "'
AND company='" + Company + "'");
....
public String get(String col, String table, String v1, String ref) {
String field = col;
sql = "SELECT "+ col +" FROM "+ table +" WHERE "+v1+"="+ref;
String comp = this.submit(sql, field);
return comp;
}
....
public String submit(String sql, String field) {
String out = new String();
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch (ClassNotFoundException cnfe) { // driver not found
System.err.println("Unable to load database driver");
System.err.println("Details : " + cnfe);
}

try {
String url = "jdbcdbcatabase";
Connection db_connection =
DriverManager.getConnection(url);
Statement stmt = db_connection.createStatement();
ResultSet result = stmt.executeQuery(sql);
if (result.next());
out = result.getString(field);
} catch (SQLException c) {
System.err.println(c);
}
return out;

I would like to keep my code as reusable as possible.
Thank you so much.

 

[Alan Krueger]

Hello,
I have a program that queries an MS Access DB then fills in the
approperate info in the program. I'm using the standard jdbc.odbc
bridge right now. And it is very slow. I was wondering if there is
anyway to speed it up.

String OComp = dbu.get("Company", "Database", "Zip", "'" + zipcode + "'
AND company='" + Company + "'");

In what context do you use this call? Do you call it frequently?

String url = "jdbcdbcatabase";
Connection db_connection =
DriverManager.getConnection(url);
Statement stmt = db_connection.createStatement();
ResultSet result = stmt.executeQuery(sql);
if (result.next());
out = result.getString(field);

You don't appear to close the connection you acquire above. You should
try to reuse an already-open connection where possible and close it when
you can't.

Also, you may want to use a PreparedStatement and use parameters, rather
than building the SQL strings manually.

I would like to keep my code as reusable as possible.
Thank you so much.

Maybe one of the DAO generation or object persistence frameworks might
work for you.

 

[Daniel Dyer]

Hello,
I have a program that queries an MS Access DB then fills in the
approperate info in the program. I'm using the standard jdbc.odbc
bridge right now. And it is very slow. I was wondering if there is
anyway to speed it up.

If you are making this query repeatedly then, as Alan suggests, you should
use a PreparedStatement and try to reuse connections. PreparedStatements
allow you to parameterise your query without having to re-parse the SQL
every time. Additionally, although it probably won't give you a
noticeable performance improvement, I would move the JDBC driver
initialisation code out of the submit method.

But, other than the PreparedStatement, the biggest improvement you could
make would be to ditch the ODBC bridge and get a proper JDBC driver. The
ODBC bridge is useful for interoperability but performance is not one of
its strong points. There is a commercial JDBC driver available for Access
but it would probably be better to save your money and change your
database instead. You can get MySQL and a JDBC driver for it for free and
the performance will be much better.

Dan.

 

[shakah]

Hello,
I have a program that queries an MS Access DB then fills in the
approperate info in the program. I'm using the standard jdbc.odbc
bridge right now. And it is very slow. I was wondering if there is
anyway to speed it up.

String OComp = dbu.get("Company", "Database", "Zip", "'" + zipcode + "'
AND company='" + Company + "'");
...
public String get(String col, String table, String v1, String ref) {
String field = col;
sql = "SELECT "+ col +" FROM "+ table +" WHERE "+v1+"="+ref;
String comp = this.submit(sql, field);
return comp;
}
...
public String submit(String sql, String field) {
String out = new String();
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch (ClassNotFoundException cnfe) { // driver not found
System.err.println("Unable to load database driver");
System.err.println("Details : " + cnfe);
}

try {
String url = "jdbcdbcatabase";
Connection db_connection =
DriverManager.getConnection(url);
Statement stmt = db_connection.createStatement();
ResultSet result = stmt.executeQuery(sql);
if (result.next());
out = result.getString(field);
} catch (SQLException c) {
System.err.println(c);
}
return out;

I would like to keep my code as reusable as possible.
Thank you so much.

How long does the query take in Access (natively)? It might be as
simple as adding an index on (Zip,company) to your table. Beyond that,
try adding debug stmts to your code, things like:

System.out.println(System.currentTimeMillis() + ": before
getConnection()") ;
Connection db_connection = DriverManager.getConnection(url) ;
System.out.println(System.currentTimeMillis() + ": before
createStatement()") ;
Statement stmt = db_connection.createStatement() ;
...etc...

At least then you'll have timing info to compare other approaches to
(e.g. if you replace the JDBC/ODBC bridge approach with a straight JDBC
driver).

 

[CodeFutures]

Some pointers
-MS Acces DB -

there's a few open source alternatives that will give you better
performance - MySQL, PostgreSQL, etc

-JDBC-ODBC -

switch over to straight JDBC

-keep code as reusbale as possible -

You could look at the Data Access Object design pattern

http://www.codefutures.com/data-access-object


In fact, you could use the FireStorm/DAO tool to import your Access
database schema, export it as a MySQL schema (there's usually some data
types to map), then generate JDBC DAOs from your MySQL schema. You
could get this all done, including downloading MySQL and FireStorm/DAO,
in a few hours since you'd be using a code generator. You could even
generate a JSP or Struts presentation tier to do end-to-end tests.

 

[Sridhar]

The use of an application server might do the trick too. open source
app servers like jboss allows u to just take a connection from its
connection pool.

 

[redgambit]

Thanks for all your help, performence is much improved.
Thank You!

 

[Lee Fesperman]

Hello,
I have a program that queries an MS Access DB then fills in the
approperate info in the program. I'm using the standard jdbc.odbc
bridge right now. And it is very slow. I was wondering if there is
anyway to speed it up.

You've already received effective comments on speeding up the code. I just wanted to
point out an error in your code...

ResultSet result = stmt.executeQuery(sql);
if (result.next());
out = result.getString(field);

The semicolon ( following the 'if' is incorrect and will cause a SQLException if the
resultset is empty.

Your original code was grossly inefficient, but it also indicated some bad habits. You
should always close Statements in JDBC, and it is good advice to close ResultSets also.

 

[Roedy Green]

I have a program that queries an MS Access DB then fills in the
approperate info in the program. I'm using the standard jdbc.odbc
bridge right now. And it is very slow. I was wondering if there is
anyway to speed it up.

Your problem is Access, a toy database. Upgrade to something with
more oomph. Just improving the bridge won't get you that far.

See http://mindprod.com/jgloss/sql.html


--
Bush crime family lost/embezzled $3 trillion from Pentagon.
Complicit Bush-friendly media keeps mum. Rumsfeld confesses on video.
http://www.infowars.com/articles/us/mckinney_grills_rumsfeld.htm

Canadian Mind Products, Roedy Green.
See http://mindprod.com/iraq.html photos of Bush's war crimes