executequery running extremely slow with sort of complicated query

[bran]

Hi everyone - I have a query that selects multiple fields from several different tables using an inner join and two left joins. When I run the exact query in access, the results are instant. When it's run using ucanaccess, it takes nearly four minutes. Can anyone help? Code:

@Test
public void read_test() throws SQLException{
    Connection conn = null;
    Statement s = null;
    ResultSet rs = null; 
    try
   {
        conn = DriverManager.getConnection("jdbc:ucanaccess:localpath/to/db.accdb;keepMirror=C:/mirror/path/theMirror1");
        s = conn.createStatement();
        String qry = "SELECT [AGRMNT-REF], PRIORITY, VENDOR, TYPE, [BASE-COST], UOM, COMPANY, [BASE-PERCENT] FROM ((AGRMNTHDR AS H INNER JOIN AGRMNTLINE AS L ON "
                + "L.[AGRMNT-REF]=H.[AGRMNT-REF]) LEFT JOIN POAGRMNTPART P ON L.[AGRMNT-REF]=p.[AGRMNT-REF]) LEFT JOIN POAGRMNTPERCENTAGE PP on "
                + "L.[AGRMNT-REF]=PP.[AGRMNT-REF]";

        rs = s.executeQuery(qry);
       while(rs.next())
       {
           System.out.println(rs.getString("UOM"));
       }

   }
      finally{
          s.close();
          conn.close();
      }

}

[Marco Amadei]

Firstly. you have to pay attention: if the access db is updated in any way after the mirror db has been cteated, the mirror db will be re-created at the next connection. So please, measure just the query executuion time:

@Test
public void read_test() throws SQLException{
    Connection conn = null;
    Statement s = null;
    ResultSet rs = null; 
    try
   {
        conn = DriverManager.getConnection("jdbc:ucanaccess:localpath/to/db.accdb;keepMirror=C:/mirror/path/theMirror1");
        s = conn.createStatement();
   long time = System.currentTimeMillis();
        String qry = "SELECT [AGRMNT-REF], PRIORITY, VENDOR, TYPE, [BASE-COST], UOM, COMPANY, [BASE-PERCENT] FROM ((AGRMNTHDR AS H INNER JOIN AGRMNTLINE AS L ON "
                + "L.[AGRMNT-REF]=H.[AGRMNT-REF]) LEFT JOIN POAGRMNTPART P ON L.[AGRMNT-REF]=p.[AGRMNT-REF]) LEFT JOIN POAGRMNTPERCENTAGE PP on "
                + "L.[AGRMNT-REF]=PP.[AGRMNT-REF]";

        rs = s.executeQuery(qry);
        long time1 = System.currentTimeMillis();
            System.out
                    .println("time in millisec:"
                            + (time1 - time) );
       while(rs.next())
       {
           System.out.println(rs.getString("UOM"));
       }

   }
      finally{
          s.close();
          conn.close();
      }

}

[bran]

Hey Marco - the db hasn't been updated and the output shows a time of: 268394 milliseconds

[Gord Thompson]

Can you confirm that the AGRMNT-REF column is indexed in all four (4) tables? (If you open the database using "console.bat" or "console.sh" you should see the indexes listed as the database loads.)

[bran]

Here's the output

[Gord Thompson]

Certainly worth a try to add indexes on the [AGRMNT-REF] columns in the [POAGRMNTPART] and [POAGRMNTPERCENTAGE] tables to see if that helps.

[bran]

wow, that helped a ton! time now is 1790 millsecs, thanks!