Dear all,
We think we have now found the issue with the slow commits.
We believe this is due to an inherent (and old) defect in our database
design. We think our original design has an implicit ordering of rows in
a table, when the table is only increasing this flaw in the design isn't
apparent.
However when we started deduping the table AND we copied rows from one
table to another to move things around, we changed the underlying order
of rows. Sqlite handles the design change BUT the flaw in our design
becomes apparent as we keep moving the data around and data gets mixed
up. The database slows down when we create a second table with an
identical structure to the first table, copy the data into the new
table, drop the old and then when we rename the old table to the new
table, things appear to slow down. Logically speaking SQLite shouldn't
notice the difference in row order, but things do slow down, even with
analyse.
We think that a better index definition could solve the problem for us,
a better database design would, but thats a tricky problem.
We're now going back to our 60GB database and start from scratch to see
if we can create the issue (now we think we know what it is).
Thanks to everybody who contributed ideas, we appreciate the help.
Rob
On 31 Jul 2018, at 15:19, Rob Willett wrote:
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
