I am trying to understand why one of the queries in my rails application works quite significantly. I am using Postgres 9.3 and rails 4.0.3 with jRuby 1.7.10, so what is the potential problem with the JDBC driver?
But basically, this is a VERY simple request:
SELECT * FROM table;
The table contains rows 851 , so this is hardly a massive data set, so I expect a quick query. When I execute this request in pgAdmin 3, I get exactly what I expect: all rows are returned within 15 to 35 ms. Nice and fast!
Of the rails, however, this is a different story. Running the request in the rails console, the fastest I was able to achieve, is 189ms , although it is usually around the 200ms sign. This query is executed by calling Table.all
My initial thought was that ActiveRecord adds overhead when creating 851 objects, which clearly slows it down. To test this, I ran
ActiveRecord::Base.connection.execute("SELECT * FROM table")
There was a slight acceleration, but then again, almost all requests took 150ms , still far from the pgAdmin label. As a last attempt, I tried
ActiveRecord::Base.connection.exec_query_raw("SELECT * FROM table")
But that did not improve performance at all.
I am really very worried about why this is going much slower, given that I can see a 10 times performance drop between pgAdmin and Rails. By running only the original SQL in Rails, I know that this is not an ActiveRecord slowing down, so I really got confused about what is.
Does anyone know why this is so slower than it should be?
UPDATE
I did a few more digging, and the rails seem to handle Date fields. If I manually select all the columns in the table, it will be just as slow, but if I select all of them except updated_at and created_at , the query will be executed within 2-4 ms, which is perfect!
Now my only problem is how to get around this. Is there a way to fix the problem with rails with dates or does the existence of rails not analyze them as dates and save them as strings or similar?
UPDATE 2
So, after I did more digging and @stonehz pointed out the error raised from this post, I upgraded to Jruby 1.7.12 and rails 4.1.0 and noticed significant speed. This is not much closer to pgAdmin's performance, but I think that without completely deleting the date columns, I'm not going to do anything better. Below is the test I'm getting right now
SELECT *: 4.080000 0.330000 4.410000 ( 5.243000) SELECT date_fields: 1.960000 0.020000 1.980000 ( 2.032000) SELECT * - date_fields: 3.070000 0.070000 3.140000 ( 3.247000) --------------------------------------------------------- total: 9.530000sec user system total real SELECT *: 3.700000 0.060000 3.760000 ( 4.663000) SELECT date_fields: 1.790000 0.020000 1.810000 ( 2.021000) SELECT * - date_fields: 2.330000 0.060000 2.390000 ( 3.180000)
This landmark requests 851 lines. The first test is a simple SELECT * . The second test selects only date fields, and the final test selects all fields except date fields. Each query is executed 100 times to get the final result.
As shown in the figure, the SELECT * now takes only 4 seconds to run 100 times, so each request takes only 40ms , which is much closer to pgAdmin ~ 30 ms. MUCH BETTER!