Querydsl faster than JDBC?

[Gerd Gühne]

Hello,

I have write some performance tests with jdbc, querydsl and hibernate. My test scenario's are real world examples, because of this I can see the different of this 3 solutions.
In most of times querydsl is slower as JDBC (~ 1.5 times) and ~1.5 to ~9 times faster than hibernate. But if I execute a select that returns only some results, querydsl is faster as JDBC! For example the select returns 390 from 259,000 database entries, querydsl wins. If I execute a select that return 250,000 from 259,000 entries, JDBC wins. I have reproduce it every time with HSQLDB.
Why is querydsl sometimes faster as JDBC? I think querydsl use internal prepared statements, because of this it can't be faster as my JDBC prepared statements?
Execute querydsl internal classes from hsqldb? The log output show's that the sql query from querydsl is exactly the same to the JDBC query... Change querydsl for optimisations the sql statement, that I have write with querydsl API? Or have the database dialect no effect of the query, that I must set for SQLQueryFactoryImpl?

Thank's for your answers.
Gerd

[Timo Westkämper]

Hi.

On Wednesday, July 24, 2013 6:21:53 PM UTC+3, Gerd Gühne wrote:

Hello,

I have write some performance tests with jdbc, querydsl and hibernate. My test scenario's are real world examples, because of this I can see the different of this 3 solutions.
In most of times querydsl is slower as JDBC (~ 1.5 times) and ~1.5 to ~9 times faster than hibernate. But if I execute a select that returns only some results, querydsl is faster as JDBC! For example the select returns 390 from 259,000 database entries, querydsl wins. If I execute a select that return 250,000 from 259,000 entries, JDBC wins. I have reproduce it every time with HSQLDB.
Why is querydsl sometimes faster as JDBC? I think querydsl use internal prepared statements, because of this it can't be faster as my JDBC prepared statements?

Querydsl isn't faster than JDBC prepared statements, but the query you write with Querydsl isn't the actual SQL that is executed.
 

Execute querydsl internal classes from hsqldb? The log output show's that the sql query from querydsl is exactly the same to the JDBC query... Change querydsl for optimisations the sql statement, that I have write with querydsl API? Or have the database dialect no effect of the query, that I must set for SQLQueryFactoryImpl?

Did you make sure that the actual SQL from Querydsl is the same? The dialect handles the serialization from the Querydsl query model to SQL.

Br,
Timo

[Gerd Gühne]

Hello,

I have checked it again and found the different.

My sql statement:

String query = "SELECT d.id, d.ra, d.decl, d.sh, d.durchmesser, n.namen, ot.Typ "
                    + "FROM daten AS d INNER JOIN objekttypen AS ot ON d.Objektid=ot.id "
                    + "INNER JOIN namen AS n ON d.id=n.datenid"
                    + " WHERE ra BETWEEN 9.0 and 10.0 and decl BETWEEN 50.0 and 60.0 and sh<? ORDER BY ot.Typ asc;";
preparedStatement = getConnection().prepareStatement(query);

My querydsl statement:

list = queryFactory.query().from(daten).
                    innerJoin(daten.objekttypenFk, objekttypen).
                    innerJoin(daten._sysFk10177, namen).
                    where(
                    daten.ra.between(9.0, 10.0).and(daten.decl.between(50.0, 60.0).and(daten.sh.lt(sh))))
                    .orderBy(objekttypen.typ.asc())
                    .list(daten.id, daten.ra, daten.decl, daten.sh, daten.durchmesser, namen.namen, objekttypen.typ);

logger output:
2013-08-07 14:32:11,035 DEBUG  AbstractSQLQuery - query : select DATEN.ID, DATEN.RA, DATEN.DECL, DATEN.SH, DATEN.DURCHMESSER, NAMEN.NAMEN, OBJEKTTYPEN.TYP
from DATEN DATEN
inner join OBJEKTTYPEN OBJEKTTYPEN
on DATEN.OBJEKTID = OBJEKTTYPEN.ID
inner join NAMEN NAMEN
on DATEN.ID = NAMEN.DATENID
where DATEN.RA between ? and ? and DATEN.DECL between ? and ? and DATEN.SH < ?
order by OBJEKTTYPEN.TYP asc (AbstractSQLQuery.java:485)

If I change the sql query also to "between ? and ?" and set after them the fixed values for ra and decl, jdbc is a little bit faster as querydsl. Querydsl helps good, to generate fast sql queries. :-)

best regards
Gerd

[Timo Westkämper]

Hi Gerd.

Good that we found the reason. Querydsl indeed helps to write fast queries.

And we have done a lot of work to make the overhead of Querydsl as small as possible.

Br,
Timo

[Ameen Mohamed]

Hi Gerd and Timo

I was also going through some performance tests with jdbc (jdbctemplate to be precise) and querydsl sql. 

The results surprised me. I have a similar query like the one you have mentioned. However querydsl was slower than jdbc by 6 times. (not 1.5 times mentioned) by you.

I iterated over the execute query 1 lakh times to come to these figures.

Any idea as to why I am getting such an overhead in querydsl.

The details of my tests are as follows 

QueryDSL query :
 queryFactory.select(Projections.bean(Customer.class,customer.firstName,customer.status,customer.customerId)).

from(customer).innerJoin(customerChat).on(customer.customerId.eq(customerChat.senderId)).

innerJoin(customerChatDetail).on(customerChat.chatDetailId.eq(customerChatDetail.chatDetailId)).

