为什么SQLite查询在Windows [服务器]机器中与Ubuntu＆amp; macos？
我们有3台机器：一个机器安装了具有不错的规格的Windows Server OS 2012-R2（12 GB RAM，3.6 GHz，4个内核，600 GB硬盘）。其他的是家用笔记本电脑，其常规规格为Ubuntu 20.04＆amp; macos。所有人都在处理SQLite DB。
在循环中，简单4000 选择 - 计数查询以计算表行的某些值。接下来是该表中该计算值的更新。我们注意到：
- 在Macos中，在Ubuntu需要2-3分钟
- ，在窗户上需要5分钟
- ，需要3小时8分钟！！！
看到日志后，我们注意到每个select + Update查询一起在Windows中需要1-3秒。此外，ubuntu使用核心为我们的程序使用100％CPU，而Windows Server仅使用＆lt;仅2％。
这是一个非常显着的差异。所有人都运行相同的源代码。我们可以做些什么来使 Windows Server OS 与Linux＆amp;一起执行查询。 macos？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
事实证明，每次
选择 +更新之前，由于A Mutex锁定的性能正在恶化。这是针对线程安全的，因为预计DB将从多个线程访问。更改现在从单线访问DB的设计后，性能改善了歧管。在Ubuntu，它的速度快了5倍，在窗户中，它的速度快10倍！！
@prapins>@prapins评论也有一些优点。现在，我们正在单个事务中执行所有
更新。这至少加快了Windows性能的速度2倍。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.