iOS Question Isql speed problem on Iphone4

marcick

Well-Known Member
Licensed User
Longtime User
I'm testing my app on some devices and facing serious speed problems on a Iphone4.
Look to this code:

B4X:
Dim rs As ResultSet=SQL_db1.ExecQuery("SELECT * FROM records tmp GROUP BY ID  ORDER BY GDate DESC")
    Dim nn As Int=0
    Do While rs.NextRow
        nn=nn+1
    Loop

Running in release mode on a Iphone6S this requires just 20mS. No problem.
Running in release on a Iphone4 this requires about 10 seconds ...

Consider that ExecQuery is executed in less than 1mS and the ResultSet contains 10 records.
What takes a so long time is the Do-While loop. Ten iterations, 1 second each ....

Is an Iphone 6S 10.000/20=500 times faster than a Iphone4 ?
 

marcick

Well-Known Member
Licensed User
Longtime User
Yep ..
I have logs with datetime, but TEN seconds you can count with your finger !
The Iphone4 is fresh restored (nothing else installed) has 13GB free and IOS 7.1.2
The app runs normal in all its parts, except where I have a "Do until rs.nextrow" and is terribly slow.
Sounds strange to me too but ...
The app works fine on Iphone6 and simulator

Could be of help if I put togheter a sample project ? (but you need an Iphone4 to test it)
 
Last edited:
Upvote 0

sorex

Expert
Licensed User
Longtime User
@marcick : try to clean the project and start it again. after a while the debugger slows down certain routines while others remain fast.
 
Upvote 0

marcick

Well-Known Member
Licensed User
Longtime User
Thanks Sorex, but that's not the problem. I'm testing it in release on a real Iphone4
 
Upvote 0

sorex

Expert
Licensed User
Longtime User
and how is the speed when you use the right counting method?

B4X:
Dim rs As ResultSet=SQL_db1.ExecQuery("SELECT count(*) FROM records tmp GROUP BY ID")

or

B4X:
Dim rs As ResultSet=SQL_db1.ExecQuery("SELECT count(distinct id) FROM records")
 
Upvote 0

marcick

Well-Known Member
Licensed User
Longtime User
The ExecQuery is executed in less than 1mS and the ResultSet contains just 10 records.
What takes a so long time is the Do-While loop. Ten iterations, 1 second each ....
This happens only on my Iphone4. No problems on Iphone6S
 
Upvote 0

sorex

Expert
Licensed User
Longtime User
ok, I thougth the amount of records would've been a lot more.

this doesn't make sense indeed.
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
It takes 1.9 seconds on my iPhone 4s.

The problem is not in the do while loop. It takes time for the SQL engine to return the next result each time.
There are 100k records in the database and it needs to do a lot of work.

B4X:
  SQL1.ExecNonQuery("CREATE INDEX id1_index ON records (V_ID)")
   SQL1.ExecNonQuery("CREATE INDEX id2_index ON records (GpsDate)")
By adding these two indices it improves to 1.08 seconds.
 
Upvote 0

marcick

Well-Known Member
Licensed User
Longtime User
I was expecting that after the ExecuteQuery the most of the job has been done and I have a small resultset containing just ten records.
So why it requires a so long time to scan 10 records ?
But that's because I don't know how it deeply works.
With those two indices above the time is now down to an acceptable 1.7 seconds.
Do I have to declare just once when the db is created, right ?
Anyway it's incredible the difference between an Iphone 6 and an Iphone 4 ... 500 times faster
I think I will anyway limit the distribution with #MinVersion: 8, hopeing to have good peformances starting from Iphone5.
 
Upvote 0
Top