>My database weights a little less than 2 Gbs and contains 130'000 keys. >When I put it on HDD and try to make 10000 queries (extracting values for >10000 different keys) with some additional processing of extracted >values, it takes about 4 seconds on my PC on any run except the first, >with maybe half of that time being the "additional processing" mentioned; >even when I perform every query 3 times in a row (making it 30000 queries), >this time does not change. On the first run, thought, 10000 queries take >about 30 seconds!
Sounds normal as you are loading up the OS cache with data. Accessing data cached in RAM is much faster than accessing data on spinning disk. Have you set SQLite's page cache equal to a reasonable working set since access to application cached pages is even faster than access to the OS file cache -- and it avoids a jump into the kernel to do I/O which puts you at the mercy of the dispatcher. Are you 100% I/O bound during the long (initial) run, and 100% CPU bound during the quicker runs? >When I put the file on a USB flash drive, somehow I always get about 45 >seconds total time, on either first run and subsequent runs. When the >queries are tripled, the total time is tripled as well (even though the >portions of the file to be read should already be cached when every >enuqie query is repeated 2 more times). >This leads me to the conclusion that 1) The delays are produced by >physical reading of the file, not by searching for a key or returning >the value; >2) file on USB never gets cached (why?? due to some file-mode flags used by >SQLite engine? or it's just a MS Windows 8 issue?) This is an OS "feature". In order for caching to work on a USB attached device, it must not be mounted in "let the user pull it out at any time" mode (the default), or what Microsoft calls "optimize for random user yanking", but must be mounted as a connected device (called optimized for performance). You should also make sure you have it formatted as NTFS and not as a FAT variant of some type. Further, USB itself is very slow, both in transfer time and especially in turnaround time. Flash drives are also very slow, usually because they do not need to operate at a speed faster than can be supported by the slow USB interface. Is this a USB 1.0, 2.0, or 3.0 connection and device? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
