I've been following this thread with interest, but this just doesn't make sense...
> Logically speaking SQLite shouldn't notice the difference in row order, but things do slow down, > even with analyse. Are you accessing each row via its ID? Even so, that should still be indexed. I thought you were simply adding records into the database - I'm failing to grasp how this is slowing down in the new database. Thanks, Chris On Tue, Jul 31, 2018 at 3:30 PM Rob Willett <[email protected]> wrote: > 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 > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
