Re: pySQLite Insert speed

On Thu, 28 Feb 2008 19:35:03 -0800 (PST), mdboldin wrote
I hav read on this forum that SQL coding (A) below is preferred over
(B), but I find (B) is much faster (20-40% faster)

(A)

sqla= 'INSERT INTO DTABLE1 VALUES (%d, %d, %d, %f)' % values
curs.execute(sqla)

(B)
pf= '?, ?, ?, ?'
sqlxb= 'INSERT INTO DTABLE2 VALUES ( %s ) ' % pf
curs.execute( sqlxb, values )

Any intution on why (A) is slower?

My only problem with (B) is that it should really be this:

sqlxb= 'INSERT INTO DTABLE2 VALUES (?, ?, ?, ?)'
curs.execute( sqlxb, values )

Apart from that, (B) is better than (A). The parameter binding employed in (B)
is not only faster on many databases, but more secure. See, for example,
http://informixdb.blogspot.com/2007/07/filling-in-blanks.html for some
in-depth explanations of why parameter binding is better than string
formatting for performing SQL queries with variable values.

HTH,

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