Avoiding a nested request in sleep mode with views and filters

Question

I use hibernate with views (@Subselect and @Synchronize annotations) and filters. The problem is asking for encapsulation of sleep mode (determined by the "value" property of the @Subselect annotation), and the actions become very slow.

For example, if I write my own SQL query (which does not take more than 100 ms to execute), it looks like this:

SELECT id, country_id, firstName, lastName FROM client WHERE client.country_id IN (2564,2558,1452,3652) 

But if I use hibernate with filters, the generated query will look like this:

 SELECT _this.id AS id1_0_0, _this.country_id AS country_id2_0_0 _this.firstName AS firstName3_0_0, _this.lastName AS lastName4_0_0 FROM ( SELECT id, country_id, firstName, lastName FROM client ) _this WHERE _this.country_id2_0_0 IN (2564,2558,1452,3652) 

This means that the sleep-generated SQL is slower than my manually written SQL query, because all rows of the client table (in a subquery) are loaded, and the filter operation is performed in the parent query after all rows are loaded, this is stupid and provides very slow performance (1.7 s versus less than 100 ms). So, does anyone know a way to avoid this?

Answer 1

Here I see various problems and solutions:

• see if you can avoid the @Subselect annotation altogether => if you can create a view in the database that returns the requested data, it will make the whole scenario and the consequences (sleep mode matching) much easier (why does Hibernate do or optimize something, that the databases are really optimized and designed for)
• Hibernate does not generate code that is obviously optimized for your database (version) => try to upgrade to a new version of Hibernate or influence it using special db hints / configuration (dialect, provider, etc ..)
• the database does not do a good job of optimizing your query (filter discarding) and it seems very bad in such a general optimization strategy => try switching to a new version of db or switch the DBMS if possible (e.g. PostgreSQL, Oracle, ... ), since more future problems are expected here.

(for more specific details, you really need to have more background information about your tables, db version, and annotated classes)