----- Original Message -----
From: "Dennis Cote" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Friday, November 04, 2005 8:48 AM
Subject: Re: [sqlite] Request for comment: Proposed SQLite API changes
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.
[snip]
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.
*I* know that, and *you* know that, but the idea is to minimize the impact
of inefficient code. This is somewhat akin to the ol' += string concat
operator ... so many people use it that you can't throw it out the window as
inefficient -- but you CAN change your underpinnings to minimize the impact.
As a wrapper writer I can't control how people code -- in my case I am bound
by the ADO.NET 2.0 architecture and have a standard that I cannot deviate
from.
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.
Yes, if one could specify the string was static or transient and if the
function were separate, then I'd be happy. As it is, I have inefficient
users of my wrapper, making trouble for themselves relying on .NET's garbage
collector and many developing on the Pocket PC where any extra memory
allocations and copies are bound to have a much higher performance impact
than the desktop.
Robert
