We have 3 machines: One has Windows server OS 2012-r2 installed with decent specs (12 GB RAM, 3.6 GHz, 4 cores, 600 GB hard disk). The others are home laptops with regular specs of Ubuntu 20.04 & MacOS. All are dealing with an SQLite DB.
In a loop, simple 4000 SELECT - COUNT queries are run to calculate certain value of a table row. This is followed by an UPDATE of that calculated value in another table. We notice that:
- In MacOS, it takes 2-3 mins
- In Ubuntu, it takes 5 mins
- In Windows, it takes 3 hours 8 mins!!!
Upon seeing logs, we noticed that every SELECT UPDATE queries together take 1-3 seconds in Windows. Moreover Ubuntu uses a core with 100% CPU for our program, while Windows server utilizes only < 2% only.
This is a very significant difference. All are running the same source code. Is there anything we can do to make the Windows server OS performing the queries on par with Linux & MacOS?
CodePudding user response：
Turns out that the performance was worsening due to a Mutex lock every time before a SELECT UPDATE. This was meant for the thread safety as the DB is expected to be accessed from the multiple threads.
After changing the design where the DB is now accessed from the single thread, the performance improved manifold. In Ubuntu it became 5X faster and in Windows it became 10X faster!!
@prapin's comments also has some merit. We are now executing all the UPDATEs within a single transaction. This speeds up at least the Windows performance by 2X.