Am 14.10.2010 um 12:56 schrieb Dan Kennedy:
>
> 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.
Ah - ok - that makes sense - that's why I didn't notice this earlier (I only
have tracing enabled for debugging purposes every now and then)...
Thanks for the explanation.
-jens
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
