Windows SQLite is SUPER SLOW

Granted my WIndows box is not a top flight machine…

but my app from start to main window display takes 1.23 seconds under macOS

but an astonishing 31.3 seconds under Win10. both machines doing the exact same operations

  • verify database exists (which for testing it does not)
  • create database
  • create all required tables and views (about a dozen)
  • populate with fake data for testing (about 30 records)

now the Windows run is via the remote debugger, would that make this kind of difference???

Also check your transactions. I’ve seen poor transaction design be REALLY slow on Windows but work well on Mac.

Well I narrowed it down to two sets of transactions that insert reference values ito their respective tables
each are sub-second on Mac and 10secs each of Win

the first creates 69 records…

  • Select count of existing
  • if zero then insert
    other are pretty much the same… nothing complex… just multiple times

You set SQLite cache to a good size?

whatever the “default” is…
doesn’t the cache affect individual queries… each of these is a Simple Insert of 4 or 5 fields (multiple times)

I have changed one set to use INSERT OR IGNORE instead of searching for an existing record… this sped up a tiny bit

Default is just 2 MB cache.

Solved : Wrapped the list of queries in “BEGIN TRANSACTION” … went from 10+seconds to <1

total startup time for Win10 is now 4.25 seconds
and for macOS it is 1.5 seconds

Windows is slow without Begin Transaction and mac works great without it, this on my app too.

And this is in the documentation (but nothing about the Windows Slow down). Been annoyed by this kind of things is… boring at minima.

[quote=455877:@Dave S]Solved : Wrapped the list of queries in “BEGIN TRANSACTION”
Good point. Now I wish there was an equivalent solution for opening and reading Excel files.