From what little you reveal I assume this is some kind of datalogging application. I also assume there will be a primary key (call ?) and also suspect that there are a number of secondary indices for data retrieval. Since you make no mention of transactions, I must infer that you are using autocommit, i.e. the data is committed to disk for each and every row.
For the primary key, only the "rightmost" page of the b-tree will be affected; for other indices, it is quite likely that random pages (different for each row) will be hit. In this scenario, increasing the page size means that 4 times as many sectors need to hit the disk surface for each statement. To increase speed, consider committing the inserts at regular intervals (1 per second maybe?), so that disk updates (which is where the time is spent) happen only once every "quite a few" records. Larger pages then have a chance of getting filled and may also improve the locality of writes, i.e. fewer seeks and more consecutively written sectors. You might also consider having a logger thread that writes into alternating table(s) (e.g. even and odd seconds' data) with INTEGER PRIMARY KEY and no other indices; and a transfer thread that copies the entries over into the "real" table in a batch transaction. -----Ursprüngliche Nachricht----- Von: Andy (KU7T) [mailto:k...@ku7t.org] Gesendet: Montag, 19. Jänner 2015 09:06 An: sqlite-users@sqlite.org Betreff: [sqlite] Page_size increase from 1k to 4k made my "REPLACE INTO" slower. why? Hi, I am using System.Data.SQLite, Version=1.0.92.0. I read that increasing the page_size to 4k on modern OS is a good thing and should speed things up. However, I have a particular query that takes substantially longer. I tried to make sure that nothing else is changed, so I am a little puzzled. Can anyone explain why this is? Query is this, in case this gives a clue: REPLACE INTO PacketSpots (Call, TS, Band, Freq, QSXFreq, Comment, Spotter, Bearing, CountryPrefix, Sect, ZN, Exchange1, GridSquare, MiscText, Name, NR, Continent, Sunrise, Sunset, Latitude, Longitude, QualityTag) VALUES (@Call, @TS, @Band, @Freq, @QSXFreq, @Comment, @Spotter, @Bearing, @CountryPrefix, @Sect, @ZN, @Exchange1, @GridSquare, @MiscText, @Name, @NR, @Continent, @Sunrise, @Sunset, @Latitude, @Longitude, @QualityTag) SqliteParameters not show. The query used to be sub 1 ms, now it is 28 ms. Since I get quite a few calls per second, this is a big change. Anyone any ideas or tips? Thanks Andy _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
