Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

The Native/Local connection is too slow. [JDBC463] #502

Closed
firebird-automations opened this issue Nov 29, 2016 · 9 comments
Closed

The Native/Local connection is too slow. [JDBC463] #502

firebird-automations opened this issue Nov 29, 2016 · 9 comments

Comments

@firebird-automations
Copy link

Submitted by: John Kilin (johnkilin)

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.

Commits: 3678683 601c6dc

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

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?

@firebird-automations
Copy link
Author

Commented by: John Kilin (johnkilin)

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,
...
);

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

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.

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

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.

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

601c6dc

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

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Fix Version: Jaybird 3.0.0-beta-2 [ 10802 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Fix Version: Jaybird 3.0.0 [ 10440 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment