Jackess，循环与多个匹配的行非常慢，

Question

我在代码中执行了这一点，我不明白为什么循环的运行时间很长。

有两个循环。Invoices表上的第一个循环和InvoicesLignes表上的第二个循环。

InvoicesLignes表有一个索引"Code"，它是Invoice的"外键"。在数据库Access中，Invoices和InvoicesLines之间存在关系，但没有定义外键，我不能使用Joiner类。我无法修改数据库。

import java.io.File;
import java.util.Date;
import java.io.IOException;
import com.healthmarketscience.jackcess.*;
class MultipleLoop {
  public static void main(String[] args) {
    File jfile = new File("/project/database.mdb");
    Date start_date= new Date("2013/01/01");
    Date end_date= new Date("2013/12/31");
    try {
        Database db = DatabaseBuilder.open(jfile);
        Table invoices = db.getTable("Invoices");
        Table invoices_lignes = db.getTable("InvoiceLignes");
        Cursor cursor = CursorBuilder.createCursor(invoices_lignes.getIndex("Code"));
        for(Row invoice : invoices) {
          if ((start_date.before((Date)invoice.get("Date")) 
                    || start_date.equals((Date)invoice.get("Date"))) 
              &&  
              (end_date.after((Date)invoice.get("Date")) 
                    || end_date.equals((Date)invoice.get("Date")) )) {
          System.out.println(String.format("Invoice id : '%s' time : '%s'", 
                             invoice.get("Code"),
                             System.currentTimeMillis( ) ));
          long start = System.currentTimeMillis( );
          for (Row invoice_ligne : cursor.newIterable().addMatchPattern("Code",
                                                           invoice.get("Code"))) 
          {
              System.out.println(String.format("Loop time : '%s'", 
                                 System.currentTimeMillis( )));
          }
          long end = System.currentTimeMillis( );
          System.out.println(String.format("nnEnd loop time : '%s'", 
                             System.currentTimeMillis( )));
          long diff = end - start;
          System.out.println(String.format("Loop elapsed time : '%s' msnn", 
                             diff ));
          }
        }
       } catch (IOException e) {
      //} catch (Exception e) {
        System.out.println(e);
      }
    }
}

我的日志是

Invoice id : '19901/79018' time : '1411043140236' 
Loop time : '1411043140237'
Loop time : '1411043140237'
Loop time : '1411043140237'
Loop time : '1411043140237'

End loop time : '1411043141335'
Loop elapsed time : '1098' ms

 Invoice id : '138901/909719' time : '1411043141335'
 Loop time : '1411043141336'
 Loop time : '1411043141336'

 End loop time : '1411043142418'
 Loop elapsed time : '1083' ms

 Invoice id : '1309091/729090' time : '1411043142419'
 Loop time : '1411043142419'
 Loop time : '1411043142419'

 End loop time : '1411043143515'
 Loop elapsed time : '1096' ms

我不明白为什么循环的运行时间很高

感谢您的帮助

Answer 1

我怀疑您仍然在有效地扫描整个[InvicesLignes]表，以查找外部[Invices]循环的每次迭代。你的方法。。。

Table invoices = db_opened.getTable("Invoices");
Table invoices_lignes = db_opened.getTable("InvoicesLignes");
Cursor cursor = CursorBuilder.createCursor(invoices_lignes.getIndex("Code"));
for (Row row : invoices) {
    long start = System.currentTimeMillis( );
    System.out.println(String.format("Invoice : '%s'", start));
    for (Row crow : cursor.newIterable().addMatchPattern("Code", row.get("Code"))) {
        System.out.println(String.format("Loop : '%s'", System.currentTimeMillis( )));
    }
    long end = System.currentTimeMillis( );
    System.out.println(String.format("End loop : '%s'", System.currentTimeMillis( )));
    long diff = end - start;
    System.out.println(String.format("Loop elapsed time : '%s' ms", diff ));
}

生产：

Invoice : '1411041092492'
Loop : '1411041092527'
Loop : '1411041092528'
Loop : '1411041092529'
End loop : '1411041094440'
Loop elapsed time : '1948' ms
Invoice : '1411041094442'
Loop : '1411041094443'
Loop : '1411041094444'
End loop : '1411041095882'
Loop elapsed time : '1440' ms

然而，这种变化。。。

Table invoices = db_opened.getTable("Invoices");
Table invoices_lignes = db_opened.getTable("InvoicesLignes");
for (Row row : invoices) {
    long start = System.currentTimeMillis( );
    System.out.println(String.format("Invoice : '%s'", start));
    IndexCursor cursor = new CursorBuilder(invoices_lignes)
            .setIndexByName("Code")
            .setSpecificEntry(row.get("Code"))
            .toIndexCursor();
    for (Row crow : cursor) {
        System.out.println(String.format("Loop : '%s'", System.currentTimeMillis( )));
    }
    long end = System.currentTimeMillis( );
    System.out.println(String.format("End loop : '%s'", System.currentTimeMillis( )));
    long diff = end - start;
    System.out.println(String.format("Loop elapsed time : '%s' ms", diff ));
}

生产：

Invoice : '1411041030007'
Loop : '1411041030060'
Loop : '1411041030062'
Loop : '1411041030063'
End loop : '1411041030063'
Loop elapsed time : '56' ms
Invoice : '1411041030067'
Loop : '1411041030069'
Loop : '1411041030070'
End loop : '1411041030071'
Loop elapsed time : '4' ms

此外，您是否考虑过使用UCanAccess？相应的UCanAccess代码。。。

String dbFileSpec = "C:/Users/Gord/Desktop/InvoiceTest.mdb";
try (Connection conn=DriverManager.getConnection("jdbc:ucanaccess://" + dbFileSpec)) {
    PreparedStatement psLignes = conn.prepareStatement("SELECT * FROM InvoicesLignes WHERE Code=?");
    Statement sInv = conn.createStatement();
    ResultSet rsInv = sInv.executeQuery("SELECT Code FROM Invoices");
    while (rsInv.next()) {
        long start = System.currentTimeMillis( );
        System.out.println(String.format("Invoice : '%s'", start));
        psLignes.setString(1, rsInv.getString("Code"));
        ResultSet rsLignes = psLignes.executeQuery();
        while (rsLignes.next()) {
            System.out.println(String.format("Loop : '%s'", System.currentTimeMillis()));
        }
        long end = System.currentTimeMillis( );
        System.out.println(String.format("End loop : '%s'", System.currentTimeMillis( )));
        long diff = end - start;
        System.out.println(String.format("Loop elapsed time : '%s' ms", diff ));
    }
    conn.close();
} catch (Exception e) {
    e.printStackTrace(System.out);
}

生产：

Invoice : '1411041417462'
Loop : '1411041417478'
Loop : '1411041417478'
Loop : '1411041417479'
End loop : '1411041417479'
Loop elapsed time : '17' ms
Invoice : '1411041417482'
Loop : '1411041417483'
Loop : '1411041417483'
End loop : '1411041417483'
Loop elapsed time : '1' ms

UCanAccess可以增加Java应用程序的启动时间，因为它将Access数据复制到hsqldb"镜像"数据库（默认情况下在内存中）中，但一旦加载，它就会非常快，我们可以使用常规SQL，而不是直接使用Jackprocess API。

有关UCanAccess的更多信息，请参阅

在没有ODBC 的情况下从Java操作Access数据库