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:[email protected]] Im
Auftrag von Rob Willett
Gesendet: Dienstag, 31. Juli 2018 16:31
An: SQLite mailing list <[email protected]>
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
>>> [email protected]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [email protected]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
