pySQLite Insert speed

[Diez B. Roggisch]

mdboldin at gmail.com schrieb:
> 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?

You most certainly have not found that A is the preferred over B - the 
opposite is true. Using A will make you vulnerable against 
SQL-injection-attacks. B OTOH will ensure that the parameters are 
properly escaped or otherwise dealt with.

Regarding the intuition - that depends on what actually happens inside 
B. If B works in a way that it

  - converts arguments to strings

  - escapes these where necessary

  - builts one SQL-statement out of it

  - excutes the SQL

then B is slower than A because A is just string-interpolation, whereas 
B is sanitizing + string-interpolation. So it must be slower.

But a "sane" DB will instead directly use the SQL passed in B, and 
transmit the parameter as binary into the backend, resulting in more 
compact representation + lesser or now marshalling overhead plus 
possible query parsing overhead reduction due to cached execution plans. 
Which could explain B being more performant.

Diez