CODEWITHSUNDEEP
performanceoracle-databasehibernateindexing
Patrick Hammer
Patrick Hammer

Reputation: 1172

Query from Hibernate App does not use DB indexes

I try to solve a performance issue of my application. The query hibernate generates, is of the form:

select * 
from ( 
    select this.a, this.b, this.state, this.id 
    from view_user this 
    where this.state=:1 order by this.a asc, this.b
) 
where rownum <= :2

where

Issue

The above query performs - fast from SQLDeveloper - fast from a small Java app with hibernate - extremely slow (>100x slower) from the application with hibernate - values for the bind variables are 2 respectively 30 (rownum origins from paging) - the hibernate query is "of the form" above. There are actually about 20 columns in the view.

Current state of analysis

Versions

=> I'm glad about every hint somebody might have concerning this issue. What troubles me is the fact that the DB tracing did not show any differences... Yes, it looks like it is something about hibernate. But what? How to detect?

For the sake of completeness, here the hibernate query (from the log):

Select * from ( 
    select this.USER_ID as USER_ID0_, this.CLIENT_ID as CLIENT_ID0_, 
    this.USER_NAME as USER_NAME0_, this.USER_FIRST_NAME as USER_FIR5_0_, this.USER_REMARKS as 
    USER_REM6_0_, this.USER_LOGIN_ID as USER_LOG7_0_, this.USER_TITLE as USER_TITLE0_, 
    this.user_language_code as user_lan9_0_, this.USER_SEX as USER_SEX0_, 
    this.USER_BIRTH_DATE as USER_BI11_0_, this.USER_TELEPHONE as USER_TE12_0_, 
    this.USER_TELEFAX as USER_TE13_0_, this.USER_MOBILE as USER_MO14_0_, 
    this.USER_EMAIL as USER_EMAIL0_, this.USER_ADDRESSLINE1 as USER_AD16_0_, 
    this.USER_ADDRESSLINE2 as USER_AD17_0_, this.USER_POSTALCODE as USER_PO18_0_, 
    this.USER_CITY as USER_CITY0_, this.USER_COUNTRY_CD as USER_CO20_0_, 
    this.USER_COUNTRY_NAME as USER_CO21_0_, this.USER_STATE_ID as USER_ST24_0_, 
    this.USER_STATE as USER_STATE0_, this.USER_TEMP_COLL_ID as USER_TE26_0_, 
    this.USER_TEMP_COLL_NAME as USER_TE27_0_, this.UNIT_ID as UNIT_ID0_, 
    this.CLIENT_NAME as CLIENT_38_0_, this.PROFILE_EXTID as PROFILE39_0_
    from VIEW_USER this
    where this.USER_STATE_ID=:1 order by this.USER_NAME asc, this.USER_FIRST_NAME asc
) 
where rownum <= :2

Unique index is over user_name, user_first_name, user_id.

Upvotes: 5

Views: 4027

Answers (5)

nevster
nevster

Reputation: 6465

There's an answer above/below about nvarchars with the setting for SQL Server - here's a similar setting for Oracle...

Check to make sure someone hasn't set the property oracle.jdbc.defaultNChar=true

This is sometimes done to resolve unicode problems but it means all columns are treated as nvarchars. If you have an index on a varchar column, it won't be used because oracle has to use a function to convert the character encoding.

Upvotes: 0

Darryl Fryatt
Darryl Fryatt

Reputation: 63

I've come across a similar situation so this might be relevant to you.

The JDBC driver is changing your parameters to unicode so a varchar becomes a nvarchar when it hits the database. If you are lucky SQL 2008 SP1 will catch this and convert it back. But SQL 2000 and SQL 2005 will not and the query optimizer will do a full table scan ignoring your indexes.

You might be able to fix this at the JDBC layer by adding a connection parameter sendStringParametersAsUnicode=FALSE to your connection string.

Upvotes: 5

Patrick Hammer
Patrick Hammer

Reputation: 1172

The final solution of the issue was c3p0. We were not able to track down why the wrong query plans were chosen. Our final assumption is that it has something to do with the connection initialization of dbcp. But because of the lake of time, we tried out c3p0. The first experiences:

  • issue does not appear
  • faster because much more aggressive in terms of using maxConnections configured for the pool
  • and because of the point above, our complete application is now faster. This is based on the general impression when using the application as well as by our various load tests

So, at the moment we are eagerly testing out various scenarios with c3p0 in order to be ready for productive systems.

Thanks for all inputs!

Upvotes: 1

Patrick Hammer
Patrick Hammer

Reputation: 1172

After a lot of debugging and trying around, I have an answer how to fix the issue. However, I can not explain yet what the problem exactly is.

The issue seems to be on JDBC connection pooling level. My config was:

  • initialSize=0
  • minIdle=10

Using the above configuration, we had the symptoms as described in the issue descritpion. The solution so far seems to be to set minIdle equal to initialSize. Otherwise, the connection seem to be initialized somehow differently and we see the performance decrease. So, atm we set both to '10'.

I am currently analyzing logs. On the DB server, the trace files do not show any differences. The JDBC tracing did not show any interesting information neither so far.

Googling, I saw that a lot of sample configuration set minIdle = initialSize. Furthermore I found two quotes (spring docu, vmware docu) concerning JDBC settings:

minIdle: The minimum number of established connections that should be kept in the pool at all times. The connection pool can shrink below this number if validation queries fail. The default value is derived from initialSize.

Note that the last sentence "derived from initialSize" is not found in all documentations for minIdle. Most of the documentations mention '0' as default.

Upvotes: 0

Todd Murray
Todd Murray

Reputation: 423

The SQL you give doesn't look like a Hibernate generated query. Are you sure it's not a handwritten query?

If you want to use Hibernate you can use setMaxResults() to limit the number of rows returned.

If you want to use a handwritten query I think you want something like this:

select *

from ( select this.a, this.b, this.state, this.id from view_user this where this.state=:1 order by this.a asc, this.b ) where rownum <= :2

Upvotes: 1

Related Questions