iOS Question Isql speed problem on Iphone4

[marcick]

I'm testing my app on some devices and facing serious speed problems on a Iphone4.
Look to this code:

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 ?

 

[Erel]

Are you using a local Mac for compilation? There were several updates to iSQL library.

 

[marcick]

Yes, local Mac and Isql is the latest 1.30 version

 

[Erel]

Check the attributes of libiSQL.a on your Mac.

Does it match:
libiSQL, size=401008, modified=02/15/2016 11:11:48

 

[marcick]

Yes, only the time is different, I have 12:55 not 11:11
Size is identical

 

[Erel]

It sounds strange that it is so slow. Are you sure that you measured it correctly?

 

[marcick]

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)

 

[Erel]

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

Yes. That will be good. I have an iPhone 4 here (4s).

 

[marcick]

I've sent you at [email protected]
Pls let me know if you receive it
Thanks

 

[sorex]

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

 

[marcick]

Thanks Sorex, but that's not the problem. I'm testing it in release on a real Iphone4

 

[sorex]

and how is the speed when you use the right counting method?

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

or

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

 

[marcick]

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

 

[sorex]

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

this doesn't make sense indeed.

 

[Erel]

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.

  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.

 

[marcick]

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.

 

[Erel]

Do I have to declare just once when the db is created, right ?

Yes.