Best practices for large databases

Problems with WiFi, SQLite ,Bluetooth, WiMax, Proxies, etc...

Best practices for large databases

Postby radioblaster » Fri Jul 17, 2009 10:30 pm

What is the best practice for developing android applications that rely on large databases - should the database be installed to the sd card, or should it be put online somewhere and queried remotely by the application??

With accessing everything remotely my concern is the amount of data that could be transfered (user has to monitor closely to not exceed data plan, etc), and, the user may be outside of cellular range but may still want to use the application.

Having the database locally seems ideal because it consumes less bandwidth, will work outside of cell range, and probably has better response time, but because the database may not be static, the user will need some easy method of downloading and installing updates to the database. Plus, are users typically fine with an app having to install it's data to the sd card? Some users may swap out sd cards for different purposes...

Any suggestions?

Thanks!!
radioblaster
Junior Developer
Junior Developer
 
Posts: 12
Joined: Thu Jul 16, 2009 4:29 pm

Top

Postby padde » Fri Jul 17, 2009 11:58 pm

Well.. its a bit hard to answer because i have not enough facts like.. how big you expect the DB will be? and how often you expect updates? But a big non static DB should be placed online. But this realy depends on db size update rate and what amount of traffic emerge.
Maybe a mix of a small local DB on the device and a bigger one online is good solution.
If you could share some more details about your planed app we could give you more help than this :D
padde
Master Developer
Master Developer
 
Posts: 443
Joined: Wed Apr 08, 2009 4:52 pm

Postby radioblaster » Sat Jul 18, 2009 12:16 am

The idea of having a remote database but a fragment of that larger db (just the light-weight data) on the device crossed my mind as well, so that is indeed a third option. Basically the idea for my application is for users to be able to locate trails (like hiking or cycling trails) nearby their current location. So, essentially the user would click a 'locate trails' button, at which point their current gps info would be used to query the database to find trails within X km. The trail heads would show up on a MapView, at which point the user could select a trail marker for more info (like trail length and difficulty). Once they find the one they want to travel, they can load the actual trail as an overlay in a MapView.

Ultimately, I like the idea of having an "online mode" and an "offline mode". In offline mode the user would just be presented a list of nearby trails with their pertinent info, rather than a MapView (so no network access is required).
As they traverse the path or trail dynamic info such as distance travelled, current speed, max speed, etc would be displayed.

In online mode, the user would have access to the MapView in addition to the dynamic info like max speed, etc.


Right now, for testing purposes the database is only about 1 MB, but with thousands of trails I expect it could reach 100 MB or more.

As for update frequency, it's hard to say as I'm doing this as sort of a proof of concept right now. I would imagine in a real world scenario people would add trails fairy regularly.
radioblaster
Junior Developer
Junior Developer
 
Posts: 12
Joined: Thu Jul 16, 2009 4:29 pm

Postby radioblaster » Sat Jul 18, 2009 1:59 am

After making a list of the pros and cons of each implementation method, I'm leaning toward the 'all data stored locally on an SD card' method.This option supports my offline/online mode idea (basically enabling or disabling MapViews), the app will be responsive due to all local access, overall cellular network usage will be low (only needed for MapView), application complexity will be low since all queries will be to a local SQLite db, and updating the database with newer versions can be done at the user's convenience (like when the user is connected to a WiFi network). The only real negative is the amount of local space consumed, requiring the user to have an SD card.

The 'all data online' option seems to be the worse for this application because it does not support the idea of an offline mode, response time could be slow due to network lag, and overall cellular network usage will be high. The benefit is that local space usage on the device is low.

The 'store lightweight fragment of data on the device, full data remotely' method seems to be the middleman, and is still a plausible solution.

That being said, this is my first mobile application so my 'conclusions' are based on what I think, not from actual experience. For all I know, my 'cons' of the 'all data online' method may be inconsequential in real-world usage and this may in fact be the best solution...

Still looking for ideas or suggestions from those who have much more experience with this kind of application than myself.

Thanks!!
radioblaster
Junior Developer
Junior Developer
 
Posts: 12
Joined: Thu Jul 16, 2009 4:29 pm

Postby padde » Sat Jul 18, 2009 3:06 am

For this kind of app and due to the offline mode a local DB is the only solution.. compareable apps like navigation software for example do it the same way for pretty same reasons.
If you offer the users some sort of sync function to share their trails and to load new ones from the online DB should be enough to keep the app alive over a long time.
I wish you good luck for your app :)
padde
Master Developer
Master Developer
 
Posts: 443
Joined: Wed Apr 08, 2009 4:52 pm

Postby radioblaster » Sat Jul 18, 2009 3:28 am

Thanks for the advice padde, I greatly appreciate it!!
Just one more thing (I hope!), does this mean I would create and populate the SQLite database on my desktop and then just publish the db file online somewhere. Then, when the app is run for the first time on the phone, it will download the db file to the sd card. My understanding is that it is best not to package a large database file with the application apk file. Is this correct?

Thanks again!
radioblaster
Junior Developer
Junior Developer
 
Posts: 12
Joined: Thu Jul 16, 2009 4:29 pm

Top

Postby arams » Sat Jul 18, 2009 6:01 am

Thanks radioblaster and padde....


Storing a db file in sdcard is a good one?

http://stackoverflow.com/questions/1129 ... id-sd-card
arams
Experienced Developer
Experienced Developer
 
