java - Jackcess，具有多个匹配行的循环非常慢，

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("\n\nEnd loop time : '%s'", 
                             System.currentTimeMillis( )));
          long diff = end - start;

          System.out.println(String.format("Loop elapsed time : '%s' ms\n\n", 
                             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

我怀疑您仍在有效地扫描整个 [InvoicesLignes] 表以获取外部 [Invoices] 循环的每次迭代。你的方法...

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，而不是直接使用 Jackcess API。

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

Manipulating an Access database from Java without ODBC