----- Original Message -----
From: "Dennis Cote" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Thursday, November 03, 2005 4:14 PM
Subject: Re: [sqlite] Request for comment: Proposed SQLite API changes
Robert Simpson wrote:
This is one gives me pause. I see lots of people doing things like this
in their code (Using C# since I address this in my wrapper's helpfile and
its convenient):
using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
{
int n;
for (n = 0; n < 100000; n ++)
{
mycommand.CommandText = String.Format("INSERT INTO [MyTable] ([MyId])
VALUES({0})", n + 1);
mycommand.ExecuteNonQuery();
}
}
I try to discourage this sort of thing, but so many people do this and
they'll be hit with a stiffer penalty under the proposed change.
IMO schema change errors are an outside case, and preventing them by
introducing this change would only penalize users that have already
either coded around this issue or don't experience it at all.
Robert,
I don't understand why you think your users will pay a stiffer penalty
under the proposed change. In your case the database schema isn't
changing, so there will be no SQLITE_SCHEMA errors to handle. The only
cost is the cost of storing a copy of the SQL string for the lifetime of
the prepared statement. In your case I assume that the statement is
prepared, stepped, and finalized in your ExecuteNonQuery function. So it
would only require additional memory for one copy of the SQL string for
the length of time that that function takes to execute. That storage would
be released when the prepared statement is finalized. I don't think this
is a severe penalty for any user.
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
