Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > speeding up a program

Reply
Thread Tools

speeding up a program

 
 
redgambit
Guest
Posts: n/a
 
      06-28-2005
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.

 
Reply With Quote
 
 
 
 
Alan Krueger
Guest
Posts: n/a
 
      06-28-2005
redgambit wrote:
> 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.
 
Reply With Quote
 
 
 
 
Daniel Dyer
Guest
Posts: n/a
 
      06-28-2005
On Tue, 28 Jun 2005 22:56:39 +0100, redgambit <(E-Mail Removed)>
wrote:

> 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.

--
Daniel Dyer
http://www.footballpredictions.net
 
Reply With Quote
 
shakah
Guest
Posts: n/a
 
      06-29-2005
redgambit wrote:
> 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).

 
Reply With Quote
 
CodeFutures
Guest
Posts: n/a
 
      06-29-2005

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.

 
Reply With Quote
 
Sridhar
Guest
Posts: n/a
 
      06-29-2005
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.

 
Reply With Quote
 
redgambit
Guest
Posts: n/a
 
      06-29-2005
Thanks for all your help, performence is much improved.
Thank You!

 
Reply With Quote
 
Lee Fesperman
Guest
Posts: n/a
 
      06-29-2005
redgambit wrote:
>
> 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.

--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
================================================== ============
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)
 
Reply With Quote
 
Roedy Green
Guest
Posts: n/a
 
      06-30-2005
On 28 Jun 2005 14:56:39 -0700, "redgambit" <(E-Mail Removed)>
wrote or quoted :

>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/...s_rumsfeld.htm

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

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Speeding up a program kid joe C Programming 2 06-27-2008 08:48 AM
[ot]Valid excuse for speeding? JaR MCSE 9 01-05-2005 08:23 PM
speeding up data transfer? Devin Panchal Wireless Networking 1 09-06-2004 05:46 PM
Need some hints on speeding up Spamtrap Perl 1 08-11-2004 11:25 PM
Speeding up page display Troy ASP .Net 2 01-22-2004 09:13 PM



Advertisments