Android Question RowCount takes ages

nibbo

Active Member
Licensed User
Longtime User
Anyone know why getting the row count from an sqlite query result set or cursor takes so long?
I have a query that 99% of the time only returns 1 row but I do need to check for zero or greater than one so I tried using resultSet.RowCount, it takes 8 seconds to get the row count value o_O

B4X:
    Dim l1 As Long = DateTime.Now
    Dim iRows As Int = rs.RowCount
    Dim l2 As Long = DateTime.Now
    Log((l2-l1) & " ms")

The above code displays 8325 ms

If I loop though the result set and count the rows manually it takes 0 ms

Why is the built in function so slow? I am a little worried as I use RowCount all over the place, I haven't checked but I guess it takes this long everywhere I use it...

Thanks
Nibbo
 

DonManfred

Expert
Licensed User
Longtime User
What is the exact query for this Resultset?
Can you upload a small project which shows the Issue?
 
Upvote 0

nibbo

Active Member
Licensed User
Longtime User
Hi DonManfred, I think the query is immaterial (correct me if I am wrong...) the time calculation just wraps the setting of iRows as this seemed to be where the code paused when stepping through the code. So it is 8000ms to make an integer variable become equal to the row count unless the query is not executed until you access the result set?
I tried it using a cursor and setting an integer to cursor.RowCount but that took about the same time.
 
Upvote 0

nibbo

Active Member
Licensed User
Longtime User
OK, I take it back the query does matter. If I change the query to a simple one then int = rowcount takes 6ms.
I have no idea how this happens as I put the time calculation around the RowCount bit only and not the query unless... perhaps the code continues to run while the query is being executed and only waits for the query to finish when it tries to access the resultSet?
All sounds unlikely but I cant come up with a better explanation.
 
Upvote 0

nibbo

Active Member
Licensed User
Longtime User
Not exactly. The performance will be identical and is related to the native database engine implementation.

Thanks Erel, but this does raise further questions...

Before we had result sets I used cursors.
After pretty much every query I would do a for/next loop to traverse the results as per the below example.

B4X:
Dim c As Cursor = sql1.ExecQuery("SELECT * From Customers")
For i = 0 to c.RowCount
    c.Position = i
    ... do something with each customer
Next

Are we saying this will iterate once through the results to ascertain the value of c.RowCount before it starts the for/next loop?
Some of my queries will return up to 10,000 results so if the above is correct I will recode them to avoid using RowCount

Thanks
 
Upvote 0

nibbo

Active Member
Licensed User
Longtime User
I never seen a case where it takes Cursor.RowCount 8 seconds; and you have created a very inefficient query.

You are correct Erel, it was some new business functionality and I have now added a new index which makes it run in the blink of an eye.
Still curious to know the most efficient way to process query results though.
I am guessing it is ResultSet with NextRow and to maintain my own row count if I need it afterwards?

Thanks.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
Use ResultSet.NextRow instead.
1. I am confused as to how you get the rowcount with a simple line like this: ResultSet.NextRow. There is Resultset.Rowcount.
2. What about Dim n as int SQL.ExecQuerySingleResult("SELECT count(*)........... Can it be also efficient.
 
Upvote 0
Top