Custom Mysql queries slower than ActiveRecord in production

[fipa]

I have been working on optimizing the performance of a Rails app by
rewriting the AR record methods into custom sql queries. What I did is
that the result from the query is not instantiated as an object, which
is supposed to be too time consuming.

Here are the performance difference between using default AR methods and
its custom equivalent. The number of requests per second was read from
the log file, it is not super accurate but gives a relative figure which
is fine.

The first figure is the default AR method, the second one is the custom
query.

In development mode:
find(:all): 15 req/s -> 30 req/s. Wow I get twice as much throughput.

In production mode:
find(:all): 80 req/s -> 60 req/s. Damn’it!

I don’t understand why in production mode, my custom sql query yields
less performance than the AR methods.

What am I missing?

[fipa]

On Apr 21, 2008, at 11:05 PM, Fernando P. wrote:

I don’t understand why in production mode, my custom sql query yields
less performance than the AR methods.

Are you, perchance, defeating AR query caching by writing the custom
SQL?

[fipa]

Hi Steve,

You nailed it! I completely forgot about query caching. I guess it might
be turned off in production mode.

Therefore writing custom queries is only efficient for operations to the
DB that will only be run once such as creating a new entry or retrieving
data across many tables.

I have to learn exactly how query caching is implemented.

Thank’s a lot for pointing this out.