Some configuration of an SQLite database is stored in the database file. See the list of pragmata.

An easy check whether the file itself is problematic would be to copy a file from a "fast" machine to the "slow" machine and see if that makes a difference.

If it does, either that is enough, or you can look at whether the pragmata cache_size, fullfsync or journal_mode make a difference.

That did it. Apparently the DB file was corrupted in some way but threw no errors. re-copied the original DB file to the local machine and now it flies! 100,000 selects in 11.6 seconds. Pretty good for an old machine.

Thanks everyone!

«… corrupted in some way…»

About 30 reasons why this could happen

«The Crux of the Biscuit is the Apostrophe»

another test you can try is to time sql queries on machine 1 and 4 directly from the sqlite command line utility.

The DB is relatively small, but did you checked disks speed and or disk problem?

L*

Comparing two of the CPUs you mention there's significant single thread performance uplift, it's worth noting that the older part has significantly less CPU cache available, and the caching mechanisms are different, understandably. That said, once you've address some of the other points listed in the thread the sysinternals suite from Microsoft provides process monitor (procmon) and process explorer (procexp) which should help you in debugging.

G'day miner7777,

Welcome to the Monastery.

I'd suggest profiling your code on the different machines to try and determine where the bottleneck is on your fourth machine. "perlperf - Perl Performance and Optimization Techniques" has general information about this; it's "PROFILING TOOLS" section has more specific details.

Devel::NYTProf is highly regarded and considered to be one of the best profilers; however, I note in "CPAN Testers Matrix: Devel-NYTProf 6.12 (latest distribution)" a lot of failures when installing on MSWin. That doesn't mean it won't work for you; just be aware that it may not.

Update (fix stupid typo): s/That does mean it won't work for you/That doesn't mean it won't work for you/ Thanks GrandFather for spotting that.

A few things come to mind.

Basically, every time you do anything (select, insert, update), SQLite (just as any other database) rummages around in the datafile(s). Depending on how your filesystem is configured it can cause one or more writes to disk, because it may be updating the "last access" time of those files.

SQLite can also cause writes during read operation, because it supports concurrency as far as i remember. So it would need to write some lock information to disk. Here, it also depends on your file system setup as well as your hardware if these things get buffered in RAM with a delayed write or if the SQLite process is essentially blocked until the system can complete the write operation.

If you use DBD::SQlite, you can try to run everything using the filename ":memory:" to run an in-memory database. This way you can check if the problem is File IO or if this is a processor-optimization-gone-wrong kind of situation.

Write operations are especially "special" if you use some kind of RAID controller or a software RAID. There are all kinds of checksum-generation plus the whole business of having to send everything to multiple disks and waiting for them to finish.

Lastly, there may or may not be a patched security vulnerability with your AMD CPU on the slow system. There were a lot of these since your processor was designed (in 2016 if Google is correct). Meltdown, Spectre, Hertzbleed, etc. Most of the fixes by the manufacturers seem to be to disable all the features that made those processors fast. And if your copy of Perl or SQLite uses one of them (which now needs to be emulated in some other way by the processor or operating system), this can slow things down by a lot. That is another thing where testing with an in-memory database can really help, because it allows you to distinguish between IO-wait and data processing.

Thoughts? Ideas? Suggestions?

Yes. Don't make any major changes until our qualified folk have given feedback.

You've an excellent question here, well defined and laid out, and I'm confident it'll be sorted.

-stevieb

a 4GB indexed database containing one table with about a million rows

You realise you are talking about a ((100000/10)-(100/(60x10)))/(100/(60*10)) = 60,000 times performance difference.

yes, but it feels worse than that. :-)

I ran it overnight and it only did 7500 selects in something like 10 hours. So something is horribly wrong, although "sfc /scannow" says everything is hunky dory.

This is a read-only DB and is a simple copy from the original. All nonessential services have been disabled on Win (eg. search and more). I did look at procexp and procmon before posting.

I have an identical machine with Win 10 Pro installed and will test there. Also may test read/write speeds of csv files to see if there are obvious disk issues that Crystal disk can't see.

Big thanks to everyone for your help!

Did you verify that the disk is indeed formatted with NTFS and not FAT32? Win 7 Disk Management can tell you that.

Don't know about Crystal disk benchmarks. I would try something simple, how fast can the DB be read?
>copy yourDBname.sqlite NULL on Windows, NULL is a reserved file name that means "the bit bucket".
See if there is any difference between the computers...

Perhaps some experimentation to see what kind of cliff you are hitting might be interesting...Make a DB 1/10 the current size and benchmark that - see if the 400 MB file compares ok against the other machines or not.

There are two programs called XSETUP and Starter, which are tweaking tools that I use to change certain startup settings. I think, these can be downloaded for free. Starter is a tiny program that is worth installing. It shows a comprehensive list of user apps that start everytime Windows starts: https://www.softpedia.com/get/Tweak/System-Tweak/Starter.shtml XSETUP is a bigger program and allows a lot more than just performance tweaking: https://download.cnet.com/X-Setup-Pro/3000-2094_4-10439607.html

"If you have a bunch of startup applications, then no wonder it's slow..."

OP is talking about program execution performance, there's nothing to suggest that windows start up time is a problem (on a 'modern PC', it shouldn't be). The tools linked to seem to be for operating systems that have past End of life.