<p>Is doing:</p>
<pre><code>stmt, _ := db.Prepare("...")
for _, val := range slice {
stmt.Exec(val)
}
</code></pre>
<p>Faster than:</p>
<pre><code>for _, val := range slice {
db.Exec("...", val)
}
</code></pre>
<p>I used <em>Exec()</em>, but it could have been <em>Query()</em> or <em>QueryRow()</em>.</p>
<p><strong>Subsequent issue</strong></p>
<p>Why am I asking this?</p>
<p>I'm trying to abstract a database, which I can summarize by this interface:</p>
<pre><code>type Data interface {
Insert(o Object)
Get(id int) *Object
}
// Without preparing
func main(){
for _, o := range objects {
data.Insert(o)
}
}
</code></pre>
<p>So, if preparing queries is faster, then I must take advantage of it, but how to keep the abstraction when using statements? Like this?</p>
<pre><code>type Data interface {
InsertObjectStatement() *Statement
Insert(s *Statement, o Object)
GetObjectStatement() *Statement
Get(s *Statement, id int) *Object
}
// Preparing
func main(){
stmt := data.InsertObjectStatement()
for _, o := range objects {
data.Insert(stmt, o)
}
}
</code></pre>
<p>I would love to hear how experienced gophers are dealing with <em>database/sql</em> abstraction!</p>
<hr/>**评论：**<br/><br/>lobster_johnson: <pre><p>Depends on the database. Preparing the query will essentially send the query to the database server once, then any subsequent invocation will only send the parameters. This saves parsing time, and a little bit of time/bandwidth spent transmitting the SQL query. </p>
<p>However, the performance benefit is typically negligible, especially if your query isn't very complicated. PostgreSQL, for example, will not cache a query plan for a prepared statement until it has been run a certain number of times; even then, query plans usually take less than a millisecond to compute.</p>
<p>As with anything, you should measure the actual benefit and determine if it's worth doing.</p>
<p>As for your abstraction, the preparing should be done as an implementation detail. Your first interface is good, not the second one. The first interface can store the prepared statements as private members of its struct:</p>
<pre><code>type DataImpl struct {
getStatement *db.Stmt
insertStatement *db.Stmt
// .. etc
}
</code></pre>
<p>Last point: If you care about performance, then grouping statements in a transaction is much more important. If you execute updates without a transaction, then a separate transaction will be created for each update.</p></pre>grutoc: <pre><p>Clear and precise, as for your implementation, it might just be what I was looking for, thank you!</p></pre>naturalselection: <pre><p>Typically it is, for prepared statements, the upfront cost is quite high but subsequent queries essentially just send the parameter data compared to parsing, analysing if using straight SQL.</p></pre>bonafidebob: <pre><p>Depends on whether you re-use the statement.</p>
<p>Exec does the equivalent of prepare if you pass it text. For simple statements the preparation time (parsing the SQL and optimizing the execution plan) are small, so no huge savings. For complex statements this can be significant, but if you don't reuse the statement it won't matter.</p></pre>QThellimist: <pre><p>By re use the statement does multiple go routines count. In example I have a webserver and the same query is called multiple times a second but in different go routines. Does this count?</p></pre>bonafidebob: <pre><p>Yes, this counts as re-using the statement.</p></pre>QThellimist: <pre><p>Then using prepare statements for almost all queries for a web app makes sense. Is there a benchmark showing how much optimizations prepare statements do?</p></pre>bonafidebob: <pre><p>This depends on the database. Measure it!</p></pre>grutoc: <pre><p>Okay, In my case I want to reuse the statement above the abstraction layer, and it looks like a mess...</p></pre>bonafidebob: <pre><p>It's not much of an abstraction if you expose the statement...</p>
<p>Create the api you want and keep the statements private (if you use them.)</p></pre>drvd: <pre><p>Maybe you should just measure it?</p></pre>dinkumator: <pre><p>Somewhat off-topic / on-topic, but if you want reasonable performance on a (large) batch of inserts, you'll want to do them in a transaction anyway. So when you do that, you can embed the prepared statements inside your wrapped transaction's struct. For small sets of inserts the difference is negligible to performance.</p></pre>grutoc: <pre><p>Thank you, I'm going to do so.</p></pre>beefsack: <pre><p>This is the sort of question to ask in a database community; preparing statements is a database command and not really specific to Go.</p></pre>FIuffyRabbit: <pre><p>I think you should learn more about databases themselves because this isn't a go specific question. If you are rune the query more than once, then yes preparing will save you time and increase throughput for the application immensely. It doesn't matter what db engine you are using because the concept of preparing applies basically the same to all of them.</p></pre>
