Gunter,
Thanks for this. We have already started on this as we realised that the
COLLATE NOCASE was irrelevant and actually slowed down integer queries.
What we have not done is reorder the table to match the index queries.
This had not occurred to us.
We think we have already created the indexes so that we 'cover' the
queries. One advantage (!) of a 200M row table is that inefficient
queries are immediately obvious as we have to do a table scan.
We have not checked that the order of columns in the index match the
ORDER BY clauses. We never thought of that either,
Thanks for the help.
Rob
On 31 Jul 2018, at 16:21, Hick Gunter wrote:
Based on the currently available information I woudl suggest the
following schema:
CREATE TABLE IF NOT EXISTS "Disruptions" (
"id" INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT,
"version" integer NOT NULL,
"Disruption_id" INTEGER NOT NULL,
"status" integer NOT NULL,
"category" integer NOT NULL,
"subCategory" integer NOT NULL,
"location" integer NOT NULL,
"levelOfInterest" integer NOT NULL,
"corridor" integer NOT NULL,
"severity" integer NOT NULL,
"startTime" TEXT NOT NULL COLLATE NOCASE,
"comments" integer NOT NULL,
"currentUpdate" integer NOT NULL,
"CauseAreaPointX" real NOT NULL,
"CauseAreaPointY" real NOT NULL,
"endTime" text NOT NULL COLLATE NOCASE,
"remarkTime" TEXT NOT NULL COLLATE NOCASE,
"lastModTime" TEXT NOT NULL COLLATE NOCASE,
"Direction" TEXT COLLATE NOCASE
);
CREATE INDEX "Disruptions_Idx1" ON Disruptions
("location","corridor","status","category","severity","levelOfInterest","version","subCategory","startTime");
CREATE UNIQUE INDEX "Disruptions_Idx2" ON Disruptions
("Disruption_id","version","category","subCategory");
CREATE UNIQUE INDEX "Disruptions_Idx3" ON Disruptions
("version","Disruption_id","location");
CREATE INDEX Disruptions_Idx5 ON Disruptions
("status","Disruption_id","Severity","levelOfInterest","category","subCategory","version");
Remarks:
COLLATE is only required for text values. If you always store data of
the declared type, COLLATE has no business with a non-text column.
I have reordered the fields so that fields used in one or more indexes
are at the front of the record. This allows SQLite to quit decoding
the record faster when building index strings. I have assumed that
each index is used/updated equally often; if you have an index that is
used most, reordering the fields may help processing speed.
Non-index fields should be ordered so that fields that feature
prominently in the retrieved data come first; again, this will allow
SQLite to quit decoding the record earlier when reading data.
It may also improve performance to create a "covering index" by adding
the (one or two) fields retrieved to the index used for locating the
record. This allows SQLite to retrieve these fields directly from the
index BTree without referring back to the table BTree.
I assume the order of the fields of each index matches the order of
the fields in the ORDER BY clause(s) of the queries that use the
respective index.
-----Ursprüngliche Nachricht-----
Von: sqlite-users
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
Rob Willett
Gesendet: Dienstag, 31. Juli 2018 16:31
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: [EXTERNAL] Re: [sqlite] Very, very slow commits - Possibly
solved
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
___________________________________________
Gunter Hick | Software Engineer | Scientific Games International GmbH
| Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013
| (O) +43 1 80100 - 0
May be privileged. May be confidential. Please delete if not the
addressee.
_______________________________________________
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
