topic Re: Query Scalability in Alfresco Archive

Query Scalability

brianshowers — Thu, 31 May 2012 22:22:23 GMT

I'm currently in evaluation mode to decide if Activiti is the right BPM solution for our project. From an API perspective, everything looks great so far. The APIs are intuitive, and the Spring integration is simple. However, I've noticed a problem with respect to scalability that is concerning.

Re: Query Scalability

heymjo — Fri, 01 Jun 2012 04:53:12 GMT

Interesting observation ! JDBC pagination is of course pure evil for the numbers you're running. Most database engines support LIMIT/OFFSET syntax that can easily be added to existing queries transparently so i'm a bit surprised MyBatis is not handing this translation natively.

In DbSqlSession there is this bit of code which seems to indicate that this problem has been thought of:


    if(databaseType.equals("mssql") || databaseType.equals("db2")) {
      // use mybatis paging (native database paging not yet implemented)
      loadedObjects = sqlSession.selectList(statement, parameter, new RowBounds(parameter.getFirstResult(), parameter.getMaxResults()));
    } else {
      // use native database paging
      loadedObjects = sqlSession.selectList(statement, parameter);
    }

But following this, native paging should work for pgsql and mysql. Surely one of the devs will clarify things up

Jorg

Re: Query Scalability

ronald_van_kuij — Fri, 01 Jun 2012 09:03:49 GMT

first of all, great analysis and post. I often see them much, much worse.

In the myBatis docs it says:

Different drivers are able to achieve different levels of efficiency in this regard. For the best performance, use result set types of SCROLL_SENSITIVE or SCROLL_INSENSITIVE (in other words: not FORWARD_ONLY).

Which might make a difference… But it would indeed be strange if it did…

In DbSQLSessionFactory there is

  @SuppressWarnings("unchecked")
  protected List filterLoadedObjects(List<Object> loadedObjects) {
    if (loadedObjects.isEmpty()) {
      return loadedObjects;
    }
    if (! (PersistentObject.class.isAssignableFrom(loadedObjects.get(0).getClass()))) {
      return loadedObjects;
    }
    List<PersistentObject> filteredObjects = new ArrayList<PersistentObject>(loadedObjects.size());
    for (Object loadedObject: loadedObjects) {
      PersistentObject cachedPersistentObject = cacheFilter((PersistentObject) loadedObject);
      filteredObjects.add(cachedPersistentObject);
    }
    return filteredObjects;
  }

That is also used in the selectList which might also influence things…

Yet, indeed the core devs should have a more detailed look at this as I do not have the time for now.

Re: Query Scalability

brianshowers — Fri, 01 Jun 2012 18:43:14 GMT

Thanks for the replies. I should have mentioned that I did my analysis using the latest official release of Activiti (v5.9). It looks to me like the code that @heymjo pointed out should in fact fix this issue, but it is currently just in the trunk codebase. Digging through the svn logs shows that the DbSqlSession snippet was added in ACT-1209 to fix this very problem.

As a user of MyBatis myself, I feel the pain. I've made the exact same mistake of assuming that MyBatis will do native SQL pagination when you pass a RowBounds when, in fact, it always just uses JDBC pagination (which, as pointed out, is for various reasons, evil).

I guess I'll just have to wait for 5.10 to be released before we can really consider integrating Activiti. As a noob question, does the Activiti release cycle ever cut any beta releases prior to the official release? Or do I have to choose between waiting for the official 5.10 release (I think it's currently not scheduled until August) or building my own copy off of the trunk bleeding edge?

Re: Query Scalability

ronald_van_kuij — Tue, 05 Jun 2012 06:20:57 GMT

No, there are no RC cycles… The trunk is usually very stable unless you use features marked experimental. You should be safe to test this (me being curious if it solves your ' problem')

Re: Query Scalability

jbarrez — Wed, 06 Jun 2012 14:33:41 GMT

Actually Daniel came to the same conclusion a while ago. Should indeed be fixed.