Guy Hindell <[EMAIL PROTECTED]> wrote:
>
> In promoting SQLite 3 (still v3.2.7, so a bit behind the current
> release) for a new project I have been doing some performance tests and
> SQLite generally looks very good. However, I have seen one surprising
> result.
>
> My schema is simple, a single table with a simple autoincrement rowid as
> the only index. Deleting all the rows from a table populated with
> ~60 rows using simply DELETE FROM Foo takes twice as long as DELETE
> FROM Foo WHERE 1. This is odd -
> http://sqlite.org/capi3ref.html#sqlite3_changes - would lead me to
> expect the opposite. So, I tried DROP TABLE Foo, and sure enough, the
> time it takes is the same as the DELETE with no WHERE clause specified -
> twice as long! Odd.
>
Version 3.3.16 is roughly 10 to 20 times faster than prior
versions of SQLite 3 for "DROP TABLE" and "DELETE FROM table"
with no WHERE clause. If the speed of these operations is
important to you, then you should definitely upgrade.
Also note that the AUTOINCREMENT feature of SQLite works
a little differently than on MySQL. If all you need is
automatically generated IDs that are different from all
existing IDs, then AUTOINCREMENT is not necessary and
in fact slows down your INSERTs. The AUTOINCREMENT
keyword causes SQLite to auto-generate IDs that are both
unique over all existing entries, and over all prior
entries that have been deleted. Without the AUTOINCREMENT
keyword, if you delete a row then create a new row, the
new row might reuse the unique ID from a previously deleted
row. With AUTOINCREMENT, you are guaranteed that the
automatically generated rowid will never have been used
before in that table.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
