Thanks Keith. I did look on the performance page but didn’t realise clicking the items on the left brought up different info. I am on windows 10 pro.
I haven’t had time to try creating the ‘personalised’ sqlite3.exe so the following relates to my own app run under the original conditions (i.e. without your code changes). In that app, rather than asking sqlite to create the table of RowIDs, my code steps through the select and stores the RowIDs in either a vector or a temporary file. For storing to the tmpfile it stores the data in a vector of size 8192, writes those 8192 int64’s to the file using fwrite and so on in blocks of 8192. Note that if the procedure is run more than once without closing the app the same tmpfile is reused. The following relates to storing the RowIDs in a temp file run in 64 bit mode. select RowID from Test order by RowID; ----------------------------------------------------- FlushMem Cached < 1 GB Run During run Cached climbs in linear fashion to ~4.7 GB in 20.5 secs. Run again without closing app During run Cached increases slightly (5.4 max) then returns to 4.7 GB in 16.5 secs. Each subsequent run (without closing or flushing) returns similar results and ditto for flushing and going through the whole thing again. App closed down. Cached stays ~ 4.7 GB Run returns much the same results had it not been shut down. All of this is pretty much the results I’d expect – first run a bit slower than subsequent runs where the data is already cached. Nothing to see here imo. select RowID from Test order by RowID desc; ------------------------------------------------------------ FlushMem Cached < 1 GB Run During run Cached rises very slowly but never above 1.5 GB. When the procedure finishes running Cached is showing under 1 GB but at the moment it finishes it jumps to 5 GB. Time = 91.4 secs. Run again without closing app. During run Cached gradually reduces from 5GB to 1GB in linear fashion but bursts back up 5 GB when procedure finishes. Time = 16 secs. Each subsequent run (without closing or flushing) returns similar results and ditto for flushing and going through the whole thing again (first run again takes over 90 secs with Cached < 1 GB throughout but bursts to 4.7 GB as procedure finishes). Plenty to fathom here. We now switch to vector mode. Select is stepped through and each RowID returned is stored in the vector of size 100,000,000. The tmpfile is never created. NB I’m doing the desc select first this time just in case. select RowID from Test order by RowID desc; ----------------------------------------------------- FlushMem Cached < 1 GB Run During run Cached never gets beyond 600 MB (for about 20 secs it seemed frozen on 297 MB). When the procedure finishes it’s showing 600 MB then shortly after shows 4.8 GB. Time = 85.3 secs. Run again without closing app. During run Cached linearly decreases to 900 MB then bursts to 4.8 GB on completion. Time = 14 secs. Each subsequent run (without closing or flushing) returns similar results and ditto for flushing and going through the whole thing again (first run takes 96? secs with Cached < 1 GB throughout but bursts to 4.7 GB as procedure finishes). Similar to previous test. select RowID from Test order by RowID; ----------------------------------------------------- FlushMem Cached < 1 GB Run During run Cached climbs in linear fashion to ~4.7 GB in 63.3 secs. Unexpected time????? Run again without closing app During run Cached stays approximately the same (~4.7 GB) in 14 secs. Each subsequent run (without closing or flushing) returns similar results and ditto for flushing and going through the whole thing again (first run again took 60 secs). App closed down. Cached stays ~ 4.7 GB Run returns much the same results had it not been shut down (14s). This last unexpected set of results suggests it’s nothing to do with the select direction. Because it differed so much from the first test I went through test 4 several times but always got similar results. I also ran the first test (tmpfile version) another couple of times but, again, there was no change. It was the only one that gave me the expected results. I’m totally lost. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
