search

jruby / activerecord-jdbc-adapter

JRuby's ActiveRecord adapter using JDBC.
BSD 2-Clause "Simplified" License
462 stars 386 forks source link

Performance issues with large result sets #545

Open joankaradimov opened 10 years ago

joankaradimov commented 10 years ago

Tested on: PostgreSQL: 9.1.12 activerecord-jdbcpostgresql-adapter: 1.3.5, 1.3.6, master jdbc-postgres: 9.3.1100, 9.3.1101 rails: 4.0.1 jruby: 1.7.10

The JDBC adapter is significantly slower than the postgres adapter in MRI. It seems that this is caused by large result sets.

I generated some fake data to test this. The users table I used had approximately 5000 rows and 30 columns.

In jruby (1.7.10 with jdbc adapter 1.3.6)

User.all.to_a         ~ 1700.0ms
User.all.pluck('id')  ~ 14.0ms
User.first            ~ 3.0ms

In MRI (2.1.0)

User.all.to_a         ~ 143.6ms
User.all.pluck('id')  ~ 5.2ms
User.first            ~ 1.2ms

I got these results from queries in the rails console on my development machine. There were no multiple runs or any real benchmarking here. However, the times remained consistent when running multiple times. I did ignore the first queries in the rails console as they were always slower (I am guessing some lazy DB connection initialization was happening there)

Calling the execute_query_raw method in arjdbc.jdbc.RubyJdbcConnection directly didn't lead to any improvement. I haven't done any profiling, but if I have to take a guess, I would say that it's either some terrible misconfiguration on our side, or the jdbcToRuby method in arjdbc.jdbc.RubyJdbcConnection is the bottleneck. The latter would mean that all adapters have this problem.

--- Want to back this issue? **[Post a bounty on it!](https://www.bountysource.com/issues/1446582-performance-issues-with-large-result-sets?utm_campaign=plugin&utm_content=tracker%2F136963&utm_medium=issues&utm_source=github)** We accept bounties via [Bountysource](https://www.bountysource.com/?utm_campaign=plugin&utm_content=tracker%2F136963&utm_medium=issues&utm_source=github).
joankaradimov commented 10 years ago

Sorry, this is a duplicate of https://github.com/jruby/activerecord-jdbc-adapter/issues/540

kares commented 10 years ago

Thanks Joan, we're just mostly getting out of the "make it correct" phase ... the next should be performance improvements, but we're low on contributors here (thus no promises). If you'd share the (simple) pieces of code where you setup the table and the data for the testing you did, that might turn out useful eventually.

GUI commented 10 years ago

I just posted this over in the other issue, but in case anyone else stumbles upon this and is interested, I put together a little benchmark suite to hopefully help out: https://github.com/GUI/activerecord-jdbc-adapter-benchmarks

It definitely seems to point to timestamp conversions being the main culprit at this point, but there might be other room for improvement. I also included a simplistic raw JDBC test script that performs similar queries just using JDBC and no ActiveRecord (results are at the very bottom of the readme). This obviously isn't a fair comparison, but I thought it might be an interesting reference point.