Issue Details (XML | Word | Printable)

Key: JDBC-463
Type: Improvement Improvement
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Mark Rotteveel
Reporter: John Kilin
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Jaybird JCA/JDBC Driver

The Native/Local connection is too slow.

Created: 29/Nov/16 12:28 PM   Updated: 18/Dec/16 03:30 PM
Component/s: JDBC driver
Affects Version/s: Jaybird 3.0.0-beta-1
Fix Version/s: Jaybird 3.0.0-beta-2, Jaybird 3.0.0


 Description  « Hide
I try select 1 million records by jaybird-full-3.0.0-beta-1.jar.
In the pure java connection i have speed 40000 records per second.
But in the native/local connection i have speed 200 recrods per second.
On both Windows/Linux.
It's very different.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 30/Nov/16 09:52 AM - edited
There is some overhead because of JNA, but this is more than I expected, and doesn't really match the tests I did a few months ago. I will investigate. What is the DDL of the table you used for this test?

John Kilin added a comment - 30/Nov/16 11:04 AM
In table 75 fields:
CREATE TABLE TABLE1 (
    F1 INTEGER NOT NULL,
    F2 TIMESTAMP,
    F3 TIMESTAMP,
    F4 DATE NOT NULL,
    F5 DATE NOT NULL,
    F6 INTEGER NOT NULL,
    F7 INTEGER NOT NULL,
    F8 INTEGER NOT NULL,
    F9 INTEGER NOT NULL,
    F10 INTEGER NOT NULL,
    F11 NUMERIC(15,2) NOT NULL,
    F12 NUMERIC(15,2) NOT NULL,
    F13 NUMERIC(18,2),
    F14 NUMERIC(15,2) NOT NULL,
...
);

Mark Rotteveel added a comment - 03/Dec/16 10:19 AM
A quick & dirty comparison between pure java and native shows that in 2.2.x, native is 3x slower, in 3.0 it is 30x slower. It looks like I'll have to switch to use JNA direct mapping.

Mark Rotteveel added a comment - 05/Dec/16 05:03 PM
The test case I used reads 100,000 rows from a single table with 8 columns (some int, timestamp, date, numeric, char and varchar columns).

Jaybird 3 beta 1 pure java: +/- 900 ms
Jaybird 3 beta 1 native: +/- 58000 ms
Jaybird 2.2.11 pure Java: +/- 900 ms
Jaybird 2.2.11 native: +/- 3200 ms

Changing the implementation to JNA direct mapping did not help that much:

Jaybird 3 with JNA direct mapping: +/- 52000 ms

After some more reading it looks like JNA writes all Java values from a Structure to their native counterpart for each native invocation and reads them back after. This can be disabled, but then you need to manually take care of reading/write those fields. Implementing this changed the run time to +/- 1600 ms.

As I do not really like the extra complication of direct mapping, I also tried this without, and then the run time was +/- 2100 ms.

For now I will use this last solution (manually syncing structure fields, no direct mapping). If it turns out this is really not enough, I can still go to direct mapping. However the fact this seems to perform better than Jaybird 2.2 looks hopeful.

I will do some more testing, because clearly my earlier performance tests were wrong.

Mark Rotteveel added a comment - 05/Dec/16 05:11 PM
https://github.com/FirebirdSQL/jaybird/commit/601c6dc24ea9a2d440bac5c4af09a19f0623536a

Managed to squeeze even a little bit more about it, now it is just below 2000 ms.