A
A
Alexander Yelagin2016-09-13 17:42:47
Yii
Alexander Yelagin, 2016-09-13 17:42:47

Why does sqlite database query speed slow down?

Hello.
There is a project that works with the sqlite database.
Local computer - i5, RAM - 4giga, ssd-screw.
Server - Intel Xeon E3-1220v2/SSD/RAM 16 Gb
php version, locally 7.0.10, and on the server 7.0.8
The project is the same both locally and on the server. But the speed of the task is almost 2 times different.
The local version of sqlite is 3.8.11 and on the server it is 3.11.1. I don't think that's the issue, but still.
The essence of the task in the system is the search for existing values, if we do not find it, we add a new value. And now, after 10K of recording values ​​in the table, the search and recording of new ones begins to slowly sag (on the server). for 20k (records), the speed of adding and searching in comparison with local launch data differs by 2-3 times.
I read the data in batches via "batch", I also write in batches of 100(batchinsert)
For fast writing, I use the parameters:
PRAGMA synchronous=0
PRAGMA journal_mode=WAL PRAGMA count_changes
=OFF
I use transactions, all read and search operations are performed in one transaction
can be the reason for such a subsidence in the speed of work in the project?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question