Sqlite3. Substitution of names in query.

[Carsten Haese]

Lawrence D'Oliveiro wrote:
> There is no such parsing overhead. I speak from experience.
>
> Look at the BulkInserter class here 
> <http://www.codecodex.com/wiki/Useful_MySQL_Routines>. I have successfully 
> used that to insert tens of thousands of records in just a few seconds. Yet 
> it makes no use of the parameter-substitution provided by MySQLdb; it is all 
> just straight Python, including the SQLString routine (also on that page), 
> which goes through every single character of each string value to decide 
> what needs escaping. Python can do all that, and do it fast.

With all due respect, but if your experience is exclusive to
MySQL/MySQLdb, your experience means very little for database
programming practices in general. Throughout most of its history, MySQL
did not support prepared statements and parameter binding, and MySQLdb
doesn't use any parameter binding API that might be available, so you're
comparing your own implementation of string interpolation to MySQLdb's
implementation of string interpolation. Your experience says nothing
about databases that have an *actual* parameter binding API.

> You don't get to figure out what's efficient and what's not by mere hand-
> waving;

I'm not handwaving.

> you have to do actual real-world tests.

I have.

See for example the timing test in
http://informixdb.blogspot.com/2007/07/filling-in-blanks.html . If you'd
like to try it for yourself, here is a version of the test for SQLite:

=================================================================
# querytest.py
class Tester(object):
   def __init__(self):
      import sqlite3
      conn = sqlite3.connect(":memory:")
      self.cur = conn.cursor()
      self.cur.execute("create temp table t1(a int, b int)")
      self.counter = 0

   def with_params(self):
      self.counter += 1
      self.cur.execute("insert into t1 values(?,?)",
                       (self.counter,self.counter*2) )

   def without_params(self):
      self.counter += 1
      self.cur.execute("insert into t1 values(%s,%s)" %
                       (self.counter,self.counter*2) )
=================================================================

And here are the corresponding results on my laptop:
$ python -mtimeit -s "from querytest import Tester; t=Tester()"
't.with_params()'
10000 loops, best of 3: 20.9 usec per loop
$ python -mtimeit -s "from querytest import Tester; t=Tester()"
't.without_params()'
10000 loops, best of 3: 36.2 usec per loop

So, you can say whatever you want, but you will never convince me that
string interpolation is better than parameter binding for getting
variable values into a query. Even if you don't accept my proof that it
is more efficient, you have not proved that parameter binding is less
efficient.

In addition to the efficiency factor, parameter binding is inherently
secure, whereas string interpolation is too easy to use insecurely.
Finally, parameter binding is the standard method, as defined by the SQL
standard, of getting variable values into a query.

You may call it "premature optimization", but I call it "choosing the
right tool for the job."

I assume that none of this will convince you, but that's fine. We'll
just agree to disagree on this.

--
Carsten Haese
http://informixdb.sourceforge.net