B4J Question Performance problem with SQLite

[Alessandro71]

while investigating on slow response time, I tracked down the issue to this code section

Dim entries As List
entries.Initialize

WriteLog("query start")
Dim rs As ResultSet = DB.ExecQuery2("SELECT DISTINCT carvaluename FROM carvalues WHERE timestamp>=? AND timestamp<=? ORDER BY carvaluename", Array As String(starttimestamp, endtimestamp))
WriteLog("query end")
Do While rs.NextRow
    WriteLog("add record")
    entries.Add(rs.GetString("carvaluename"))
Loop
rs.Close

the first 2 log messagest ("query start" and "query end") are printed in less than a second, while the "add record" lines come rather slowly, about 1 sec apart one another
the whole While loop ends in about 20 seconds, with no noticeable difference between Debug and Release mode

the query returns 21 records from a table (actually a view) with 300000 lines
the same query, when performed in "DB Browser for SQLite" takes about 900ms

from the elapsed time, it looks like every NextRecord call just performs the whole query again

i already run ANALYZE and PRAGMA optimize on the DB with no effect

am i missing something?

 

[Erel]

The slowdown comes from the SQLite driver itself.

 

[Mashiane]

If I understand the OP problem correctly, the issue is not the query or the structure of the table. The query executes fine and is fast.

The issue is the slowness of the code from line 7 to 10 on post 1, the loop speed which perhaps could be sped up if possible.

If the speed of the loop depends on the query being executed then that is something else and interesting.

 

[Mashiane]

The slowdown comes from the SQLite driver itself.

Just saw this after I posted. Interesting... Any advise or solution?

 

[Erel]

The slowdown comes from the handling of the query by the JDBC driver. It has nothing to do with any other code.

 

[Alessandro71]

for the sake of completeness i tried removing the DISTINCT and the ORDER BY, in different tests, with no effect at all

 

[OliverA]

Try

Dim entries As List
entries.Initialize

WriteLog("query start")
Dim rs As ResultSet = DB.ExecQuery2("SELECT DISTINCT carvaluename FROM carvalues WHERE timestamp>=? AND timestamp<=? ORDER BY carvaluename", Array As String(starttimestamp, endtimestamp))
WriteLog("query end")
Do While rs.NextRow
    WriteLog("add record")
    'entries.Add(rs.GetString("carvaluename"))
Loop
rs.Close

Reason: supposedly GetString(String) is expensive.
If this makes a difference, try GetString(Int), where Int the the column position of the resultset that you are trying to retrieve.
Source: https://stackoverflow.com/questions/8623551/sqlite-queries-extremely-slow-in-java
Note: I've not seen this issue in jRDC and SQLite, but jRDC does not use string names for column access of query results

 

[Alessandro71]

Already tried, with no benefit.
It's the NextRow call, as @Erel already pointed out

 

[Alessandro71]

as a followup, I solved this by rewriting the query as follows

SELECT carvaluename from (SELECT DISTINCT carvalueid as id FROM carvaluevalues WHERE timestamp>=? AND timestamp<=?) JOIN carvaluenames on id=carvaluenames.carvalueid ORDER by carvaluename

same result, but lightning fast now