使用存储过程作为“动态”视图？

I want to create a "view" to eliminate the same three-line sub-query from about 90 queries in an app I'm working on.

我想创建一个“视图”，从我正在处理的应用程序中的大约90个查询中删除相同的三行子查询。

The problem is the sub-query contains a condition based on a variable.

问题是子查询包含基于变量的条件。

SELECT * FROM items WHERE id NOT IN (
  SELECT item_id FROM excluded_items WHERE user_id = 123
);

If it wasn't variable, I could simply make a view and be done with it.

如果它不是变量，我可以简单地创建一个视图并完成它。

I'm not sure what to do in this case though. Adopting the same mentality behind a view I'm tempted to make a stored procedure that returns the desired record set, so that it could be called something like this:

我不知道在这种情况下该怎么做。在视图背后采用相同的心态我很想制作一个返回所需记录集的存储过程，以便可以像这样调用它：

SELECT * FROM user_items(123);

Now I have a single place to update this item exclusion and any further conditions, however I'm not sure how indexing is affected if I want to join the results of that SP against other tables?

现在我有一个地方可以更新此项目排除和任何其他条件，但是我不确定如果我想将该SP的结果加入其他表格，索引是如何受到影响的？

So is this good/bad practice? Is there another way to do it, or should I just suck it up and keep replicating this sub-query?

那么这种好/坏的做法是什么？有没有其他方法可以做到这一点，或者我应该吮吸它并继续复制这个子查询？

4 个解决方案

#1

As usual your mileage may vary. If you are worried about this being a good practice in terms of your code syntax, I don't think it matters. It is a pretty normal thing to use a stored procedure to return record sets from and if it saves you development time - then why not do it? However, if you have determined that the cost to your query execute times is impacted in such a negative way that your business costs more than your productivity as a programmer, then by all means don't go with stored procedures.

通常你的里程可能会有所不同。如果您担心这在代码语法方面是一个很好的做法，我认为这不重要。使用存储过程返回记录集是非常正常的，如果它可以节省您的开发时间 - 那么为什么不这样做呢？但是，如果您确定查询执行时间的成本受到如此负面影响，以至于您的业务成本高于程序员的生产力，那么无论如何都不要使用存储过程。

I have heard a lot of banter over the years about stored procedures from people calling them evil to best practices. The conclusion that I have come to is as always use the right tool for the job.

多年来，我听到很多关于存储过程的人都把它们称为邪恶的最佳实践。我得出的结论是一如既往地使用正确的工具。

To determine how the change exactly affects performance, execute a few test queries using:

要确定更改如何影响性能，请使用以下命令执行一些测试查询：

EXPLAIN ANALYZE SELECT * FROM items WHERE id NOT IN (
  SELECT item_id FROM excluded_items WHERE user_id = 123
);

and then

接着

EXPLAIN ANALYZE SELECT * FROM user_items(123);

Then compare the execution times and the query plans. I think you will then be able to make a more informed decision.

然后比较执行时间和查询计划。我想你将能够做出更明智的决定。

#2

Did you try something like

你尝试过类似的东西吗？

create view user_items as (
  select i.*, u.id as user_id
    from (items i cross join users u)
      left join excluded_items e
      on (i.id = e.item_id
        and u.id = e.user_id)
    where e.item_id is null
);

already? I tested it with PostgreSQL 8.3, which is able to pull the condition on the user_id into the cross join if you use the view in simple queries like

已经？我使用PostgreSQL 8.3测试它，如果你在简单的查询中使用视图，它可以将user_id上的条件拉入交叉连接

select *
  from user_items ui
  where user_id = 1;

If your queries using this view become too complicated for the query optimizer to find the possibility to pull the condition on user_id into the cross join and the full cross join is calculated, then you can still play with some parameters of the query optimizer to get it pulled in again.

如果使用此视图的查询变得过于复杂，以至于查询优化器无法将user_id上的条件拉入交叉连接并计算完整的交叉连接，那么您仍然可以使用查询优化器的某些参数来获取它又拉了进来。

#3

I think the stored procedure solution is more DRY and really improves readability. Although I certainly prefer to use views where possible (especially with PostgreSQL's powerful rules), I just can't think of a nicer way of expressing this.

我认为存储过程解决方案更干，并且确实提高了可读性。虽然我当然更喜欢在可能的情况下使用视图（特别是使用PostgreSQL的强大规则），但我想不出更好的表达方式。

#4

-2

Having the same SQL in 90 places can be solved the client side too. For example, create a function that builds the SQL string:

在90个地方拥有相同的SQL也可以在客户端解决。例如，创建一个构建SQL字符串的函数：

public string SqlItemsForUser(int iUserId) {
    return "SELECT * FROM items WHERE id NOT IN ( " +
        "SELECT item_id FROM excluded_items WHERE user_id = " +
        Convert.ToString(iUserId) + ");";
}

You can call this function in 90 places, so if you have to change the subquery, you only have to change it in one place.

您可以在90个位置调用此函数，因此如果您必须更改子查询，则只需在一个位置更改它。

#1