[sqlite] Degradation of performance in SQLite 3?

[Ulrik Petersen]

Hi all,
has anyone experienced less performance with SQLite 3.0.7 over 2.8.13 on 
the same data?  That is what I am experiencing.  I'd appreciate help in 
figuring out why and perhaps what I can do about it.

I have a linguistic database (it's from my project, Emdros 
http://emdros.org/) and the relevant parts of the schema look like this:

CREATE TABLE clause_monad_ms (
   object_id_d INT NOT NULL,
   mse_first INT NOT NULL,
   mse_last INT NOT NULL,
   is_first CHAR(1) NOT NULL DEFAULT 'N',
   PRIMARY KEY (object_id_d, mse_first)
);
CREATE TABLE clause_objects(
   object_id_d INTEGER PRIMARY KEY,
   first_monad INT NOT NULL,
   last_monad INT NOT NULL,
   mdf_text_type TEXT NOT NULL ,
   mdf_number_within_sentence INT NOT NULL ,
   -- etc etc. plus other data for the clause
);
-- similarly for phrase
CREATE TABLE word_gut(
   object_id_d INTEGER PRIMARY KEY,
   first_monad INT NOT NULL,
   last_monad INT NOT NULL,
   mdf_word_number INT NOT NULL ,
   mdf_verbal_tense INT NOT NULL ,
   -- etc etc. plus many other columns containing word-data
);
CREATE INDEX clause_mm_monads_i
ON clause_monad_ms
(mse_first, mse_last);
CREATE INDEX clause_mm_o_i
ON clause_monad_ms
(object_id_d);
CREATE INDEX clause_o_fm_i
ON clause_objects
(first_monad);
CREATE INDEX clause_o_lm_i
ON clause_objects
(last_monad);
CREATE INDEX word_g_flm_i
ON word_gut
(first_monad, last_monad);
My application, Emdros, is a query engine for linguistic data.  I have a 
suite of Emdros queries that I run to test the speed of Emdros. Emdros 
translates these queries into a series of SQL queries.

I've ported Emdros over to SQLite 3.0.7, but experienced between 27% and 
49% speed degradation over 2.8.13.  This is on a 137MB database with 
around 1.7 million rows in various tables (up to 430,000 rows in one 
table, namely the word_gut table).

I use the sqlite3_prepare/step interface, with sqlite3_column_XXX calls 
to get the data.

Has anyone experienced anything similar?  Can anyone suggest ways I 
could improve the above schema and/or indexes?

Thanks in advance.
Ulrik Petersen
PS: Kudos to Dr. Hipp and all the contributors for making SQLite such
a worthwhile and pleasant piece of software to interact with.
--
Ulrik Petersen, Denmark
Emdros - the text database engine for analyzed or annotated text
http://emdros.org