Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsWhite Papers
Discussion GroupsFirst AidDatabasesJavaBeansGUIJava 3DVirtual MachineCORBASecurityToolsGeneral
Java DirectoryOpen Source ProjectsSample Book ChaptersUser GroupsWeb Resources
Related Topics
Databases.NETMore Topics ...

Java Forum / General / June 2005

Tip: Looking for answers? Try searching our database.

speeding up a program

Thread view: 
redgambit - 28 Jun 2005 22:56 GMT
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 = "jdbc:odbc:Database";
           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 - 28 Jun 2005 23:45 GMT
> Hello,
> I have a program that queries an MS Access DB then fills in the
[quoted text clipped - 4 lines]
> 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 = "jdbc:odbc:Database";
>             Connection db_connection =
[quoted text clipped - 3 lines]
>             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 - 29 Jun 2005 00:25 GMT
> 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.

Signature

Daniel Dyer
http://www.footballpredictions.net

shakah - 29 Jun 2005 02:05 GMT
> Hello,
> I have a program that queries an MS Access DB then fills in the
[quoted text clipped - 36 lines]
> 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 - 29 Jun 2005 10:19 GMT
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 - 29 Jun 2005 13:14 GMT
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 - 29 Jun 2005 15:34 GMT
Thanks for all your help, performence is much improved.
Thank You!
Lee Fesperman - 29 Jun 2005 21:05 GMT
> 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.

Signature

Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS  (http://www.firstsql.com)

Roedy Green - 30 Jun 2005 07:27 GMT
>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

Signature

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



Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.