Take a look at the queries being run and do an "explain query plan" for each and look at the output to see what it decides to use. Look for "SCAN TABLE" cases that might benefit, or other oddities where it's doing anything you're not expecting.
-----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Rob Willett Sent: Tuesday, July 31, 2018 11:42 AM To: SQLite mailing list Subject: Re: [sqlite] [EXTERNAL] Re: Very, very slow commits - Possibly solved 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
