Android Question RowCount takes ages

[nibbo]

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

    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]

What is the exact query for this Resultset?
Can you upload a small project which shows the Issue?

 

[nibbo]

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.

 

[nibbo]

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.

 

[Erel]

You should never use RowCount with ResultSet.
1. You are forcing the database engine to go over the results and count them.
2. It is not part of ResultSet API. It is there because B4A ResultSet extends Android Cursor.

Use ResultSet.NextRow instead.

 

[nibbo]

You should never use RowCount with ResultSet.
Use ResultSet.NextRow instead.

Interesting, are we saying if I need the RowCount then I should go back to using Cursors's?

 

[Erel]

Not exactly. The performance will be identical and is related to the native database engine implementation.

You shouldn't use RowCount at all. It was needed with Cursor. It is not needed when you use ResultSet.

 

[nibbo]

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.

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

 

[Erel]

I never seen a case where it takes Cursor.RowCount 8 seconds. I guess that what actually happened is that the query was not executed until you called RowCount, and you have created a very inefficient query.

RowCount will not be the real bottleneck. The problem is in the query itself.

 

[nibbo]

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.

 

[Erel]

Performance will be the same. I never use Cursor or RowCount as ResultSet is simpler and cross platform.

 

[Mahares]

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.

 

[Erel]

The correct answer is in post #9.
Ignore everything else.