While that makes sense Clemens it again fails to explain why it does no caching
when desc is stepped through first. I am at least satisfied that it’s not
something I should dwell on or, more the point, it isn’t down to a bug in my
code which is why I got embroiled in it in the first place.
For completeness here’s the ‘ac-dc’ figures
for (int i=0; i<5; i++)
{
for (int j=0; j<10000000; j++) sqlite3_step(asc);
std::cout << "After asc " << FreeMBs() << std::endl;
for (int j=0; j<10000000; j++) sqlite3_step(desc);
std::cout << "After desc " << FreeMBs() << std::endl;
}
10mill Ascending - 10mill Descending
After asc 12324
After desc 12334
After asc 11391
After desc 11434
After asc 10484
After desc 10484
After asc 9534
After desc 9535
After asc 8585
After desc 8586
Tom
________________________________
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of
Clemens Ladisch <clem...@ladisch.de>
Sent: Thursday, May 31, 2018 2:06:08 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] This is driving me nuts
x wrote:
> Why would window’s increase it’s cache size during step ascending then
> reduce it during step descending in such a consistent manner?
Windows has several different caching strategies.
<https://msdn.microsoft.com/en-us/library/windows/desktop/aa363858.aspx>
says:
| FILE_FLAG_RANDOM_ACCESS
|
| Access is intended to be random. The system can use this as a hint to
| optimize file caching.
|
| FILE_FLAG_SEQUENTIAL_SCAN
|
| Access is intended to be sequential from beginning to end. The system
| can use this as a hint to optimize file caching.
|
| This flag should not be used if read-behind (that is, reverse scans)
| will be used.
|
| If none of these flags is specified, the system uses a default general-
| purpose caching scheme.
SQLite does not specify these flags.
Apparently, the default general-purpose caching scheme tries to detect
what access pattern the application uses.
The rows are stored in rowid order in the file, so the ascending scan
looks as if it might be a sequential scan. This means that Windows will
prefetch following data, but it does not know if some data will be read
again later, so it will keep the data in the cache.
The descending scan looks like a reverse scan. By that point, Windows
probably assumes that you are definitely using sequential scans, so it
thinks the data can be thrown out of the cache after you've read it.
Regards,
Clemens
_______________________________________________
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
