"Anish Enos Mathew" <[EMAIL PROTECTED]> wrote: > Well, currently I am doing a mobile project and we are testing the > performance of four databases depending on the time it takes to insert > or delete or search a particular data from the database. I am using > sqlite3 database. I am using sqlite_exec command for doing a particular > process in the data base. But the problem with sqlite_exec is that the > sqlite engine should parse the select or insert statement and then > produces the result. If we are having a low level API for doing the same > which doesn't takes parsing time, we could directly do the process > rather than giving the select or insert statements. So I would like to > know is there any other method which can be used to retrieve or insert > data's from the database other than using the select or insert commands. >
You can access the database unpublished low-level APIs, but this is labor intensive, exceedingly error prone, and very likely to break in one SQLite release to the next. This approach is definitely not recommended. A better method would be to call sqlite3_prepare() on parameterized SQL statements at application startup. Then use sqlite3_bind_*() to bind parameters to the various SQL statements, call sqlite3_step() to run them, then sqlite3_reset() to ready them for another run. This completely bypasses the parser, except for the handful of calls to sqlite3_prepare() that occur when the program is launched. Note however, that if your database schema changes, or if you call VACUUM or ATTACH, then you will have to rerun all of your sqlite3_prepare() statements afterwards. If you never change your database schema and you never call VACUUM or ATTACH, then you can compile your SQL statements in advance (at application build time) and never invoke the parser at all. In this scenario, the parser can be completely omitted from the SQLite library, reducing the library footprint to as small as 60KiB. That can be very helpful for embedded devices with extremely tight memory and processor constraints. The calls to sqlite3_prepare() can occur on the development workstation and the low-power embedded processor only has to invoike sqlite3_bind(), sqlite3_step() and sqlite3_reset(). This approach requires a proprietary add-on, however. One final note: If you are comparing INSERT performance, be sure to do your SQLite INSERTs from within a transaction. SQLite typically does about 50000 to 100000 INSERTs per second (on a workstation) but only about 60 transactions per second due to disk rotational latency. Many people Try to do a series of raw INSERTs using SQLite, see that only about 60/second occur, and assume that INSERTs are slow. This is not the case. It is the implied COMMIT that occurs after each INSERT that is slow. If you do a BEGIN, followed by thousands of INSERTs, then a single COMMIT, things will go much, much faster - literally thousands of times faster. -- D. Richard Hipp <[EMAIL PROTECTED]>
