Simon,
As an exercise we have just added in COLLATE NOCASE to our integer
columns.
Whoops! We thought this would make no difference but its added extra 70%
to our processing speeds.
We've now got to the stage where we can make changes quickly, so we'll
back that change out and go back to the integer defn without COLLATE
NOCASE.
Rob
On 31 Jul 2018, at 14:59, Rob Willett wrote:
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