Posts: 89
Joined: Fri May 08, 2009 7:45 am

Postby padde » Sat Jul 18, 2009 8:11 am

I would populate the DB on desktop and build a small website for that app
where user can download always the newest version of the DB. So later when
a huge amount of custom trails are available they can download from home and
dont have to update the old apk included DB with long download sessions from their device.

Their could be much more functionality on that website.. some sort of support forum
or a forum for user experience exchange... or maybe they can choose a PLZ/ZIP CODE
online and the website generates a DB file with the according data. This way the users
have full control about the content stored on their device.

Thats the way i would do it... a small DB in the apk to directly test the app i think would be fine
but not the real big one. If the user likes the app he will go to that website and download the big
one or a custom build one for him... just depends on your plans :)
padde
Master Developer
Master Developer
 
Posts: 443
Joined: Wed Apr 08, 2009 4:52 pm

Postby radioblaster » Sat Jul 18, 2009 4:10 pm

All great suggestions padde! However, in addition to a web site for customizing and downloading the db file, I would still like functionality through the application just for ease of use for those who don't want to bother with the hassle of loading a browser, navigating to the site, etc. Do you think it would be feasible (and reasonable) to have a "Download current data" button which when selected would do something like:

Code: Select all
onClick
{
    If not connected to WiFi then
    {
        warning message: "Due to the large database size, we strongly recommend you connect to a Wireless network before proceeding. Would you like to proceed anyway?" YES/NO
        If NO then
        {
            message "Action cancelled by user"
            return
        }
    }
    establish connection with server
    download newest db file (show progress bar)
    overwrite current db file on sd card
    message "Update complete"
}


Better yet, for speed and to conserve bandwidth, it would be nice if the update actually just downloaded new data into the existing database - not sure how difficult of a task that is though (I don't think SQLite even supports replication natively...)
radioblaster
Junior Developer
Junior Developer
 
Posts: 12
Joined: Thu Jul 16, 2009 4:29 pm

Postby FreeGeezer » Fri Jan 22, 2010 2:32 am

My problem is very similar to the one being described here. I need some advice as well as guidance to a tutorial that would help me accomplish the task.

I have a large mySQL database, on a Linux server, it contains 112,000 records consuming about 55Mb. This database is updated weekly with probably 65% to 75% of the records being affected. The content of this data are points of interest and they are geocoded.

When the user begins an inquiry, we first determine their location as a base. The user then selects some search parameters, on of which is the radius of results they desire. The remote server is then queried for records with in x distance from their current location, that meet the users selection parameters. Those records would be downloaded to an internal SQLite database, created solely for the purpose of the current session.

They would then be displayed on a map where the user could select a map point marker for more information or choose to get driving direction -- hopefully turn-by-turn voice directions.

Thank you for any help you can offer.
FreeGeezer
Once Poster
Once Poster
 
Posts: 1
Joined: Fri Jan 22, 2010 2:06 am
Location: Central Oregon

Postby padde » Fri Jan 22, 2010 3:17 pm

This sound exactly like what i did for my bachelor thesis. I used a remote mysql db and accessed it via webservices
(one webservice would be enough but this was part of the thesis). One webservice was a servlet with different methods of de/serialisation and the other was implemented as soap webservice. In the app i synced a local sqlite db to cache the nearest points of interest surrounding the user in a defined radius.

Iam sure i can help you with your project.. and maybe share some code.


Greets
Padde
padde
Master Developer
Master Developer
 
Posts: 443
Joined: Wed Apr 08, 2009 4:52 pm

Postby achie1266 » Thu Jan 28, 2010 7:29 pm

I have a very similar requirement in my app.

My application needs to download a large database(~10,000 records) and right now its taking around 10 minutes to load the data from my company's website, parse the xml and store it in the database.

The final database size would be around 2-3MB.

But this seems to be not so user friendly since the user needs to wait for 10-15 mins when they use the app for the first time.
And then it is also required that they have a good internet connection. I did not implement the functionality yet for the cases where internet will be disconnected in the middle of the process.

So I am considering other options, to do it more quickly.
The options that I have right now it seems are.
1. package the whole database with the app[may be in res/assets folder] and copy those db files when the user opens the app for the first time.
cons: this will duplicate the data as it will be present in two places.
? is there anyway we can delete the original file after database is copied?
2. instead of using xml from web and then parsing it, I would like to download just the db files[not xml but the sqlite files directly] and may be compress them to decrease the download time and then copy them into database. is it possible to do so?

I would like to know any advices that you may have with this one.
Thank you.
achie
User avatar
achie1266
Master Developer
Master Developer
 
Posts: 223
Joined: Mon Nov 09, 2009 10:56 pm
Location: Denver

Postby Emmanuel7 » Mon Feb 01, 2010 11:48 pm

I made the first choice a long time ago in my game, and it was a mistake.
The size of an application IS important for user, so if it is more than 5 Mo, they might don't want to use it, give bad ratings, etc...

So I would advice you to go with the option (2) : download directly a binary of your database.
To generate the file, the method I used was to make the generation on my phone, and then get the file from the phone with DDMS ( or adb if you prefer the command line ).

Hope it helps,
Emmanuel7
Senior Developer
Senior Developer
 
Posts: 164
Joined: Fri Dec 12, 2008 2:17 am
Location: Paris

Top

Return to Networking & Database Problems

Who is online

Users browsing this forum: Exabot [Bot] and 6 guests