pySQLite Insert speed

[Carsten Haese]

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