Slow indexing

[AndiLeni]

I noticed, that indexing content from my CMS is extremely slow.
A regular 800 - 1000 word page takes about 3-5 seconds to index, which results in very long indexing times for a regular website with a few dozen pages.
Is there anything I can do to speed this up or find the bottlenecks?

Edit:
The bottleneck seems to be in StateSetIndex.php inside the function index().
Calling $this->stateSet->add($newState, $state, $mappedChar); takes about 0.03 seconds, which accumulates to a large delay.

[Toflar]

200 documents per second is actually pretty fast 😊 There's not much to optimize here anymore, no.
So your speed is very much expected and normal. If you need faster indexing times, Loupe is not the right choice for you (it's documented in the README).

[AndiLeni]

@Toflar I ran some test here on my machine. The slow speed is indeed related directly to the size of the indexed document.
When I try to index a simple website article with 400 words, 2100 sql statements are executed in roughly 4 seconds.
It is not surprising that my indexing is slow. Executing multiple thousands of sql statements is really time consuming.

Maybe it is possible for you to make some adjustments here to reduce the amount of requireed sql updates? Or maybe combine them instead of executing them on their own?

[fiedsch]

Or maybe combine them instead of executing them on their own?

Sounds promising. Accumulating the data data to be inserted and then executing a statement like

INSERT INTO table (column1,column2 ,..)
VALUES 
   (value1,value2 ,...),
   (value1,value2 ,...),
    ...
   (value1,value2 ,...);

might help to reduce the number of queries and speed up things a bit.

[fiedsch]

An alternative might be to use INSERT together with ON CONFLICT (https://www.sqlite.org/lang_conflict.html) to reduce the overhead of testing wether to insert or update the data in Engine::upsert(). Disclaimer: I‘m afraid I don not understand the data structures enough to see if this would make sense :-(

[Toflar]

It's not like I haven't tried all of your proposals already - even before I've open sourced Loupe 😉 upsert() is used because the resulting ID is required for relations in those places. So using bulk inserts is not an option there. And the ON CONFLICT is something I've tried but failed - it's even mentioned in the source code so if anybody is successful - PRs welcome. I'm sure there is potental to group stuff, take adavantage of built-in SQLite features, foreign keys or what not but it may require significant time investments here.

But then again: 2100 SQL statements for 400 words is not surprising at all. The data is split and optimized for searching, that's why indexing is slow and it's not a bad thing - it's supposed to be slow. Indexing will always be slow because that's the nature of how indexing works. Not saying we cannot optimize it but you have to free yourselves from thinking that we can combine this into one simple query. That's just not how things work.

Also, we have a performance test in the repo: If I run bin/index_performance_test.php, it finishes in 230s and in doing so, it indexes 32k documents, 152k terms, 2.7 million relation rows and more! So that's a hell lot of data SQLite processes within 230s. And not to mention the whole state set calculation and language detection which takes up a significant share of those 230s for sure.

So 400 words taking 4 seconds sounds a bit strange - maybe you can create a reproducer for us - should look about the same as the bin/index_performance_test.php with your data and then we can measure if it takes 4 seconds on our systems as well?

[AndiLeni]

@Toflar I was able to identify the issue. The slow indexing time was caused by the xdebug php extension. Even though it was not actively profiling the indexing process, it did slow down the process drastically. After disabling the extension in the php.ini file, the indexing was way faster (about 1 second for one website page).