Gmail for Mobile HTML5 Series: Suggestions for Better Performance

On April 7th, Google launched a new version of Gmail for mobile for iPhone and Android-powered devices. We shared the behind-the-scenes story through this blog and decided to share more of our learnings in a brief series of follow-up blog posts. This week, I'll talk about a few small things you can do to improve performance of your HTML5-based applications. Our focus here will be on performance bottlenecks related to the database and AppCache.

Optimizing Database Performance

There are hundreds of books written about optimizing SQL and database performance, so I won't bother to get into these details, but instead focus on things which are of particular interest for mobile HTML5 apps.

Problem: Creating and deleting tables is slow! It can take upwards of 200 ms to create or delete a table. This means a simple database schema with 10 tables can easily take 2-4 seconds (or more!) just to delete and recreate the tables. Since this often needs to be done at startup time, this really hurts your launch time.

Solution: Smart versioning and backwards compatible schema changes (whenever possible). A simple way of doing this is to have a VERSION table with a single row that includes the version number (e.g., 1.0). For backwards-compatible version changes, just update the number after the decimal (e.g., 1.1) and apply any updates to the schema. For changes that aren't backwards compatible, update the number before the decimal (e.g., 2.0) at which point you can drop all the tables and recreate them all. With a reasonable schema design to begin with, it should be very rare that a schema change is not backwards compatible and even if this happens every month or so, users should get to use your application 20, 30 even 100 times before they hit this startup delay again. If your schema changes very infrequently, a simple 1, 2, 3 versioning scheme will probably work fine; just make sure to only recreate the database when the version changes!

Problem: Queries are slow! Queries are faster than creates and updates, but they can still take 100ms-150ms to execute. It's not uncommon for traditional applications to execute dozens or even hundreds of queries at startup – on mobile this is not an option.

Solution: Defer and/or combine queries. Any queries that can be deferred from startup (or at any other significant point in the application) should be deferred until the data is absolutely needed. Adding 2-3 more queries on a user-driven operation can turn an action from appearing instantaneous to feeling unresponsive. Any queries that are performed at startup should be optimized to require as few hits to the database as possible. For example, if you're storing data about books and magazines, you could use the following two queries to get all the authors along with the number of books and magazine articles they've writen:

SELECT Author, COUNT(*) as NumArticles
FROM Magazines
GROUP BY Author
ORDER BY NumArticles;

SELECT Author, COUNT(*) as NumBooks
FROM Books
GROUP BY Author
ORDER BY NumBooks;

This will work fine, but the additional query will generally cost you about 100-200 ms over a different (albeit less pretty) query like:

SELECT Author, NumPublications, PubType
FROM (
  SELECT Author, COUNT(*) as NumPublications, 'Magazine' as PubType, 0 as SortIndex
  FROM Magazines
  GROUP BY Author
  UNION
  SELECT Author, COUNT(*) as NumPublications, 'Book' as PubType, 1 as SortIndex
  FROM Books
  GROUP BY Author
)
ORDER BY SortIndex, NumPublications;

This will return all the entries we want, with the magazine entries first in increasing order of number of articles, followed by the book entries, in increasing order of the number of books. This is a toy example and there are clearly other ways of improving this, such as merging the Magazines and Books tables, but this type of scenario shows up all the time. There's always a trade-off between simplicity and speed when dealing with databases, but in the case of HTML5 on mobile, this trade-off is even more important.

Problem: Multiple updates is slow!

Solution: Use Triggers whenever possible. When the result of a database update requires updating other rows in the database, try to do it via SQL triggers. For example, let's say you have a table called Books listing all the books you own and another called Authors storing the names of all the authors of books you own. If you give a book away, you'll want to remove it from the Books table. However, if this was the only book you owned by that author, you would also want to remove the author from the Authors table. This can be done with two UPDATE statements, but a "better" way is to write a trigger that automatically deletes the author from the Authors table when the last book by this author is removed. This will execute faster and because triggers happen asynchronously in the background, it will have less of an impact on the UI than executing two statements. Here's an example of a simple trigger for this case:

CREATE TRIGGER IF NOT EXISTS RemoveAuthor
AFTER DELETE ON Books
BEGIN
  DELETE FROM Authors
  WHERE Author NOT IN
    (SELECT Author
     FROM Books);
END;

We'll get into more detail on triggers and how to use them in another performance post to come.

Optimizing AppCache Performance

Problem: Logging in is slow!

Solution: Avoid redirects to the login page. App-Cache is great because it can launch the application without needing to hit the network, which makes it much faster and allows you to launch offline. One problem you might encounter though, is that the application will launch and then you'll need to hit the network to get some data for the current user. At this point you'll have to check that the user is authenticated and it might turn out that they're not (e.g., their cookies might have expired or have been deleted). One option is to redirect the user to a login page somewhere, allow him to authenticate and then redirect him back to the application. Regardless of whether or not the login page is listed in the manifest, when it redirects back to your application, the entire application will reload. A nicer approach is for the application itself to display an authentication interface which sends the credentials and does the authentication seamlessly in the background. This will avoid any additional reloads of the application and makes everything feel faster and better integrated.

Problem: AppCache reloading causes my app to be slow!

Solution: List as few URLs in the manifest as possible. In a series of posts on code.google.com, we talked about the HTML5 AppCache manifest file. An important aspect of the manifest file is that when the version gets updated, all the URLs listed in the file are fetched again. This happens in the background while the user is using the application, but opening all these network connections and transferring all that data can cause the application to slow down considerably during this process. Try to setup your application so that all the resources can be fetched from as few URLs as possible to speed up the manifest download and minimize this effect. Of course you could also just never update your manifest version, but what's the point of having rapid development if you never make any changes?


That's a brief intro to some performance considerations when developing HTML5 applications. These are all issues that we ran into ourselves and have either fixed or are in the process of fixing in our application. I hope this helps you to avoid some of the issues we ran into and makes your application blazing fast!

We plan to write several more performance related posts in the future, but for now stay tuned for next post where we'll discuss the cache pattern for building offline capable web applications.

By Derek Phillips, Software Engineer, Google Mobile

Previous posts from Gmail for Mobile HTML5 Series
HTML5 and Webkit pave the way for mobile web applications
Using AppCache to launch offline - Part 1
Using AppCache to launch offline - Part 2
Using AppCache to launch offline - Part 3
A Common API for Web Storage