speeding up a program

Discussion in 'Java' started by redgambit, Jun 28, 2005.

  1. redgambit

    redgambit Guest

    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:eek:dbc: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.
     
    redgambit, Jun 28, 2005
    #1
    1. Advertising

  2. redgambit

    Alan Krueger Guest

    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 = "jdbc:eek:dbc:Database";
    > 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.
     
    Alan Krueger, Jun 28, 2005
    #2
    1. Advertising

  3. redgambit

    Daniel Dyer Guest

    On Tue, 28 Jun 2005 22:56:39 +0100, 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.


    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
     
    Daniel Dyer, Jun 29, 2005
    #3
  4. redgambit

    shakah Guest

    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 = "jdbc:eek:dbc: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.


    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).
     
    shakah, Jun 29, 2005
    #4
  5. redgambit

    CodeFutures Guest

    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.
     
    CodeFutures, Jun 29, 2005
    #5
  6. redgambit

    Sridhar Guest

    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.
     
    Sridhar, Jun 29, 2005
    #6
  7. redgambit

    redgambit Guest

    Thanks for all your help, performence is much improved.
    Thank You!
     
    redgambit, Jun 29, 2005
    #7
  8. 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)
     
    Lee Fesperman, Jun 29, 2005
    #8
  9. redgambit

    Roedy Green Guest

    On 28 Jun 2005 14:56:39 -0700, "redgambit" <>
    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/mckinney_grills_rumsfeld.htm

    Canadian Mind Products, Roedy Green.
    See http://mindprod.com/iraq.html photos of Bush's war crimes
     
    Roedy Green, Jun 30, 2005
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Spamtrap

    Need some hints on speeding up

    Spamtrap, Aug 11, 2004, in forum: Perl
    Replies:
    1
    Views:
    378
    Jim Gibson
    Aug 12, 2004
  2. Troy

    Speeding up page display

    Troy, Jan 21, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    524
    George Ter-Saakov
    Jan 22, 2004
  3. OHM

    Speeding up pages

    OHM, May 24, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    371
  4. =?Utf-8?B?TWF0dA==?=

    Speeding up a DataGrid with XML

    =?Utf-8?B?TWF0dA==?=, May 20, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    479
    =?Utf-8?B?TWF0dA==?=
    May 20, 2005
  5. kid joe

    Speeding up a program

    kid joe, Jun 25, 2008, in forum: C Programming
    Replies:
    2
    Views:
    255
Loading...

Share This Page