Simon,
Apologies for taking so long to get back, we've been building a test
system and its taken a long time.
We're just getting round to trying your ideas out to see what difference
they make,
We've created a new table based on your ideas, moved the collate into
the table, analysed the database. We did **not** add COLLATE NOCASE to
the columns which are defined as integers. Would that make a difference?
We've found it now takes around 10% longer to do the queries than
before.
Rob
Please try moving your COLLATE clauses into the table definition.
e.g. instead of
CREATE UNIQUE INDEX "Disruptions_Idx3" ON Disruptions ("version"
COLLATE NOCASE ASC, "Disruption_id" COLLATE NOCASE ASC, "location"
COLLATE NOCASE ASC);
Your table definition should have
"version" integer NOT NULL COLLATE NOCASE,
"Disruption_id" INTEGER NOT NULL COLLATE NOCASE,
...
"location" integer NOT NULL COLLATE NOCASE,
and the index should be
CREATE UNIQUE INDEX "Disruptions_Idx3" ON Disruptions
("version" ASC, "Disruption_id" ASC, "location" ASC);
Once data has been entered, do ANALYZE. This step may take a long
time.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
