Anish Enos Mathew wrote:
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.
Anish,
You should definitely look at the sqlite pre-compiled query mechanism.
It uses the following set of API functions to allow very fast repeated
execution of SQL statements by eliminating the SQL compilation process
for all but the first execution.
sqlite3_prepare()
sqlite3_bind_*()
sqlite3_step()
sqlite3_reset()
sqlite3_column_*()
sqlite3_finalize()
You are doing inserts into a table, so you would prepare an SQL insert
command with parameters for the values to be inserted. For example if
you have a table as shown below.
create table t (
id integer primary key,
a text,
b integer
);
You would prepare the following insert statement once using
sqlite3_prepare(). The ? characters are placeholders for parameter values.
insert into t values (?, ?, ?);
This will parse the SQL once and give you a compiled SQL statement that
you can execute many times with different values bound to the
parameters, without ever parsing anymore SQL. The prepared statement
only works for the specified table. If you have multiple tables, you
will need to prepare multiple statements, one per table. These prepared
statements can be kept and reused or the duration of your program
(assuming the schema isn't changing).
For each execution you issue a series of sqlite3_bind_*() calls to
supply the values to be used for the parameters, and the call
sqlite3_step() to execute the insert. Finally you will call
sqlite3_reset() to reset the statement so that it is ready to be
executed again. When you have no further need for the prepared statement
you call sqlite3_finalize() to release the memory held by statement.
The same mechanism can be used to prepare and execute SQL queries as
well. After the query is parsed, you bind the parameters that specify
the lookup conditions, and execute the query. The sqlite3_step()
function will return after each result row is located. You then use the
sqlite3_column_*() functions to read the values of the selected columns
for that row, and then call sqlite3_step() again to get the next row.
This mechanism practically eliminates the cost of parsing SQL
statements. If this doesn't meet your needs, than you should probably
look at a different database library since SQLite doesn't have a stable
published API for its b-tree layer. There are many other b-tree packages
that would be a better fit than SQLite if that is the level at which you
need to access your data.
Dennis Cote