where(customerChatDetail.isRead.eq(true).and(customer.status.eq(true))).fetch();

JDBCTemplate query:

this.jdbcTemplate.query(

       "select first_name,status,customer_id from customer inner join v_customer_chat on customer.customer_id=v_customer_chat.sender_id inner join v_customer_chat_detail on v_customer_chat.chat_detail_id = v_customer_chat_detail.chat_detail_id where v_customer_chat_detail.is_read = ? and customer.status = ?;",new Object[] {true, true})

I Iterated over the call to these methods 1 lakh times and came with a figures of around 6000ms for querydsl and 800ms for jdbctemplate.

Am I missing something?

[Timo Westkämper]

Hi Ameen.

If you could provide an example project I could tell you exactly what in your case causes the overhead and if Querydsl could be improved to lessen the overhead.

Querydsl to JDBC comparison would be more useful, since JDBC is the underlying API.

Br,

Timo

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

[Ruben Dijkstra]

Hi,

first of all, the two queries do something different.

In your Querydsl query, you want the result as a Customer, so Querydsl is taking some time to populate the properties as a Customer object.

Also, you can extract the Projections.bean() call to outside the loop, since every call will need to make a new BeanMap (the metadata on what and how to populate).

Projections.bean uses the most reflection, so it's the unluckiest that you picked that one to benchmark.

Also, what kind of database do you use for the benchmarks?

Try to reduce the confounding in the two benchmarks, and I'm sure that you will see less than 6 times difference.

Although, on average I think 60 µs from nothing to result not that bad for so much reflection.

https://www.wolframalpha.com/input/?i=6000+ms+divided+by+1+lakh

Hope that it helps,

Br,

Ruben

[Ruben Dijkstra]

Yes, the full project would be of value.

There are a lot of variables at play, so it's hard to write a good benchmark.

Br,

Ruben

On Friday, 1 July 2016 18:08:57 UTC+2, timowest wrote:

Hi Ameen.

If you could provide an example project I could tell you exactly what in your case causes the overhead and if Querydsl could be improved to lessen the overhead.

Querydsl to JDBC comparison would be more useful, since JDBC is the underlying API.

Br,

Timo

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

[Ameen Mohamed]

Thanks for the reply Ruben and Timo

I had changed the implementation of the querydsl query to as follows:

List<Customer> customers = new ArrayList<>();

Customer customerObj = null;

List<Tuple> customerTuples =  queryFactory.select(customer.firstName,customer.status,customer.customerId).

from(customer).innerJoin(customerChat).on(customer.customerId.eq(customerChat.senderId)).

innerJoin(customerChatDetail).on(customerChat.chatDetailId.eq(customerChatDetail.chatDetailId)).

where(customerChatDetail.isRead.eq(true).and(customer.status.eq(true))).fetch();

for (Tuple row : customerTuples) {

customerObj = new Customer();

 customerObj.setFirstName(row.get(customer.firstName));

 customerObj.setStatus( row.get(customer.status));

 customerObj.setCustomerId(row.get(customer.customerId));

 customers.add(customerObj);

}

I got a slight improvement where 6000ms reduced to around 5000ms. Again it is for 1 lakh iterations. And still the performance overhead when compared to jdbctemplate is more than 5 times worse.

For completeness here is the jdbctemplate implementation:

List<Customer> customers = this.jdbcTemplate.query(

       "select first_name,status,customer_id from customer inner join v_customer_chat on customer.customer_id=v_customer_chat.sender_id inner join v_customer_chat_detail on v_customer_chat.chat_detail_id = v_customer_chat_detail.chat_detail_id where v_customer_chat_detail.is_read = ? and customer.status = ?;",new Object[] {true, true},

       new RowMapper<Customer>() {

           public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {

            Customer customer = new Customer();

               customer.setFirstName(rs.getString("first_name"));

               customer.setStatus(rs.getBoolean("status"));

               customer.setCustomerId(rs.getLong("customer_id"));

               return customer;

           }

       });

Basically I am trying to do the exact same thing with different libraries and measure which one incurs more overhead.

1. Execute a relatively complex query with joins.

2. Populate beans from the resultset.

I am using H2 In-memory database. The database holds just a couple of records for each table. And 1 result row that matches the query.

The method is executed in a for loop (1 lakh iterations). And time calculated with the help of System.nanoTime() around the loop.

Its a spring boot project with different end points (one for querydsl and another for jdbctemplate). The configuration for querydsl and queryfactory is done as follows

    @Autowired

    public DataSource dataSource;

    @Bean

    public PlatformTransactionManager transactionManager() {

        return new DataSourceTransactionManager(dataSource);

    }

    @Bean

    public com.querydsl.sql.Configuration querydslConfiguration() {

        SQLTemplates templates = H2Templates.builder().build();

        com.querydsl.sql.Configuration configuration = new com.querydsl.sql.Configuration(templates);

        

        configuration.setExceptionTranslator(new SpringExceptionTranslator());

      

        return configuration;

    }

    @Bean

    public SQLQueryFactory queryFactory() {

        Provider<Connection> provider = new SpringConnectionProvider(dataSource);

        return new SQLQueryFactory(querydslConfiguration(), provider);

    }

Since I am executing both of these in the same project with the same datasource. I assume the only difference is the query serialization overhead in QueryDSL. Any thoughts?

I will try and share a similar project with you. Let me know if I am missing something. 

Thanks for your inputs

Ameen