去sql - 准备语句的范围 [英] Go sql - prepared statement scope

问题描述

我使用Go（1.6.x）sql包与PostGres（9.4）一起构建API。我的预备陈述是否有申请或要求范围？阅读完文档后，在应用程序级对其进行调整以减少准备阶段的数量似乎更为有效。但是，也许还有其他的考虑和准备好的陈述并非旨在长期存在？

解决方案

准备好的语句可以执行重复性的SQL命令，例如参数值可能不同。

它们并不意味着长时间生活，因为预先准备的语句可能（它们在事务中调用时）会保留活动的数据库连接（long意味着它们不被使用时的意思;即使这需要很长时间，重复执行准备好的语句也是很好的）。连接是一种昂贵的资源，只能根据需要进行保存。只需创建一堆准备好的语句而不关闭它们，就可以用完主动/允许的连接，然后阻止与数据库服务器的进一步通信。

使用准备好的声明如果你想执行相同的插入， update 或选择



有一篇文章（由Myles McDonnell在下面的评论中发布）进入准备好的语句。它提到，如果预处理语句不是从事务创建的，它们会将连接释放回连接池，但在需要时，它们会尝试重复使用它们准备的连接（因为如果db服务器辅助/也扮演主动角色在准备好的语句中，它绑定到服务器端的连接）。如果没有，他们会重新准备他们在一个新的连接（导致不良的性能开销）。

总而言之，你所描述的是一个工作模型，如果您在很多后续请求中需要/执行的准备好的语句数量较少，则可能意味着响应时间缩短。但是，这也意味着另一方面，从长远来看，它们可能会导致所有准备好的陈述都将在所有关联池中进行准备。决定你的情况是否可以接受。

通常应该避免这种情况（并且在HTTP请求结束之前关闭预准备语句），但是如果你只有其中有几个，而且你确实需要它们在多个请求之后，你可以将它们移出请求范围。

I'm building an API using the Go (1.6.x) sql package along with with PostGres (9.4). Should my prepared statements have application or request scope? Having read the docs it would seem more efficient to scope them at the application level to reduce the number of preparation phases. However, perhaps there are other considerations and prepared statements are not designed to live that long?

解决方案

Prepared statements are so that you can execute repetitive SQL commands which may only differ in parameter values for example.

They are not meant to live "long" as a prepared statement may (they do if called from a transaction) reserve an active database connection ("long" means when they are not used; it is perfectly fine to repetitively execute a prepared statement many times even if this will take long). A connection is an expensive resource and should only be hold as long as needed. Just by creating a bunch of prepared statements and not closing them, you could run out of active / allowed connections and then block further communication to the db server.

Use a prepared statement if you want to execute the same insert, update or select statement with different parameters multiple times in one (HTTP) request. Do not use prepared statement to outlive (HTTP) requests.

In certain driver implementations and database servers prepared statements may also involve resources allocated on the DB server itself (not in the Go application). For example a prepared statement may be pre-compiled on the DB server and the server might prepare query execution plan, allocate certain resources such as memory for it. These may be permanently reserved until the prepared statement is closed.

There's an article (posted by Myles McDonnell in comments below) going into the implementation details of Prepared Statements in Go. It mentions that if prepared statements are not created from transactions, they release the connection back to the connection pool, but when needed, they try to reuse the same on which they were prepared (because if the db server aids / also plays an active role in prepared statement, it is bound to the connection at the server side). If not, they will re-prepare them on a new connection (causing undesirable performance overhead).

All in all, what you describe is a working model, and if you have a low number of prepared statements which are needed / executed in many subsequent requests, they can mean shorter response times. But it also means that on the other hand on the long run, they might result in that all your prepared statements will be prepared on all the connections of the pool. Decide whether this is acceptable in your case.

In general this should be avoided (and prepared statement be closed before the end of the HTTP request), but if you only have a few of them and you do need them in many requests following each other, you may move them out of request scope.

这篇关于去sql - 准备语句的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！