On Oct 14, 2010, at 5:43 PM, Jens Miltner wrote:
> I just stumbled across a problem where sqlite would be stuck for
> quite a long time inside sqlite3VdbeExpandSql when using bound BLOB
> parameters, i.e. my query looks like
>
> INSERT INTO foo VALUES (?,?,?,?...)
>
> and one of the parameters is a BLOB of about 700k.
>
> What I found is that when this query is executed, SQLite will
> actually produce a string representation of the BLOB, which is done
> using the following loop:
>
>> assert( pVar->flags & MEM_Blob );
>> sqlite3StrAccumAppend(&out, "x'", 2);
>> for(i=0; i<pVar->n; i++){
>> sqlite3XPrintf(&out, "%02x", pVar->z[i]&0xff);
>> }
>> sqlite3StrAccumAppend(&out, "'", 1);
>
> Here, sqlite3XPrintf will call sqlite3StrAcuumAppend, which
> essentially mallocs a new block that is 3 bytes larger than the old
> block, copies the old data, appends 2 characters (hex digits) and
> frees the old block.
>
> This looks like it would be *very* inefficient. I haven't noticed
> this problem before, so I'm not sure there are other conditions that
> cause this code path to be used now, but I thought I'd raise the
> question nonetheless to clear this issue.
>
> (I'm using SQLite 3.6.22 on Mac OS X).
>
> Any ideas / answers?
It only does that if you have an sqlite3_trace() hook registered.
If it's a problem, maybe you can clear the hook (by passing NULL
to sqlite3_trace()) temporarily while executing queries that use
large blobs. Or don't use it at all, if you can live without SQL
tracing.
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
