The CPU results are shown in the following graph (Figure 2).įigure 2.
In our final measurement (500K rows), our new Excel JDBC Driver runs approximately 13x faster than the old ODBC-based setup.Īs above, we compared the original-type connection to connections using our new Excel JDBC driver on Excel files with 10 columns of data and a variable number of rows (10K, 50K, 100K, 300K, 500K rows). As the number of rows increases, the timing for the original ODBC driver setup increases quadratically with increasing row count, while the new Excel JDBC Driver timing increases linearly.
Data points cover the standard deviation (along the y-axis) of each measurement.Īs is apparent from the QueryPair timings shown in Figure 1, for small Excel files (50K rows or less), the drivers are nearly the same. Each measurement is an average of 3 runs. All data types are string types for all columns. Runtime test setup: Win 2008 R2 VM, 32 GB RAM, 3.5 GHz clock, 8 GB App server JVM Heap, 6 GB Agent JVM Heap, Agent query threads: 1, Agent Message Size: 3 MB, both Source and Target queries in QueryPair hit the same Excel file with the same query. Excel QueryPair Duration (sec): JDBC/ODBC bridge vs. The timing results are shown in the following graph (Figure 1).įigure 1. We compared the original-type ODBC/JDBC-bridge-based connection to connections using our new Excel JDBC driver on Excel files with 10 columns of data and a variable number of rows (10K, 50K, 100K, 300K, 500K rows). Our Excel JDBC driver uses the H2 SQL grammar and functions. You can write your Excel queries in a standard SQL syntax for greater flexibility and ease of maintenance.
What are the advantages of QuerySurge's new Excel JDBC Driver?
With Java 8, the builtin ODBC/JDBC bridge is no longer part of Java, so we are providing the new Excel JDBC Driver as part of QuerySurge, as a replacement. This driver uses a standard all-Java JDBC-based approach for connecting to Excel files, in contrast to QuerySurge's original approach, which used Java's builtin ODBC/JDBC bridge to connect to Excel files through Microsoft's Excel ODBC driver. With QuerySurge 6.3, a QuerySurge-specific Excel JDBC driver has been released for our users who, as part of their data testing, use Excel files as QueryPair Sources and/or Targets.