Jdbi performance is poor

[吕兵阳]

@stevenschlansker , I use testng tests jdbi2.75, found both in multi threads operation and single thread operation, the performance of this : JDBC > sql2o > mybatis > Jdbi. Jdbi performance is much worse than mybatis. I, about 6000 data in the database query returns a List object, made at least 20 rounds of testing, run 1000 times, respectively, multithreading running and single thread. Are the results. Slower than mybatis probably around 10 s. Mybatis is slower than the sql2o probably less than 10 s,sql2o is slower than jdbc less then 2s.If you need, I can upload my test code!

[吕兵阳]

在 2016年9月20日星期二 UTC+8上午10:03:31，吕兵阳写道：

[Steven Schlansker]

I replied on the GitHub issue you filed -- we need some more information, either a self contained test case
showing the problem or output of a profiler showing our code as a hot spot. Usually performance issues like this
are due to suboptimal usage or long database queries.

> On Sep 19, 2016, at 7:03 PM, 吕兵阳 <kkr...@gmail.com> wrote:
>
> @stevenschlansker , I use testng tests jdbi2.75, found both in multi threads operation and single thread operation, the performance of this : JDBC > sql2o > mybatis > Jdbi. Jdbi performance is much worse than mybatis. I, about 6000 data in the database query returns a List object, made at least 20 rounds of testing, run 1000 times, respectively, multithreading running and single thread. Are the results. Slower than mybatis probably around 10 s. Mybatis is slower than the sql2o probably less than 10 s,sql2o is slower than jdbc less then 2s.If you need, I can upload my test code!
>

> --
> You received this message because you are subscribed to the Google Groups "jDBI" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[吕兵阳]

I upload the test cases, the attachment is a complete project, you can download down, there are my test SQL, and data, including complete code.​

 test-core.tar.gz

​

[Steven Schlansker]

Using VisualVM's Sampler, it looks like the majority of your time is spent in the BeanMapper2 class's "map" method.

The code is doing a lot of very expensive string conversions in "map".  To get better performance, you should cache the results of any string matching whenever possible.

You can reference the jdbi3 version of BeanMapper which handles this nicely, although with new Java 8 syntax:

https://github.com/jdbi/jdbi/blob/jdbi3/core/src/main/java/org/jdbi/v3/core/mapper/BeanMapper.java

It looks like we don't do a great job of this in BeanMapper, so I filed a ticket to improve the base implementation.  I will be out of town for a week and so can't fix it myself right now.

https://github.com/jdbi/jdbi/issues/496

On Sep 20, 2016, at 6:41 PM, 吕兵阳 <kkr...@gmail.com> wrote:

I upload the test cases, the attachment is a complete project, you can download down, there are my test SQL, and data, including complete code.​

 test-core.tar.gz

[Steven Schlansker]

Using VisualVM's Sampler, it looks like the majority of your time is spent in the BeanMapper2 class's "map" method.

The code is doing a lot of very expensive string conversions in "map".  To get better performance, you should cache the results of any string matching whenever possible.

You can reference the jdbi3 version of BeanMapper which handles this nicely, although with new Java 8 syntax:

https://github.com/jdbi/jdbi/blob/jdbi3/core/src/main/java/org/jdbi/v3/core/mapper/BeanMapper.java

It looks like we don't do a great job of this in BeanMapper, so I filed a ticket to improve the base implementation.  I will be out of town for a week and so can't fix it myself right now.

https://github.com/jdbi/jdbi/issues/496

On Sep 20, 2016, at 6:41 PM, 吕兵阳 <kkr...@gmail.com> wrote:

I upload the test cases, the attachment is a complete project, you can download down, there are my test SQL, and data, including complete code.​

 test-core.tar.gz

[Steven Schlansker]

Using VisualVM's Sampler, it looks like the majority of your time is spent in the BeanMapper2 class's "map" method.

The code is doing a lot of very expensive string conversions in "map".  To get better performance, you should cache the results of any string matching whenever possible.

You can reference the jdbi3 version of BeanMapper which handles this nicely, although with new Java 8 syntax:

https://github.com/jdbi/jdbi/blob/jdbi3/core/src/main/java/org/jdbi/v3/core/mapper/BeanMapper.java

It looks like we don't do a great job of this in BeanMapper, so I filed a ticket to improve the base implementation.  I will be out of town for a week and so can't fix it myself right now.

https://github.com/jdbi/jdbi/issues/496

On Sep 20, 2016, at 6:41 PM, 吕兵阳 <kkr...@gmail.com> wrote:

I upload the test cases, the attachment is a complete project, you can download down, there are my test SQL, and data, including complete code.​

 test-core.tar.gz

[吕兵阳]

I use VisualVM, found that the result of it is.

To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "jDBI" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jdbi/YhYM-2arwGE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jdbi+unsubscribe@googlegroups.com.

[吕兵阳]

i test jdbi3,the performance is still ： sql2o>mybatis>jdbi3.Jdbi3 underline convert the Camel, not a lot of toLowerCase ().jdbi3 slower than sql2o almost 8 s.

在 2016年9月20日星期二 UTC+8上午10:03:31，吕兵阳写道：

