Go prepared statements vs querying directly

The common consensus appears to be that directly queried statements don't allow parameters, and that prepared statements do.

However in Go's database/sql package, you are allowed to use the ODBC parameters and send parameters to such things as db.QueryRow() and db.Query(). So it appears that they are functionally equivalent.

That being said, what is the point then, of first creating a statement, and then executing it? Let's say statements compile against the database first -- doesn't that increase load and thereby reduce performance since you're adding an extra trip? And since you can get parameters from Query/QueryRow, wouldn't that make statements a bad thing?

Keep in mind that the database/sql package is a package designed to cover the functionality of all theoretically possible SQL database systems without getting in the way of platform-specifics.

SQL driver instances are injected at runtime through sql.Register. These drivers do not necessarily build on ODBC, it could be something else.

Some SQL-databases support escaping query parameters, some don't – some support "compiling" of prepared statements, others don't.

By making it a 2-step process, the driver can decide where and how to escape query parameters and how they will be inserted into the query.

> you are allowed to use the ODBC parameters and send parameters to such things as db.QueryRow() and db.Query()

The methods have this signature for convenience only, in the background the driver still escapes and queries separately. You can see this happen here and here, respectively.

In theory, when a database server receives a simple SQL statement (or batch) it has to compile it: parse it into some internal form and then prepare the so-called "query plan" — the sequence of operations (such as index or table scans, comparisons etc) to carry out actual request. Doing all this obviously costs cerain server resources. So many DBMSes started to support "prepared statements": the server does the parsing/planning step exactly once and hands you off "a handle" onto the result which you subsequently "call" multiple times, just supplying different parameters.

Now let's move to a more complicated realm.

First thing to note is that when memory and processing power got cheaper, DBMSes running on commodity to high-end servers became able to afford to spend more resources when dealing with queries, so some of them cache user queries. That is, when you do a simple query (an SQL statement or a batch) the server does all the usual parsing/query planning but then it saves the results and if it later encounters the same query, it skips the processing part and just performs operations on the data.

The second thing to note is that while programming languages/libraries tend to present the programmer with a certain common interface for accessing database engines (ODBC for C is one example, the database/sql for Go is another, and there are myriads others), the wire protocols or other means of actual accessing the servers may differ drastically. For instance, one database server might support passing parameters along with the query in its wire protocol while another one might not and so the access layer have to turn your parameters into escaped SQL literals and embed them into the query which is then sent to the server.

The main idea you should aquire from all this: the database engines are different and you have to know the ins and outs of yours if you are planning for putting intensive load on it by your programs. Know what the wire protocol of your DMBS can do and what it can't, know if your DBMS is fast at connection establishing or not etc etc etc. Look for documentation on performance optimization of your particular DBMS.

See also this and this for some discussions of different DBMSes.