rails/rails — pull request #10152 by Noemj (2013-04-09 14:32:48+00:00)
Title: Statement cache

The statement cache allows users to cache a single statement, which can then be executed for more optimizated performance.

Duping the relation returned from yielding a statement allows skipping the AST building phase, and the cached SQL can be used for the subsequent executions.

This solution will not be able to achieve the performance of find_by_sql however, as ARel has to be visited to fetch the cached SQL for the relation. This process takes some time.

I will attach the test result of a simple statement case, where building the AST might not take as much time as in a complex query.

Queries:

Non-cached
####
b = Book.where(:name => "my book").limit(100).to_a

Cached
####
cache = ActiveRecord::StatementCache.new do
Book.where(:name => "my book").limit(100)
end

b = cache.execute

SQL
####
b = Book.find_by_sql('SELECT * FROM BOOKS WHERE name = "my book" LIMIT')

TEST RESULTS

Table initialization: One matching variable with name = "my book", others with randomized name

The resuls are the individual times for a query, using a sample of 10000 queries

Table size 10

non-cached time
0.3672949429
cached time
0.3111773293
sql time
0.20331745149999997

Table size 100

non-cached time
0.3689390816
cached time
0.3414276569
sql time
0.2262848629

Table size 1000

non-cached time
0.4784290494999999
cached time
0.44194926990000005
sql time
0.3445473818

Table size 10000

non-cached time
1.7374471796000002
cached time
1.6614139447999998
sql time
1.5416361941999999

Table size 100000

non-cached time
14.0685073448
cached time
14.0078855941
sql time
13.8059879331