Performance bottleneck

ml · **Joined:** Thu Apr 15, 2004 1:12 pm **Posts:** 55

Hi,

My application needs to determine if there exists a persistent object with a given value for one if its properties, but I don't need to actually retrieve the object. Currently, the method that does this looks like this:

Code:

    protected boolean existsWithKey (Object key)
        throws HibernateException
    {
        net.sf.hibernate.Session hs = HibernateFactory.getSession();
        Iterator i = hs.iterate (
                            "select from ResellerAgent where contactId = ?",
                            (Long) key,
                            Hibernate.LONG
                        );
        return i.hasNext();
    }       

My time is being totally dominated by this, so I'm wondering if there's a faster way. Would dropping down to straight SQL help here, or is that barking up the wrong tree?

Also... I could not see from the 2.1.2 Reference Documentation if there is any way to specify indices for class mapping tables (maybe I missed it). Adding one by hand ('alter table ResellerAgent add index contactId_index (contactId)' ) helped performance somewhat. Is there a way to specify this in the mapping? And does anyone know if there's an Xdoclet tag to generate that?

Thanks a lot,
mark

christian · **Posted:** Fri May 07, 2004 2:15 am

I'm sure you can set indexes on any <column> mapping element. Your code apparently executes two very simple SQL queries, how can that be slow? Have you checked the SQL log of Hibernate?

ml · **Joined:** Thu Apr 15, 2004 1:12 pm **Posts:** 55

Thanks for your quiclk reply!

christian wrote:

Your code apparently executes two very simple SQL queries, how can that be slow? Have you checked the SQL log of Hibernate?

Yes, I have. The ResellerAgent has a many-to-one association to another object, which contains a collection and other stuff. The query I see hibernate issuing (left outer join) seems to indicate that it's reconstituting the whole object as well as eagerly reconstituting the object at the other end of the many-to-one. The log4j DEBUG messages from hibernate also indicate that Hibernate is doing far more than I need it to be doing for this purpose.

I tried doing a straight SQL query, but I was kind of guessing at it because I had a hard time understanding the doc chapter (15) on SQL queries. Or maybe the problem is that my SQL knowledge is too lame. Anyway, I guessed wrong because it didn't work... here's my code:

Code:

    protected boolean existsWithKey (Object key)
        throws HibernateException
    {
        net.sf.hibernate.Session hs = HibernateFactory.getSession();
        String sql =
                "select {agent.contactId} from ResellerAgent"
                + " where {agent.contactId} = :key";
        Query q = hs.createSQLQuery (sql, "agent", ResellerAgent.class);
        q.setLong ("key", ((Long)key).longValue());
        return q.list().size() != 0;
   }

...and here's what I see (I have hibernate.show_sql=true):

Code:

     [java] Hibernate: select contactId0_ from ResellerAgent where contactId0_ = ?
     [java] WARN  - SQL Error: 1054, SQLState: S0022
     [java] ERROR - Column not found,  message from server: "Unknown column 'contactId0_' in 'field list'"

I think I'm just not grokking the table alias syntax for createSQLQuery() or something. Any ideas?

Quote:

I'm sure you can set indexes on any <column> mapping element.

Right! I found the relevant help on that here: http://forum.hibernate.org/viewtopic.php?t=930488[/url]

baliukas · **Posted:** Fri May 07, 2004 3:13 pm

Try "select count(*) from ResellerAgent where contactId = ?" in HQL