Robert Simpson wrote:
It's 100,000 more memory allocations and about 4.6mb of string copies
SQLite is doing behind the scenes. Every time SQLite prepares a
statement it allocates and copies the string. If you're inserting a
million rows into a database using the above "cheese" method (which I
hate but a lot of folks do anyway) then that's a pretty serious
performance hit IMO.
Robert,
When you are doing things this way the additional cost of the memory
allocations and string copies is much much smaller than the cost of
parsing the SQL and generating the VM code over and over. I suspect it
would be barley noticeable even if you actually measure it.
Doing things this way is a bad idea because you are doing most of the
work over repeatedly when you don't need to. The proposed change would
add a trivial amount of extra work for each iteration. Improving
performance in this situation is a classic example of where changing
your algorithm will provide a much greater return than micro
optimization to eliminate one malloc and one strcpy. You could optimize
this loop all you want, it will never be fast.
On the other hand, the extra allocation and copy that concerns you could
often be eliminated. If a new prepare API function was created to go
along with the enhanced step function, it could use the same type of
string destructor argument that the bind text call uses. This would
allow the caller to pass in an SQLITE_STATIC string and avoid the need
for SQLite to copy the SQL statement in many (if not most) cases. If it
ever needed to recompile the statement, it would be able to use the
string that the caller already had. SQLite would then only make its own
copy if it was passed SQLITE_TRANSIENT.
Dennis Cote