@stevenschlansker , I use testng tests jdbi2.75, found both in multi threads operation and single thread operation, the performance of this : JDBC > sql2o > mybatis > Jdbi. Jdbi performance is much worse than mybatis. I, about 6000 data in the database query returns a List object, made at least 20 rounds of testing, run 1000 times, respectively, multithreading running and single thread. Are the results. Slower than mybatis probably around 10 s. Mybatis is slower than the sql2o probably less than 10 s,sql2o is slower than jdbc less then 2s.If you need, I can upload my test code!

[Steven Schlansker]

Hi, sorry for the delay, I'm back from vacation now --

Another thing to consider: Using generic mappers like the BeanMapper
is almost always going to be slower than writing a custom mapper.

We will work to improve the performance as much as possible but
a reflection based implementation will likely always be relatively slow.

If you care about the performance of mapping specific types I would
consider writing a mapper for that type, the implementation for a
bean such as yours is very simple, and it should dramatically improve
performance.

> --
> You received this message because you are subscribed to the Google Groups "jDBI" group.

> To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.

[Steven Schlansker]

Here is an example of a mapper for your Student type:

https://gist.github.com/stevenschlansker/24a884bfa439da098b2eccdeba56fb4f

(I am still wondering why a Student would have so many user names! Are you
sure your data model is right?)

Using this instead of the generic BeanMapper, the performance is significantly
improved. (over 60% faster in one of my tests).

[吕兵阳]

Ok, now I feel jdbi have no advantage, we have a mybatis generator why use jdbi?

在 2016年9月29日星期四 UTC+8上午11:39:15，Steven Schlansker写道：

[吕兵阳]

Sql2o design basic about the same with you,It is second only to the performance of JDBC,I hope you can consult sql2o part design。

在 2016年9月20日星期二 UTC+8上午10:03:31，吕兵阳写道：

@stevenschlansker , I use testng tests jdbi2.75, found both in multi threads operation and single thread operation, the performance of this : JDBC > sql2o > mybatis > Jdbi. Jdbi performance is much worse than mybatis. I, about 6000 data in the database query returns a List object, made at least 20 rounds of testing, run 1000 times, respectively, multithreading running and single thread. Are the results. Slower than mybatis probably around 10 s. Mybatis is slower than the sql2o probably less than 10 s,sql2o is slower than jdbc less then 2s.If you need, I can upload my test code!

[Brian McCallister]

Fair question -- I use JDBI because it works the way I think (or at least the way I have thought in the past, which I understand and agree with for the most part).

Sql2o looks pretty nice (well, to be honest, it looks exactly like JDBI), which is cool. If sql2o came into its APIs independently of JDBI, that is pretty cool. I love cases of convergent evolution. Frankly, I doubt it, though :-) If Lars is lurking here, I'd love to compare notes (you also look familiar, did we meet at a JavaZone by chance?)

MyBatis predates JDBI by a bit (with its old name of iBatis) and was certainly a thing that influenced JDBI. I don't remember the details from over a decade ago on exactly how though :-)

So, why does JDBI work the way it does, and why do I plan on continuing to use it? JDBI is really focused on providing a great API for talking to databases. The JDBC API is horrible (it is massively successful, but that is not because of its user-friendly API design), and I want a better one. There have been three APIs for JDBI, the first was like the current fluent API, but predates Java5 and type parameterization, so was all Map oriented. Version 2 made that much better once Java 5 and type parameterization became a viable thing. The SQL Object API actually grew out of a JDBC4 proposal which I thought was good, but the JDBC EG decided against. I am very happy with its heuristic value (it was one of the first annotation-oriented apis, which later became very popular via jax-rs, retrofit, feign, etc. I have never been very happy with the sql object api implementation, but after Dain and Martin fixed the bugs in my first implementation, it *works* (and is reasonably correct), so there it is :-)

Performance of the library has never been a priority because every time I've run any meaningful benchmark, performance of the lib was dwarfed by making an RPC out to the database. The time spent in JDBI was literally lost in the noise of talking over the network and was not measurable. Microbenchmarks stubbing out the database could certainly lead to things that might be improved, but for every real case with a real (network accessed) database, the variance in talking to the database inside JDBC made the time spent in JDBI impossible to effectively detect. If this has changed such that the impact of JDBI is measurable, it is worth taking a look at :-) The multithreaded case in your benchmark makes me worry that we have some contention cropping up and that'd be bad.

For JDBI3, the priority has been taking advantage of the Stream API, which maps exceptionally well queries. It is also taking nicely separable things, like the sql object api, out of the core (so that one with a prettier impl, and which makes extension, etc, easier) becomes easier. 

-Brian

--

[吕兵阳]

 Although you say there may be some reason. But jdbi3 only support jdk8 above, your work is done with jdk8?At present, there are a lot of project is running under jdk8

在 2016年10月4日星期二 UTC+8下午9:31:27，Brian McCallister写道：

[Brian McCallister]

On Tue, Oct 4, 2016 at 7:22 AM 吕兵阳 <kkr...@gmail.com> wrote:

 Although you say there may be some reason. But jdbi3 only support jdk8 above, your work is done with jdk8?At present, there are a lot of project is running under jdk8

