Sqlite randomly slowing down on simple (but big) table on iOS

Sqlite randomly slowing down on simple (but big) table on iOS

I'm working on an enterprise sales app, for iPad, that uses Sqlite as its
internal database, and a strange behaviour recently showed up.
I have a huge table that is filled with information from several other
tables (sort of like a "materialized view"), which can contain over 2
million rows, depending on how the user is set up. When the user wants to
search for an item, the app performs a query on this huge table that has
an indexed column and on other columns that are used as filters and/or
metadata. I'll post the query and the basic idea below. Anyway, this query
usually returns in 2~3 seconds on an iPad 4th gen, no more than that, and
this is just fine. This table is dropped, re-created and filled every time
the user taps a button to synchronize his data with our server.
However, recently the same query in the same table (with no relevant
changes at all), randomly started to take 40~50 seconds. If you do the
same thing later, on the same device, with the same filters (or even
changing the filters!), the same query on the same table takes the 2~3
seconds again. I haven't found any specific situation that causes this
slowdown, the app is the only one running at that time. The device is not
the problem, we've seen this happen on at least 5 different iPads, one is
an iPad 3 and the others are iPads 4th gen.
I don't think it is some sort of caching, since the app does not cache
anything, and these times are rather random. Sometimes they take 40
seconds for 10 times in a row, then suddenly it starts to take only 2
seconds again, and the same thing the other way. The only thing that is
clear to me is that this slowdown only occurs after intensive use (1 - 2
days of work using the app), so I'm also having troubles to cause this
behaviour while debugging on the iPad I have with me.
What I've tried:
Attach Instruments to the process and check what resources are being used
during the slowdown. The app does INTENSIVE use of the iPad's 'disk'
(flash memory) during the whole time. I don't have the example to analyse
it again now, but I think the CPU usage was around 30%. The RAM usage is
stable at 90~100MB, which is normal for our app.
Run VACCUM on the db; - reduced ~50MB on a database I had as example.
Run ANALYZE on the db; - didn't see any improvements
Run REINDEX on the db; - seems to be helping a little, but it's not
solving the problem.
Kill the process and start over - nothing changes
The huge table is constructed as the following, and does NOT have any
foreign keys or other any other constraint:
CREATE TABLE FMV_CATALOG(
UNIQUE_ID TEXT,
PRODUCT_ID INTEGER,
<bunch of metadata/filtered columns - total of 20 columns>
);
And the query that is made to find the products is:
SELECT
PRODUCT_ID
,UNIQUE_ID
<all other required columns, ~20 columns>
FROM
FMV_CATALOG
WHERE
UNIQUE_ID = '<some id>_<other id>'
AND PRODUCT_NAME LIKE '%iPhone%'
<and other optional, rarely used, filters.>
I'm totally out of ideas, so any help will be appreciated.
Thanks!