Sqlite3. Substitution of names in query.

[Lawrence D'Oliveiro]

In message <mailman.2442.1257115236.2807.python-list at python.org>, Carsten 
Haese wrote:

> Lawrence D'Oliveiro wrote:
>
>> Says someone who hasn't realized where the real inefficiencies are.
>> Remember what Tony Hoare told us: "premature optimization is the root of
>> all evil". These are databases we're talking about. Real-world databases
>> are large, and reside on disk, which is several orders of magnitude
>> slower than RAM. And RAM is where string parameter substitutions take
>> place. So a few hundred extra RAM accesses isn't going to make any
>> significant difference to the speed of database queries.
> 
> You're just not getting it. The cost is not in performing the parameter
> substitutions themselves. The cost is in parsing what's essentially the
> same query one million times over when it could have been parsed only
> once. You might find an increase of seven orders of magnitude
> insignificant, but I don't.

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.

You don't get to figure out what's efficient and what's not by mere hand-
waving; you have to do actual real-world tests.