DrySQL: Not All I Had Hoped

I happened upon DrySQL the other day and I was immediately interested. DrySQL is an add-on to the standard ActiveRecord support in Rails that uses a lot more of the meta-data in the database to generated the model classes. The standard ActiveRecord classes basically just use the column names to create accessor and modifier methods for the models. DrySQL takes the idea of using the database schema definition as a source of information much further. It figures out the primary key looking for the primary key column in the database, rather than a column named ‘id’. It setups up validations based on the data type of the columns in the database. It defines joins based on the referential constraints. So basically DrySQL is trying to make ActiveRecord what it should have been from the beginning.

I was really happy when I found that someone is working on improving ActiveRecord. I have always been annoyed by in the inconsistencies of Rails ActiveRecord. For example, Rails ActiveRecord requires some of the meta-data to reside in the DB and some to reside in the code has bugged me. Even worse your are actually required to duplicate some of the meta-data by the standard ActiveRecord library, for example validations.

I use the past tense for my happiness because today I installed DrySQL and I am no longer all that happy. It may well do exactly what it says, which I will just assume, but unfortunately I don’t have time to really test it. The reason I do not have time to test it is that it adds a least twenty four (24) seconds to the response time for every single request. One of the simplest requests the application handles goes from

Completed in 0.18736

to

Completed in 24.24459

simply by adding require_gem 'drysql' to environment.rb.

That twenty four seconds seems to be to build the User model. Requests that instantiate other models incur even greater overhead. This kind of performance hit is completely untenable. While this overhead would be less of an issue in production because this generation will only be done once per model class it is still a complete show stopper for me, and I suspect for most other people. Really, who can give up thirty seconds of their life every single request, even in their development environment?

There is a possibility, and I really hope this is the case, that this is performance issue really just artifact of my environment. I am running a fairly recent version of Edge Rails (a couple of weeks old), postgres 0.7.1 (installed as gem) and postgresql-8.1 all on Edgy. I would love to hear about it if you see a problem with my setup (or if you are using DrySQL without these issues).

Even though this version of DrySQL does not seem to work for today I remain hopeful. DrySQL is definitely the way ActiveRecord should work and there is no reason generating the model class should take an excessive amount of time. Perhaps the next version of DrySQL will be more performant.

2 thoughts on “DrySQL: Not All I Had Hoped

  1. Hi Peter

    You’re absolutely right…DrySQL’s performance with PostgreSQL is abysmal. My benchmarks revealed an average time of 15 seconds per model class for DrySQL to reflect on the information schema and retrieve the class’s table metadata, and then fetch a row from the table. As you mentioned in your post, this becomes less worrisome in long-running production apps because the DB reflection is only done once per model class, but I do believe that performance is an issue that needs to be addressed.

    As for tweaking your environment, the only thing that comes to mind is choosing a Ruby-PostgreSQL driver. I had difficulty getting the Windows native driver to work in my environment, so I chose a pure Ruby driver in the interest of time since I only use PostgreSQL for testing. I’m told that the pure ruby driver is considerably slower than the native one, which may explain the poor performance I experience.

    I have admittedly neglected PostgreSQL in favor of other DBMSs that I have had more incentive to support, but I do plan to address performance with PostgreSQL as soon as I can. DrySQL’s performance with most of the other DBMSs that it supports is considerably better than with PostgreSQL (i.e. less than a second for the same benchmark test with Oracle or MySQL), but I still plan to address the performance in those as well.

    In the short term, my plan is to work on optimizing the queries that DrySQL uses to retrieve DB schema information, and to offer user-configurable DB reflection so that programmers can choose when and how they would like DrySQL to reflect on their DB schema.

    Thanks for trying out DrySQL. If you care to share any more benchmarking information or offer any suggestions for improvement, I’m sure that readers of the DrySQL forums on RubyForge (myself included) would appreciate it.
    http://rubyforge.org/forum/?group_id=2438

    Cheers.

    • Bryan
  2. Hi Peter

    I just wanted to follow up with you about your performance issues with DrySQL. I have just released a new version of the gem (0.2.0), which yields considerably better performance with all supported databases (in my test environment).

    As well, the latest release introduces the ActiveRecord::Base.generate_orm method, which will generate the associations and validations for all your model classes on demand, for those who prefer to take the performance hit upfront rather than incrementally during the application’s uptime.

    DrySQL is still a work in progress, but hopefully you’ll find the latest release to be an improvement over the previous.

    Cheers.

    • Bryan

Comments are closed.