#1494 ✓resolved

Performance problems with STI

Reported by baccigalupi | March 16th, 2011 @ 08:14 PM | in 1.1.1

We use a command pattern to capture our history or a feed for our users. As such, we have built a STI structure for our activities. Currently we have about 20 different activities.

We are using DataMapper version 1.0.2

Unfortunately when we do any request related to getting all activities, the generated SQL mandates that they be of a type which includes all our activity types:
SELECT ... FROM "activities" WHERE ... ("type" IN ("ActivityType1", .... every singe activity type) );

The table is large, and this extra precaution is slowing down our queries by about 40%. Perhaps because these requests are ofter sub-select statements in postgres, we haven't been able to find an index that will address the slow down.

What we would really like is to not have this precautionary WHERE clause in our queries.

Is this a feature or fallout from design decisions. We are happy to fix this problem if it is not for some reason considered a feature.

Comments and changes to this ticket

  • Dan Kubb (dkubb)

    Dan Kubb (dkubb) March 16th, 2011 @ 11:47 PM

    • State changed from “new” to “confirmed”

    So what you're saying is that when you're querying the top-most parent class, including the discriminator in the query is unnecessary? I would agree with this.

    Keep in mind that it would still be necessary when querying for any subclass that it include an IN() clause with it's class name and all it's descendants' class names. If the class hierarchy is relatively flat, then this shouldn't be an issue, but if it's narrow and deep it still might be -- although I don't see any other way of getting correct results any other way.

    I would love to see a patch for this optimized behaviour if you're willing to contribute it.

  • baccigalupi

    baccigalupi March 17th, 2011 @ 04:48 PM

    We branched, wrote a test, and deleted the one line, and committed. All the specs pass. Unfortunately, we haven't been able to confirm the speed boost because of a combination of 1.1 not working with our app and gem dependency hell. We are going to make the change on version 1.0.2 as well and commit it locally until 1.1 seems more usable.

    Please do add this change though.

    github: http://github.com/cohuman/dm-core
    commit: 6a96521f7f7e7547fb2c

    I have made a pull request as well.

  • baccigalupi

    baccigalupi March 17th, 2011 @ 06:48 PM

    We made these changes to our locally packaged gem (the best short term solution for us), and we saw a 16% performance boost in our full application stack, so this kind of improvement really matters!

  • Dan Kubb (dkubb)

    Dan Kubb (dkubb) March 17th, 2011 @ 10:09 PM

    • State changed from “confirmed” to “resolved”
    • Milestone set to 1.1.1
    • Milestone order changed from “196340” to “0”

    This has been committed to dm-core: https://github.com/datamapper/dm-core/commit/4562aae8cba28740c6e256...

    @baccigalupi: I'm curious to know what problems you had with 1.1? We reduced a lot of the dependencies between different gems, so the general situation should be significantly better than 1.0.2. Is the problem more that other gems specify dependencies that are lower than what DM gems are tested with?

  • baccigalupi

    baccigalupi March 18th, 2011 @ 09:53 AM

    Awesome. I am glad that it got in.

    Yes, we are using gems that require lower gem dependencies. We were stuck in an addressable/builder Bundler loop for a while. It took a couple hours of haggling with Bundler, but we finally fully upgraded. The real deal breaker was that dm-paperclip which has not progressed since the summer just wasn't working at all. So we are going to have to craft our own adapter for paperclip or find another library if we want to move to 1.1.

  • Jared Morgan

    Jared Morgan March 18th, 2011 @ 10:51 AM

    As a replacement for dm-paperclip, you might check out https://github.com/jm81/attach/tree/binderclip. It's very much in process, but it includes a DataMapper adapter that is usable (I've used it with 1.0.2 and 1.1.0). If nothing else, it might provide some help if you develop your own adapter. To use it, add the following to your Gemfile:

    gem 'paperclip', '2.3.8'
    gem 'binderclip', '0.3.0', :git => 'git://github.com/jm81/attach.git', :ref => 'e053617', :require => 'binderclip/orm/data_mapper'

    (Sorry for the largely off-topic comment)

  • Piotr Solnica (solnic)

    Piotr Solnica (solnic) March 18th, 2011 @ 10:54 AM

    Or you can check out my fork of dm-paperclip which works with latest DM: https://github.com/solnic/dm-paperclip

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »