I ran these each 5 times with similar results. The new query API
is faster than the 2.3x API if you use "where", but much slower if
you use "find". Will try to run thru debugger if I get a chance
today.

drjoke - I'm not seeing a similar performance difference on my
Ubuntu laptop with postgresql. Rails 3 seems about 20% slower here.
What hardware and db engine are you using?

John - I see similar performance diffs between find() and
where() in dev mode, but not in production mode. I suspect from
looking at profiles that this is because find() is performing a
more expensive query compilation than where but that this
difference disappears in production mode due to query caching. Do
you see the same performance delta when you run in development and
production mode or only in development mode?

I am using MySQL, and am seeing this same problem both on Amazon
EC2 running Ubuntu and Macbook Pro running snow leopard. The
performance difference is similar in both instance.
Now using the Rails 3 new ActiveRecord API yield very very fast
result.

I apologize, but that last test is incorrect. The queries were
not even executed.. I did not realize it was lazy loaded. I added a
print statement so the query gets executed and the results are
about the same for both queries now. So "where" is not performing
much different than "find" on Rails 3.

Another test w/ Rails3 edge instead of beta4 gems. Performance
is better in Rails 3 edge, but still not as good as 2.3.2. But
acceptable for my app.

@drjoke - Sorry about my earlier test with "where". It was
incorrect b/c the queries were never executed... I hope you didn't
start converting your queries as a result. Have you tried
benchmarking your app with edge Rails?

@Miles Egan - performance is slightly better in production mode
in edge.

So I'm seeing rails 3 as almost twice as slow doing a find() but
significantly faster with a where(). This is on a desktop
workstation though so the I/O characteristics might be very
different from an EC2 instance.

Daniel - if you look at the benchmark code I posted I access an
attribute(name, in this case) of each instance returned by where()
and use it to calculate a running total. So it looks to me as if it
is actually performing the queries. Is this not the case?

@Miles - At first glance it appears that it executes the
queries, but take a look at this:

p User.where(:id => rand(5000) + 1).class
ActiveRecord::Relation

p User.where(:id => rand(5000) + 1).name
"User"

Your query returns an ActiveRecord::Relation object and
coincidentally the name method returns a 4 character string
("User") also. Its just like calling User.name. You need to call
.first or [0] to get the User model and execute the SQL
query.

Rohit I've attached two traces from ruby-prof on this benchmark,
prof2.txt for rails2 and prof3.txt for rails 3. I'm no expert at
ruby profiling either but the calls to the Arel::SqlCompiler kind
of jump out from the profile. It would be great if somebody more
familiar with the arel/AR code could look at this.

In the benchmarks above, the value we should look at is (real).
That said, we had a performance decrease by moving from 20s (2.3)
to around 24s (using Rails master). This is expected since we moved
to ARel and ActiveRecord today share both the old and new code (as
soon as we deprecate and remove it, performance will likely
improve).

Also, notice we already have some benchmarks inside activerecord
that we've been using extensively. Nonetheless, the profiling
results helps, so I'm assigning to Emilio to see if there is
something we can cache help in Arel::SqlCompiler.

I feel we should look at total, which is the third number,
because it signifies the total CPU consumed to process this
request.

In real deployment, especially in my case with AWS EC2, I am
concern about the CPU consumed because I would have multiple ruby
threads processing requests in parallel. All these threads will add
up to the total CPU used by the EC2 instance.

(real), the wall clock time, is influence by other factors like
network latency or even MySQL delay. It makes the slowness in the
Rails 3.0 code itself appear a lot less significant. Also, it would
not signify the concern that people will have in real deployment
with CPU cycles.

@Jose I have another question as well. Do you expect things to
improve in Rails 3.0 final?

When you said "(as soon as we deprecate and remove it,
performance will likely improve)" are you talking about removing
the old .find API before things start improving? If that is so, it
will be in Rails 3.1 or after?

Results are in the attached spreadsheet (2 sheets). I just
included total time (self), total % of time (%self), and method
calls. Rails 3 has significantly more method calls - 661,902 vs
399,990. And total time - 4.09 vs 2.39

There are alot of calls to Class.new, #method_missing, etc..
Performance may be taking a hit from some of the meta stuff and
arel in general.

However I also compared run time for basic calls to
User.find_by_sql and ActiveRecord::Base.connection.execute and I'm
already seeing almost twice as long runtimes there between Rails
2.3.5 and Rails 3 Edge. Note: my database is actually empty while
running these tests.

@drjoke : Removing the old API won't have any affect on the
performance. Under the hood, old API is using the new finder
methods already ( Check Relation#apply_finder_options ).

@Daniel : Difference in find_by_sql time is because of an
instrumentation call in AbstractAdapter#log. If you remove
ActiveSupport::Notifications stuff from there, you'd see completely
different results.

Finder performance hit in AR 3 is mainly because of ARel and
ActiveSupport::Notifications to a very minor extent. So, patches
please :)

@Patrik : Unfortunately you're right. I could squeeze out a tiny
performance gain by changing the code a little, but most of the
remaining difference comes from @block.call(*args) in the
Subscribers 'push' method.

I was able to shave a little of the overhead off the arel code
by explicitly defining all the methods that are called via
method_missing in arel/lib/arel/engines/sql/relations/compiler.rb,
but it didn't seem like it was enough to really make it worth
it.

I don't think it is needed to define all the methods. What I
tried to do before with little performance impact was to call the
methods directly on the relation and change the method there from
protected to public. e.g instead of

I did basically the same thing Daniel. It seemed to shave a
little overhead off but not much.

As far as I can tell there's not a single big bottleneck in the
Arel code. It's just operating at a higher level of abstraction
than the old AR code and that comes at a price, at least in this
case.

I think it would be good to consider a few more benchmarks than
just this one trivial case though. It's not clear to me how the
performance of Rails 2 and Rails 3 compare in this respect over a
lot of real-world use cases.

Daniel I think it's worthwhile to run each of your benchmark
tests at least 10 times and take some averages. When I do this on
my machine I get a fairly wide spread of results so I think one run
isn't enough to judge the effectiveness of a patch.

After my refactor, the "log" method performs at the same speed
as 2.3.x, and the find_by_sql() method performs faster than 2.3.x.
I can't speak for the performance of arel, but maybe that should be
a different ticket?

Rails 3 lazy loads a lot of stuff, this means that ActiveRecord
and ActionController gets loaded just on the first request. This is
why it takes longer (probably ActionView is being loaded).

Second, benchmarks using development (with cache classes) and
considering just one request, are very poor. You need to use
benchmarking tools like httperf or apache benchmark to have an
average result for a bunch of whole requests.

Rails 3.0.0 (standard deviation is high, but tests would take
too long for more conns)

Thanks, this is already much better! Just one thing, be sure to
leave the first request out (although I don't think it should
change something, probably just the max) and use a good server, as
thin/mongrel. We should not use Webrick in such benchmarks! :)
Thanks!

We already did a bunch of improvements since beta 4. The only
way we can improve it even more is by profiling the app you
generated and see if we find any hotspot.

Btw, taking a look at your script, I saw you are using
/posts/index in your requests to a Rails 3 app. Beware that Rails 3
does not include the defaults routes, so you may as well be
rendering a 404 page.

We spent the past year and a half improving the internals of
Rails. Because there is so much new code, there is also lots of
obvious, low-hanging optimizations still to be done. We will
continue to release pre-releases of Rails that might have
unacceptable performance characteristics, and we will continue to
improve the performance of the various systems as we move toward
3.0 final.

Part of the reason for the performance issues are new systems
like pervasive instrumentation and escaping by default. We have
been able to reduce the impact of some (but not all) of these new
systems to almost zero. ActiveRelation is a new system that still
needs to undergo significant performance tuning.

Make no mistake, though: we will not release Rails 3.0 final
that is slower, for the common case, than Rails 2.3. In order to
achieve this, we need the help of the community to provide us with
repeatable benchmarks that demonstrate the specific performance
problem that people are encountering. We have some of our own
benchmarks and apps, but anything you guys can do would help.

The RC2 was released, so I figure I'd better update this ticket
with progress.

I am currently optimizing simple queries like
Post.find(1). The currently release candidate of Arel
is approximately 36% faster than before I started playing with it.
I was able to get a 36% speed improvement through "superficial"
performance improvements. Things like converting to
attr_(reader|writer), removing unnecessary conditional tests, using
faster methods, removing inject, reducing call stack, etc.

There are a few issues with Arel that cause it to be slow.
Unfortunately, these are due to a fundamental design problem with
the current Arel implementation. The problem is kind of long to
explain, so I may just blog about it rather than bury it in a
ticket. There are probably other "superficial" speed increases I
can do, but they are becoming more difficult to find.

I think that the only real way we can see good improvements is
through a fundamental design change. I've already put together an
"experimental" branch of ARel to prove my theory. I believe this
branch is the only way forward. I've included benchmarks from the
experimental branch. It's slightly faster than Rails 2.3.x, and I
believe after I've finished making the tests pass, we can improve
it to be even faster than 2.3.x.

Please help by sending me internet hugs, high fives, best
wishes, and patches. I'll continue to move forward and update this
ticket as I go along. Thanks for your patience and support!
<3 <3 <3

From my benchmarks, it seems that the performance of
ActiveRecord is nearly the same as 2.3.x:

Unless there are objections, I'd like to close this ticket as
"close enough". I will continue to improve the performance of
ActiveRecord, but I think those performance improvements must be
made inside AR rather than ARel.

Great news! Was just testing this out for the SQL Server
compiler and noticed I could not bundle rails 3-0-stable and arel
master using my ENV vars for the path due to the version in Arel
still being 1.0.1. I changed lib/arel/version to 2.0.0 and got the
local bundle working. Something you may want to change if indeed
rails/arel at master is indeed the 2.0 version.