Hi people,
I though automatic index was controlled by the pragma `automatic_index`, which
seems to do nothing for me, unless I'm doing it the wrong way, while `WITHOUT
ROWID` on table creations, seems to effectively prevent automatic indexes, the
latter with or without the `PRAGMA automatic_index=0`.
Is this expected or is this me doing something wrong?
I'm concerned about it mainly for file size, more than for speed, while it
matters too, as the program is far more slow than expected. For a program I'm
running I got this:
* With ROWID: execution time is 22 seconds and DB file size is 1.9MB?;
* Without ROWID, execution time is 19 seconds and DB file size is 788KB (2.4
times smaller)?;
* With insertion disabled, execution time is 6 seconds, so most of the
execution time is spent in rows insertion.
The program indexes HTML files (*), the test runs on only 52 average sized HTML
files, so the difference will be even more important with bigger files set.
I'm feeling lost about this: the pragma seems to do nothing and it's `WITHOUT
ROWID` on table creations, which seems to do it instead. The pragma is executed
right after the cursor creation. I'm using APSW (**). Pragma seems to works, as
I tested with `PRAGMA synchronous=0` (just for a test, I really don't want to
use this).
I don't know if it's OK to ask for this kind of question here. If it's not,
just tell me, I won't bother.
(*) There are bout 103 000 insertions queries ending into 15 600 rows, using
this:
CREATE TABLE element_attribute_values
(element TEXT NOT NULL,
attribute TEXT NOT NULL,
value TEXT NOT NULL
CHECK ((attribute IS NOT '') OR (value IS '')),
count INTEGER NOT NULL DEFAULT 1,
PRIMARY KEY (element, attribute, value))
INSERT OR REPLACE INTO element_attribute_values
(element, attribute, value, count)
VALUES (:element, :attribute, :value,
(SELECT count + 1 FROM element_attribute_values
WHERE (element IS :element)
AND (attribute IS :attribute)
AND (value IS :value)))
(**) Standard Python SQLite3 binding seems perceptibly slow. With APSW, the
program is 17 times faster than with standard Python SQLite3
binding???execution time is around 370 seconds in the latter case, whatever how
I manage transactions.
--
Yannick Duch?ne
