SQLite: Is It faster To Use a Variable For Cursor.Rowcount

[Mahares]

Suppose we have a very large table with a large number of records. Is it faster to replace the rowcount with a variable so it does not have to be recalculated every iteration or does it not have an impact on speed improvement?
This:

Dim MyRowCount as Int =Cursor1.Rowcount
For i=0 to MyRowCount -1
Cursor1.position=i
....
Next

Versus this:

For i=0 to Cursor1.Rowcount -1
Cursor1.position=i
....
Next

 

[Erel]

You will not see any difference between the two.

 

[Mahares]

Thank you Erel. I am surprised you do not pick up any speed when you compute the record count one time and store it in a variable. I thought using For i= 0 to Cursor1.Rowcount-1 will recalculate the count at each iteration and row; hence, slow down the process. I am assuming you have tested it for an extremely large table and are sure of the results.

 

[mc73]

I see no reason for not setting a variable outside of the loop, equal to your cursor's size. I think java dynamically checks the bound, even though not sure if I don't check the low level way of doing things. Still, playing around with nonQueries can always have an effect, if we have a doEvents involved. Perhaps you could try deliberately altering db contents, and log changes inside your loop. This can be an interesting check

 

[Mahares]

@Erel: You are absolutely correct. There is no difference in performance to the 1/100th of a second whether to replace Cursor1.RowCount-1 with a variable or use it the way it is in a SELECT query. I tested it with a table that has 20200 records and 40 fields (columns). Both yielded the same duration time. It baffles me. I did not test it in an action query (INSERT). We can never trap you, can we?

 

[eps]

20k rows is not a lot of rows to be honest.

The performance will depend more on the efficiency of your SQL and how many columns it affects and so on and of course how many rows it accesses. Ordering and so on will affect it or computations in the SQL itself.

I'd take a brief look at Explain Plans and so on and then Indexes, if you want to improve the performance.

 

[Informatix]

@Erel: You are absolutely correct. There is no difference in performance to the 1/100th of a second whether to replace Cursor1.RowCount-1 with a variable or use it the way it is in a SELECT query. I tested it with a table that has 20200 records and 40 fields (columns). Both yielded the same duration time. It baffles me. I did not test it in an action query (INSERT). We can never trap you, can we?

Your B4A code is not executed exactly like you wrote it. Changes are made when it is converted to Java, then from Java to bytecode, and finally when the bytecode is executed by the JVM. In this last step, the JIT compiler does some optimizations on-the-fly (for example, all boundary checkings are moved out of loops).

 

[Informatix]

Suppose we have a very large table with a large number of records. Is it faster to replace the rowcount with a variable so it does not have to be recalculated every iteration or does it not have an impact on speed improvement?

If you have a very large table, you should not create a cursor on your records, you should access them individually instead. I can't remember where's the limit but beyond this row count that's a lot faster (and it's still fast below). Look at the Demo_DBWordTable of UltimateListView. My cursor holds only one record each time. If the individual query is too slow because of its complexity, you'll have to create a temp table with a global query result (in memory if you wish) and do individual queries on that table.