[jboss-jira] [JBoss JIRA] Commented: (JBPM-735) add indexes to foreign keys

[Tom Baeyens (JIRA)]

    [ http://jira.jboss.com/jira/browse/JBPM-735?page=comments#action_12341489 ] 
            
Tom Baeyens commented on JBPM-735:
----------------------------------

Christian wrote:
"SQL optimization is much much more than creating a default index with default settings. Just because this is often done in practice doesn't mean we should encourage this ad-hoc optimization. IMO this is a problem that is solved with documentation, not software. "

well.  i understand that hibernate cannot create optimized DDL.  but in the case of jBPM, we know the object model and the main use cases.  we want to give as much support as possible.  we realize this is a big dev and test effort.  therefor we are not going to claim that we provide optimized scripts for all possible databases.  

Instead, i want to centralize all information on how to optimize the default generated DDL that we get out of hibernate.  For this purpose, we created a wiki page http://wiki.jboss.org/wiki/Wiki.jsp?page=JbpmDbCompatibility  On that page we can collect user experiences as well as our own expertises.    Second step is that we might add scripts or dedicated binaries for specific databases that use that information to create enhanced DDL and hibernate configuration and mapping files.

I believe in a best effort approach.  Something inbetween "do it yourself" and "we provide optimized DDL and hbm's for all databases"

> add indexes to foreign keys
> ---------------------------
>
>                 Key: JBPM-735
>                 URL: http://jira.jboss.com/jira/browse/JBPM-735
>             Project: JBoss jBPM
>          Issue Type: Feature Request
>          Components: Core Engine
>            Reporter: Tom Baeyens
>         Assigned To: Tom Baeyens
>             Fix For: jBPM 3.2 alpha 2
>
>
> most databases don't generate implicit indexes for foreign keys.  so it's better to change the default and specify indexes on all foreign keys that can be used in joins.
> here's the mail conversation that led to this issue:
> > max, sorry for going out-of-bound.  david doesn't yet have access to 
> > salesforce.
> >
> > | No database I know of will create indexes by default for FKs, but 
> > | that doesn't rule it out I suppose.  I'd think that the risk is low 
> > | though.
> > | Even if it were the case, in my opinion it is up to Hibernate to 
> > | recognize the existing indexes and not generate DDL for them.
> No, not up to hibernate. Up to the DBA :)
> > should we consider creating the FK indexes in jbpm by default and only 
> > document on which databases this would create a problem of duplicate 
> > indexes ?
> That might be easier yes. I also don't know which db's that does create the index or not but in any case if it would always make sense to create the index why don't the database you know don't do it ?
> In any case, it is all controllable via the index attribute and if you find that the db's you deploy most to should have the index created then that makes sense for you.
> That said having a global or dialect controlled flag for it *might* make sense...but that is a future addition.
> /max
> > regards, tom.
> >
> > | -----Original Message-----
> > | From: David Lloyd 
> > | Subject: Re: New case comment notification. Case Number 00010803
> > |
> > | On Wed, 2006-08-16 at 09:20 +0000, Thomas Baeyens wrote:
> > | > Thomas Baeyens has added a comment to case 00010803 :
> > | "Foreign keys in JBPM Schema are not indexed?!".  Please read the 
> > | comment below and then click on the link to respond appropriately.
> > | >
> > | > Comment:
> > | > we didn't do this explicitly for PK's because most
> > | databases generate
> > | > these automatically.  i probably assumed the same was true for FK's.
> > | >
> > | > question to the hibernate team:  in general, should we be adding 
> > | > indexes on all our foreign key relations ?  is there are
> > | catch why we
> > | > wouldn't want to do this ?
> > |
> > | I would personally strongly recommend adding indexes for all foreign 
> > | keys.  The only downside is that they take up more storage in the 
> > | database, and they can slow down updates and inserts a little.  But 
> > | in the average case, the user doesn't know the difference, and 
> > | they're a lot more likely to complain about the vast slowness of a 
> > | full table scan due to a missing index than they are likely to 
> > | complain about inserts being 4% slower or whatever.
> > |
> > | > could it be that some databases generate indexes for FK's
> > | by default
> > | > and that the explicit index results in a second index being
> > | generated
> > | > and maintained ?
> > |
> > | No database I know of will create indexes by default for FKs, but 
> > | that doesn't rule it out I suppose.  I'd think that the risk is low 
> > | though.
> > | Even if it were the case, in my opinion it is up to Hibernate to 
> > | recognize the existing indexes and not generate DDL for them.
> > |
> > | My .02c.
> > |
> > | - DML
> > |
> > |

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.jboss.com/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira