在Rails中使用存储过程 [英] Using Stored Procedures in Rails
问题描述
正如我在上一篇文章中所说，我们的Rails应用程序必须与我们要从中提取数据的第三方应用程序中的E-A-V类型的表进行交互.我创建了一个View，以使数据正常，但运行时间太长.我们有一位离岸PHP开发人员创建了一个存储过程来帮助加快存储过程.
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.
现在，我们遇到了需要从Rails应用程序调用此存储过程以及提供搜索和过滤的问题.之所以可以做到这一点，是因为Rails将其视为传统的Rails模型.如何使用存储的proc执行此操作?我们是否需要编写自定义搜索和排序(我们正在使用Searchlogic)?管理人员无法理解使用Rails中存储的proc的缺点；他们只说当前方法加载数据花费的时间太长，需要修复，但是搜索和过滤是关键功能.
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.
编辑，我在此处发布了此查询的代码:优化一个奇怪的MySQL查询.有趣的是，当我在GUI(Navicat)中运行此查询时，它运行大约5秒钟，但是在网页上运行却需要一分钟以上.该视图很复杂，原因是我在原始帖子中概述了一些原因，但我认为MySQL可以像SQL Server一样优化和缓存视图(或者更确切地说，我如何看待SQL Server的视图)以提高性能.
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.
推荐答案
您可以从Rails调用存储过程，但是由于标准生成的SQL无法使用，因此您将失去ActiveRecord的大部分好处.您可以使用本机数据库连接并调用它，但这将是一个泄漏的抽象.您可能要考虑使用DataMapper.
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.
回想一下您的最后一个问题，我将让DBA创建一个触发器，以根据数据创建更多的关系结构.触发器会将EVA数据插入表中，这是我所知道的在MySQL中进行物化视图的唯一方法.这样，您只需在插入时支付少量的增量背景成本，应用程序即可正常运行.
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.
反正...
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.
这篇关于在Rails中使用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