Correct, JDBI3 will require jdk8. If you are using an earlier version, you can still use JDBI2. Both versions will happily coexist in the same codebase even (different artifactId and package name).

-Brian

[Steven Schlansker]

> On Oct 4, 2016, at 7:50 AM, Brian McCallister <bri...@skife.org> wrote:
>
>
>
> On Tue, Oct 4, 2016 at 7:22 AM 吕兵阳 <kkr...@gmail.com> wrote:
> Although you say there may be some reason. But jdbi3 only support jdk8 above, your work is done with jdk8?At present, there are a lot of project is running under jdk8
>

Yes, the next version will target Java 8. There are many appealing new features (notably Streams) that we want to leverage.
Additionally, Java 7 is End of Life as of April 2015 -- if you are not on 8 yet, you are running unsupported
and will not receive e.g. critical security fixes (unless you pay Oracle for support)

We are trying to look forward. Java 8 adoption is not 100% yet, but it is rising, and will only
go up from here.

[Arnaud Roger]

feels a bit like spaming but.
I'm the dev behind http://simpleflatmapper.org/ and following https://github.com/bwajtr/java-persistence-frameworks-comparison
I decided to see if I could integrate my mapper into jdbi. here is the result http://simpleflatmapper.org/0109-getting-started-jdbi.html.

SFM is highly optimized mapper from/to flat structure, here are some old jdbc benchmark https://github.com/arnaudroger/SimpleFlatMapper/wiki/Jdbc-Performance-In-Memory-HsqlDb
I would need revise it but should give you an idea.
On top of that the mapper support inner object, inner list, constructor injection, factory method, lombok builder etc ...
I'm not a user of jdbi so would appreciates feedbacks !

Regards, Arnaud.

[Brian McCallister]

That's cool! I've used Immutables+Jackson+Rosetta for similar things,  but the SFM looks awesome! /me wanders off to try it out!

Thanks!

-Brian

--

You received this message because you are subscribed to the Google Groups "jDBI" group.

To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+unsubscribe@googlegroups.com.

[li haiyang]

Brian,

"Performance of the library has never been a priority because every time I've run any meaningful benchmark, performance of the lib was dwarfed by making an RPC out to the databa..."

Basically, I agree with what Brian said. However, if you guys can improve the performance of "mapper" (from result to obj, or from object to statement) to make whole framework more efficient, that's not a bad idea.

Although the time spent by jdbi can be ignored, comparing the time consumed by jdbc/network, the mapper part is still very critical because because it's still a heavy part if not considering the network part.

So I think an efficient mapper is very important to any data access layer framework, like JDBI or other.

I took quick look into BeanMapper2, the code is not really efficient. the performance can catch sql2o after I change a bit, refer to the attachment.

Further suggestions: if someone spend couple of hours or a day to look into https://github.com/EsotericSoftware/reflectasm. it's pretty simple, 

   and adopt the reflectasm  api in mapper. According the test i did in other application, the performance will beat any DAL framework, and could tie to the raw JDBC.

To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.

[Steven Schlansker]

Yes, I think we are mostly in agreement here.

We will take seriously any performance issue that can be reproduced in a reasonably
sane production setup, or preferably a macrobenchmark.

We will take less seriously, but still consider, microbenchmarks that show hot spots --
but it will always be with the assumption that for the vast majority of users, the cost
of disk + network copying will far outweigh the cost of JDBI.

Way back to the original poster's point, we have actually dramatically improved the situation
in the meantime. RowMapper are now given an opportunity to compute the mapping from SQL column label
to Java name once per query, rather than once per row:

https://github.com/jdbi/jdbi/blob/jdbi3/core/src/main/java/org/jdbi/v3/core/mapper/RowMapper.java#L61-L63
https://github.com/jdbi/jdbi/blob/jdbi3/core/src/main/java/org/jdbi/v3/core/mapper/reflect/ConstructorMapper.java#L124-L140

Hopefully this will be a meaningful improvement particularly in the case of the case folding and other complex string operations.

Thank you for the link to the ReflectASM project. It looks interesting, but I think you need to make a bit more compelling of a case to
bring in such an intrusive change. Your benchmarks on the website don't actually have a scale, which makes them very hard to interpret.
Additionally it looks like only a 2x change specifically on the cost of invoking a getter or setter. For all but the most abusive
cases, I have a hard time believing that this 2x change on a single field set will be anything but noise, especially once you are copying
data off the network to feed it.

> <BeanMapper2.java>

[Matthew Hall]

I second Steven's comments.

In general I'm happy to accept PRs for straightforward and obvious performance improvements (even illusory improvements such as those demonstrated by microbenchmarks).

However if a pull request makes the code harder to follow or maintain, it would have to produce a _significant_ performance improvement to be considered.

-Matthew

> To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+unsubscribe@googlegroups.com.

> For more options, visit https://groups.google.com/d/optout.

> <BeanMapper2.java>

--
You received this message because you are subscribed to the Google Groups "jDBI" group.

To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+unsubscribe@googlegroups.com.