Comparing Querydsl, jOOQ, JEQUEL, activejdbc, iciql and other query DSLs

Question

Can someone point me to some resources about the performance comparison among the different Query DSL libraries available for using with Java, like: Querydsl, jOOQ, JEQUEL, activejdbc, iciql and etc...

Background: I m using Spring JDBC template, but that still required the queries to be written in plain string format. Although I don't have issues in writing the direct queries, but I m concerned having direct dependency on DB table names. I don't want to use any ORM framework like Hibernate or JPA/EclipseLink. I need the raw performance as high as possible (IMO, they are good for more CRUD centric applications). I can afford some slight overhead for the these DSLs only if that is a little (I believe, it'll be mostly StringBuilder/String concatenations!)

I've considered using named queries externalised in some xml. But just trying to evaluate the value different Query DSL libraries provide.

Edit: more on my requirement: I want to know the performance comparison among these when building a moderately complex query using their API methods. All I need is to generate a query string using any of these query DSL libraries and pass that to Spring JDBC template. So, I want to know if addition of this intermediate step incurs considerable performance penalty, I want to use named queries or build my own library which just uses StingBuilder or similar approach

update my experience with jOOQ, iciql, QueryDSL:

All though I missed to mention this in my original post, I m also keen about the ease of use & the overhead I need to have in my entity classes (like if any additional annotations or implementations required).

jOOQ:

• requires changing the entity properties to the library specific way
• can return SQL query string

Iciql:

• entity can be mapped with no or little changes (can be mapped using total 3 ways)
• but with that it limits to only select queries (for update/delete/... requires entity changes again)

QueryDSL:

• multiple ways to bind entities with table (other than library specific ways, using JPA annotations is supported). but we need to modify the entities at least
• no simple/direct way to get the query string

(all observations are with little knowledge I've on these; if any of these are incorrect, please correct)

With all of the above, I m sticking with writing named queries :( But as the Lukas Eder answer seems explains about my original post concern (performance), I've accepted his.

Jailbird

Do you want a comparison with identical underlying SQL queries or not? This is a quite difficult task, you need to take processing, projections, generated SQL and usage of various JDBC level optimizations into account. – Ganja

I've edited the question. Please see. I don't want any functionality like ORM or something which issues the query by itself. It is enough for the library to generate the query. Thanks – Jailbird

@mrCoder: For next time, instead of modifying your question (which might be a bit confusing for some), you can also add an answer to your own question, if you want to further elaborate... – Melodymeloid

Getting the query string from QueryDSL is described here: #21689722 – Nelidanelie

Answer 1

In modern JVM's you shouldn't be worrying about SQL string concatenation too much. The true overhead any database abstraction layer may produce (compared to the relatively high round-trip time to the database and back), is usually due to second-level caching, which is done in Hibernate/JPA. Or by inefficiently mapping object models to SQL in a way that using indexes or general query transformation becomes impossible.

Compared to that, string concatenation is really negligible, even for complex SQL constructs with several UNIONs, nested SELECTs, JOINs, semi-JOINs, anti-JOINs, etc, so I'm guessing all of the frameworks you mentioned perform in a similar manner, as they allow you to keep control over your SQL.

On the other hand, some frameworks or usage modes in those frameworks may actually fetch the whole result set into memory. That can cause issues if your result sets are large, also because with Java's generics, most primitive types (int, long, etc) are probably mapped to their corresponding wrappers (Integer, Long).

As for jOOQ (of which I'm the developer), I have previously profiled the library with YourKit Profiler for massive query execution. The bulk work was always done in the database, not in query construction. jOOQ uses a single StringBuilder for every query. I imagine (not verified), that QueryDSL and JEQUEL do the same...

As for iciql, which is a fork of JaQu, there might be some additional impact by the fact that they use Java instrumentation to decompile their natural syntax. But I guess that can be omitted, if it means too much impact.

Melodymeloid

I concur with Lukas, Querydsl uses also a single StringBuilder for most queries and the String building overhead is minimal. The overhead generated from creation of intermediate DSL objects and related garbage collection is difficult to measure properly. – Ganja

Hi Timo! :-) I think GC on intermediate objects is marginal, too. Those objects will never make it into the heap (tenured space). How about large result sets? Does QueryDSL load results into memory, as jOOQ does in default execution modes? jOOQ supports lazy fetching, but I think that's not used very often. Does QueryDSL support primitive types in result sets? – Melodymeloid

Querydsl supports both eager and lazy fetching via iteration. Querydsl supports primitive types in result sets. – Ganja

Thanks for the detailed answer. Does that mean, both jOOQ and QueryDSL are more than just query builders? If yes, can I use them with Spring JDBC template (which means, can they just emit the generated query string via some method which I can pass to template methods?) – Jailbird

@mrCoder: Yes, both frameworks are also used to execute the queries, even though that step is optional - should you wish to use them as plain query builders. From what I understand, this is not the case for JEQUEL. In jOOQ, the SQL can be obtained using Query#getSQL(). Surely, Timo will tell you in a minute how this can be done with QueryDSL... – Melodymeloid

Actually in Querydsl this is not so straightforward. With query.toString() you will get a template for a prepared statements, but the bindings are not directly accessible. Spring Data offers integration with Querydsl : springsource.org/node/3192 – Ganja

Answer 2

You should also look at MyBatis Statement Builder.

While MyBatis is clearly a mapping technology it does have a Statement builder DSL that seems to be decoupled from MyBatis (that is you don't need anything else from MyBatis to use the builders... annoyingly its not in its own jar). I don't like it because it uses ThreadLocals.

Persse

+1 for that API idea! Although, this allows for constructing bogus queries that do not make sense at all, without any framework support for syntax correctness. I guess, that ThreadLocal is also the only possible choice for this API. Beware if you forget the BEGIN()... – Melodymeloid

I could not believe the threadlocal either . I want to use JOOQ but I would prefer to use real SQL with just some constants AND for it to Map to immutable objects AND for it to be built off of Spring JDBC so I can use @Transaction. The only thing I'm missing right now is the schema table/column name constants generator. I'm hoping I don't get lost in vietnam :) – Persse

Answer 3

I cannot speak for other frameworks, but I performed a primitive analysis of performance to compare ActiveJDBC and Hibernate. The test was on a laptop with 8G RAM, SSD drive against MySQL. Table PEOPLE with a few simple columns and a surrogate ID PK.

One test was to insert 50K records as objects, and the other was to read the 50K objects from table at once (in memory). In both tests ActiveJDBC showed 40% performance improvement over Hibernate. In either case, the queries generated were simple insert and select, closely resembling each other.

hope this helps,

Igor

Utter

ActiveJDBC is not a query generation framework, this is an ORM, as such you cannot get a generated query from it to feed into Spring JDBC template – Utter

Nice. This should about show the impact of the huge abstraction layer and maybe the second-level caching. Did you enable that during your tests? – Melodymeloid

no, these tests were primarily to expose object creation problems. Since code does not run the same query twice, use of cache would yield not performance benefit (speculation). AJ has a query level cache: code.google.com/p/activejdbc/wiki/Caching, which I'm sure behaves quite different from Hibernate. What I did was a brutal force test, apples to apples. – Utter

OK, I can see how that cache may be helpful in some cases. Nice idea! – Melodymeloid

Answer 4

A light-weight, no-dependency library for programmatic SQL query creation is the OpenHMS SQL Builder library:

https://openhms.sourceforge.io/sqlbuilder/

Available as Maven dependency:

https://mvnrepository.com/artifact/com.healthmarketscience.sqlbuilder/sqlbuilder

Termite