Android Question What is the fastest method (SQL question)

[DPaul]

Hi,

What would be the most recommended way to go, if fast response times are a concern ?

There is an SQL database with +/- 50 fields.
The max number of records will rarely exceed 500.
But I need to question it in a variety of ways.

Two possibilities:
a) For each question, i need a different combination of fields to be selected and some "WHERE " clause.
Advantage : the "sel as cursor" will contain something closer to what i need, disadvantage, i need many different SQL statements.

b) i simply do "SELECT * from..." no where clause, and i filter out what i need in code.
Advantage: obviously only one call to the database, disadvantage: some more code to write.

I can assess the coding consequences of a) and b), except that i have no idea of the resource impact on the tablet.
Calling an SQL database multiple times might be more punishing than writing a For Next loop to go through all the records every time.

Any thoughts ?

thx,
Paul

 

[Erel]

Any thoughts ?

This is a very small database. I think that you should start with the solution that is easiest for you to develop and maintain. It will most probably be fast enough.

 

[BillMeyer]

Here's my 2 cents.

I have a MySQL Database which I access via jRDC2. I have a table with 106 records in it with 18 fields. To fetch all the records it takes the server 0.3 seconds for the transaction (from pressing the request button on my device to getting the desired result in the logs output) - speed is not your problem in my opinion - but your design is what is going to have an influence on your end product.

I would create my database and then write a whole lot of procedures with parameters etc and then just call these - this will give me the variable element that I require.

You could, as you suggest, fetch all records and then filter out all at local level, but I think that is an unnecessary amount of data transfer. YOur server will usually be the faster, bigger machine, so have it do the work and transfer only what you need.

On modern devices (tablets) I don't think that you would tax the device with a 500 record strong database as @Erel says and even less so if you have the server doing all the work.

Enjoy !!

 

[DPaul]

OK, maintenance of the code is a big concern indeed.
If performance is less of an issue, i'll go for what seems the simplest.
"KISS".
Paul