Using Stored Procedures in Rails

Question

As I said in a previous post, our Rails app has to interface with an E-A-V type of table in a third-party application that we're pulling data from. I had created a View to make the data normal but it is taking way too long to run. We had one of our offshore PHP developers create a stored procedure to help speed it up.

Now we run into the issue that we need to call this stored procedure from the Rails app, as well as provide searching and filtering. The view could do this because Rails was treating it as a traditional Rails model. How could I do this with the stored proc? Would we need to write custom searching and ordering (we were using Searchlogic)? Management is incapable of understanding the drawbacks of using a stored proc from Rails; all they say is that the current method is taking too long to load the data and needs to be fixed, but searching and filtering are critical functions.

EDIT I posted the code for this query here: Optimizing a strange MySQL Query. What is funny is that when I run this query in a GUI (Navicat) it runs in about 5 seconds, but on the web page it takes over a minute to run; the view is complicated for reasons I outline in the original post but I would think that MySQL optimizes and caches views like SQL Server does (or rather, how I read that SQL Server does) to improve performance.

Answer 1

You can call stored procedures from Rails, but you are going to lose most of the benefits of ActiveRecord, as the standard generated SQL will not work. You can use the native database connection and call it, but it's going to be a leaky abstraction. You may want to consider DataMapper.

Looking back at your last question, I would get the DBA to create a trigger to create a more relational structure from the data. The trigger would insert the EVA data into a table, which is the only way I know of to do materialized views in MySQL. This way you only pay a small incremental background cost on insert, and the application can run normally.

Anyway...

ActiveRecord::Base.connection.execute("call SP_name (#{param1}, #{param2}, ... )")

But there's an open ticket out there on lighthouse indicating this approach may not work with out changing some of the parameters to use the connection.