File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes iOS and the fly likes sqlite on ipad Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Mobile » iOS
Bookmark "sqlite on ipad" Watch "sqlite on ipad" New topic
Author

sqlite on ipad

Hetal Vora
Greenhorn

Joined: Feb 11, 2009
Posts: 12
We are trying to implement a functionality which requires about 15 Mb of sqlite database (about 115,000) rows to be downloaded and displayed on a screen. We are zipping the database in gzip and downloading the data. But the download takes a lot of time on a 3G network. Also, the queries run a lot slower on the database and it takes a lot of time to fetch even small chunk of data and display on screen.
The solution we are thinking of is splitting the database into multiple chunks.

Any suggestions on how can we improve the performance? Also, any idea on what is the ideal size of database that we can split into to get a fast query performance.

Thanks,
Hetal


Thanks and Regards, Hetal
SCJP 1.5 100%
Alaa Nassef
Ranch Hand

Joined: Jan 28, 2008
Posts: 467
The queries you run for sure have WHERE clauses. Make sure that the columns used in the conditions are indexed. This will hugely boost the performance. As for the large size, why don't you put the database (or a part of it) in the application binary?


Visit my blog: http://jnassef.blogspot.com/
Hetal Vora
Greenhorn

Joined: Feb 11, 2009
Posts: 12
Thanks for the indexing suggestion. The database would be dynamic based on certain conditions. Hence I cannot bundle it in binary.
Would splitting up the database into multiple logical databases help the cause?
Alaa Nassef
Ranch Hand

Joined: Jan 28, 2008
Posts: 467
I don't believe that splitting the data into multiple databases would help query performance a lot. From my experience, correct indexing makes the highest performance boost. However, I was thinking of splitting the data into chunks and giving the user the option to download each chunk separately. This might be OK, but it totally depends on your business. On more thing that could decrease the download size is not to download the sqlite database itself, but rather download the gzipped raw data, then parse it and insert it in the database when download it. Finally, try to vacuum your database, and see if the size decreases.
Alaa Nassef
Ranch Hand

Joined: Jan 28, 2008
Posts: 467
Oh, I forgot to mention this. If you are going to index the database, with this large size, you'd better do that on the device, and not in the sqlite database that's downloaded, since the index will take some extra size. Another thing, take care that each index you add will increase the time needed to insert/update/delete the record a little (not much) but will hugely increase query performance if you are using the column you are indexing in WHERE clauses. So my advice is, index the right columns, and do that on the device after the data is downloaded.
Hetal Vora
Greenhorn

Joined: Feb 11, 2009
Posts: 12
Thanks a lot for the suggestions.
Alaa Nassef
Ranch Hand

Joined: Jan 28, 2008
Posts: 467
You're most welcome. Tell me how it goes.
Pratik Goswami
Ranch Hand

Joined: Mar 02, 2009
Posts: 136

Hi Hetal,

I would like to share what I have done for Improving Database operation!!!

I am downloading bunch of records from server , nearly 30,000. While inserting same records in database it was taking around 2 minutes and 10 seconds on iPod touch (I believe for iPod for testing as when performance is a factor). You won't believe but when I used Begin Transaction and Commit Transaction before database operation (for inserting rows in DB) data inserted in only 4-5 seconds of time.

I was able to save 40% time on whole operation.
Good luck.

Thanks
Pratik Goswami


iOS * Android Developer
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: sqlite on ipad