-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 10 posts ] 
  Previous topic | Next topic 
Author Message
smcnamara
 Post subject: is automatic version column indexed?
PostPosted: Wed Jan 04, 2006 12:33 pm 
Beginner
Beginner

Joined: Thu Nov 24, 2005 2:58 pm
Posts: 21
Updates to versioned data uses a where clause as follows;

The Oracle OEM SQL monitor shows me this as one of hte Top10 statements;

Code:
UPDATE authorization
    SET version = :1,
        last_updated = :2,
        state = :3
    WHERE authorization_id = :4
      AND version = :5


As the version tag does not support an index attribute and one is not automatically generated wont the lack of a version index affect performance as the table size grows?

Code:
<hibernate-mapping default-lazy="false">
    <class name="com.blah.Authorization" table="AUTHORIZATION"
           dynamic-insert="true" dynamic-update="true">
        <comment>
            An Authorization is used to record the authorization state for
            each account that participates in a transaction.
        </comment>

        <id name="id" type="java.lang.Long" access="field">
            <column name="AUTHORIZATION_ID">
                <comment>Primary key for authorization records.</comment>
            </column>
        </id>

        <version column="VERSION" name="version" type="java.lang.Long" access="field" unsaved-value="negative">
            <meta attribute="description">Internal field that contains version information.</meta>
        </version>

        <property name="created" type="java.util.Calendar" access="field">
            <column name="CREATED">
                <comment>The date/time when the row was created.</comment>
            </column>
        </property>

        <property name="lastUpdated" type="java.util.Calendar" access="field">
            <column name="LAST_UPDATED">
                <comment>The date/time when the row was last updated.</comment>
            </column>
        </property>
    </class>

</hibernate-mapping>
Top
 Profile  
 
christian
 Post subject:
PostPosted: Wed Jan 04, 2006 12:45 pm 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
So, add an index to your DDL. If you want it exported automatically, write it into your mapping file with <database-object><create>...
Top
 Profile  
 
smcnamara
 Post subject:
PostPosted: Wed Jan 04, 2006 1:04 pm 
Beginner
Beginner

Joined: Thu Nov 24, 2005 2:58 pm
Posts: 21
christian wrote:
So, add an index to your DDL. If you want it exported automatically, write it into your mapping file with <database-object><create>...


eh? the 3.0 DTD i have has no mention of database-object. Yet it appears here. Is this a 3.1 feature; if so shouldn't the DTD version have changed?

So how can I fix the missing version index problem on 3.0.5?
Top
 Profile  
 
smcnamara
 Post subject:
PostPosted: Wed Jan 04, 2006 1:13 pm 
Beginner
Beginner

Joined: Thu Nov 24, 2005 2:58 pm
Posts: 21
BTW is/was there a reason the index isn't automatically generated given that the resultant SQL will have a where clause?
Top
 Profile  
 
smcnamara
 Post subject:
PostPosted: Thu Jan 05, 2006 12:01 pm 
Beginner
Beginner

Joined: Thu Nov 24, 2005 2:58 pm
Posts: 21
sorry to dump this but doesn't anyone know how to fix this? it looks like this is a clear performance issue when using versioned data. hasn't anyone seen it or resolved it?

I cant use the <database-object> without moving to 3.1 and I currently use the SchemaExport utility to generate all the db scripts and would rather avoid manually having to hack in additional steps to create the missing indexes.
Top
 Profile  
 
christian
 Post subject:
PostPosted: Thu Jan 05, 2006 12:05 pm 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
Ok, here are some hints:

- SchemaExport is for development

- Performance of database index lookups is irrelevant in development

- the DDL that is used for the production database is controlled by a DBA who refines it far beyond what SchemaExport can ever provide

- hence, there never will be a flag or attribute for every possible performance optimization, for every possible database management system, in Hibernate's mapping metadata

- finally, for all those who ignore this advice, there is the <database-object> in Hibernate 3.1, which you should upgrade to if you want that functionality.

Now of course you will start arguing that "index" on "version" should be very common and therefore, that there should be an attribute for it. Well, there isn't, get over it and upgrade.
Top
 Profile  
 
ekupcik
 Post subject:
PostPosted: Thu Jan 05, 2006 12:18 pm 
Newbie

Joined: Sun Oct 09, 2005 6:36 am
Posts: 19
But why would you want an index on the version column at all? After all the WHERE clause contains the primary key column and i assume that the optimizers in the DB are smart enough to use this wisely. Of course you can can create an index on it. But i'd rather first check whether the fact there is no index has any real effect at all before i add an idex to every version column in your DB.
Top
 Profile  
 
smcnamara
 Post subject:
PostPosted: Thu Jan 05, 2006 1:16 pm 
Beginner
Beginner

Joined: Thu Nov 24, 2005 2:58 pm
Posts: 21
christian wrote:
Ok, here are some hints:

- SchemaExport is for development

- Performance of database index lookups is irrelevant in development

- the DDL that is used for the production database is controlled by a DBA who refines it far beyond what SchemaExport can ever provide

- hence, there never will be a flag or attribute for every possible performance optimization, for every possible database management system, in Hibernate's mapping metadata

- finally, for all those who ignore this advice, there is the <database-object> in Hibernate 3.1, which you should upgrade to if you want that functionality.

Now of course you will start arguing that "index" on "version" should be very common and therefore, that there should be an attribute for it. Well, there isn't, get over it and upgrade.

ah christian who knew mind reading was one of your many talents! You appear to possess it in equal measure to your patience & humility.

Now as to your observations that were intended to help;

1) fair enough
2) bollox
3) you say; but fair enough
4) again that seems fair

I could get into this more with you but to be honest I suspect it would simply invite more arrogant unhelpful BS. You've managed to build some good software but you seriously need to learn to respect and help others as they get to grips with hibernate and ask questions about it.

Having used hibernate over the last 9 months or so I have to say how regularily I'm appalled at the level of "support" offered by members of the hibernate team and similarily encouraged by the genuine efforts at helping that regular users provide to each other.
Top
 Profile  
 
smcnamara
 Post subject:
PostPosted: Thu Jan 05, 2006 1:18 pm 
Beginner
Beginner

Joined: Thu Nov 24, 2005 2:58 pm
Posts: 21
ekupcik wrote:
But why would you want an index on the version column at all? After all the WHERE clause contains the primary key column and i assume that the optimizers in the DB are smart enough to use this wisely. Of course you can can create an index on it. But i'd rather first check whether the fact there is no index has any real effect at all before i add an idex to every version column in your DB.


hi ekupcik,

you make a good point. I'm no DBA but the absence of the index seemed like it might be an issue; if having the PK in the same query is enough of a hint to allow the db perform an optimal query then great. thanks for the comment.
Top
 Profile  
 
christian
 Post subject:
PostPosted: Thu Jan 05, 2006 1:36 pm 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
Unbelievable.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 10 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.