View-centric performance optimization for database-backed web applications

View-centric performance optimization for database-backed web applications Yang et al., ICSE 2019

The problem set-up in this paper discusses the importance of keeping web page load times low as a fundamental contributor to user satisfaction (See e.g. ‘Why performance matters’). Between client-side tools such as Google’s Lighthouse, back-end tools that can analyse ORM usage and database queries and point out issues such as N+1 selects, and the information provided by your favourite APM I was initially wondering what ground there was left to tread here. So I was pleasantly surprised when it turned out the authors were looking at the problem in a different way to most of these approaches.

Rather than accepting the current rendered view (web page) as seen by the end-user as fixed, and then asking what can be done to optimise the end-to-end loading time of that page, this paper examines the question of what changes to the current view could dramatically reduce its load time? I.e., small (or sometimes not so small) changes to what the end user ultimately sees on the page, that can have a net benefit on the overall user experience.

Empirical studies have found that about a quarter of real-world web application performance problems are solved by developers through view changes, like pagination and view content removal. These changes often bring much more performance improvement than the view preserving ones (8.79x vs 2.16x on average) but involve more changes…

Panorama helps developers understand the cost contribution of the different elements in a rendered view, suggests view changes to improve page load times, and can even undertake automated refactoring to help implement them. The evaluation shows that changes suggested and generated by Panorama sped-up page load times by 4.5x on average, and up to 38x. Moreover, users like the resulting experience at least as much if not more than the original page designs.

A good reminder that the highest leverage performance optimisations are often to be found in the design space, as opposed to the implementation space.

Introducing Panorama

The current embodiment of Panorama works with Rails applications and integrates with the RubyMine IDE. It has three main elements: an interface that visually highlights the data processing costs of each web page element in the rendered view and allows the developer to explore view-change refactorings; an estimator that provides the data feeding this view; and an optimiser that generates patches implementing view-change refactorings.

A web page instrumented by Panorama looks like this:

Parts of the page are colour-coded heat-map style to indicate how costly they were to generate. Right-clicking on an HTML tag in the browser opens up a context menu with a list of suggested refactorings for that element. If a suggestion is chosen, Panorama communicates with a RubyMine editor (the app is assumed to be running on localhost during development, e.g. rails s) to display a suggested code patch for review. If the source code update is accepted, the developer can immediately see the new resulting page in the browser, together with updated heat-map information.

Cost estimation

While a web page’s load time consists of client side rendering time, network communication between client and server, computation time on the server, and database query cost, Panorama’s estimator currently focuses on database query cost, as query time often contributes a significant portion of the page load time.

(In my experience, server-side view rendering time can also be surprisingly significant in web applications).

Panorama uses static analysis to generate an Action Dependency Graph (ADG) which connects code in views (and helpers?) and controller actions to the underlying ActiveRecord calls they generate. The application is also instrumented to ensure every HTML tag has an unique id if it didn’t originally.

Given the graph Panorama works backwards from each tag to find the queries that contribute to it, and forward from each query to find the tags that consume information provided by it.

To associate costs with the queries Panorama can use dynamic profiling if a suitable test suite is available. Alternatively, it can use static analysis to estimate processing costs by considering how many times a query might be issued, whether its execution time might increase as the number of rows in the table(s) involved increase, and an estimate of the time taken to execute one query instance.

The cost of an HTML tag is then set as the cost of the most expensive query that contributes to it.

View refactoring

Panorama supports four different types of view refactoring:

  • Introduce pagination
  • Switch from synchronous to asynchronous loading
  • Reduce the accuracy of displayed results (approximation)
  • Remove content from the page altogether

Pagination is a suggested refactoring whenever there are loops iterating over an unbounded query, and each loop iteration leads to some rendered content. The generated refactorings use the well-known Ruby gem, will_paginate.

Asynchronous loading can be used to pull-in supplemental content after the main page has rendered. It generally only makes sense for the slowest elements on a page. Panorama also takes into account all of the view elements that depend a given query to ensure asynchronous refactoring suggestions can truly remove a given query execution from the initial page generation.

The generated refactoring for an asynchronous load generates a new view file and controller action with corresponding route, and the modifies the view to load the content asynchronously.

Accuracy reduction is perhaps a little less obvious. Here Panorama looks for aggregation queries whose results are displayed as numeric values on web pages. As an example, in Redmine the index page shows counts of the number of issues assigned to and reported by the user. When the user is involved in hundreds of issues, the resulting queries can take more than one second (surprising, ‘hundreds’ is not a big number! you are doing the count in the database, right?).

The proposed UI change in a situation like this is to pick some limit value N, and have the UI show the exact count when there are up to N members, and the text ‘more than N’ (which should really be ‘N or more’ ?) when there are potentially more than N results. In the backend of course this is achieved by adding a limit(N) clause to the query. For maximum and minimum queries, Panorama similarly uses a limit clause, and adds ‘at least’ or ‘at most’ next to the query result display.

Content removal (e.g., removing an expensive-to-compute sidebar item so that the main content the user is interested in loads faster). is pretty self-explanatory from the view perspective. Of course to ensure the expected speed-up is actually achieved, the refactoring generated by Panorama has to track back through the dependency graph to remove the corresponding computation in the action controller as well.


We evaluate Panorama using a suite of 12 open-source Ruby on Rails applications, including top 2 most popular Ruby applications from 6 major categories of web applications on GitHub… they have all been actively developed for years, with hundreds to tens of hundreds of code commits.

The apps are Discourse (Ds), Lobster (Lo), Gitlab (Gi), Redmine (Re), Spree (Sp), Ror_ecommerce (Ro), Fulcrum (Fu), Tracks (Tr), Diaspora (Da), Onebody (On), OpenStreetmap (OS) and FallingFruit (FF).

Panorama’s static analysis identified 149 performance-enhancing opportunities across these twelve apps, as shown in the table below.

15 of these are picked at random (6 pagination, 2 asynchronous load, 4 approximation, and 3 content removal suggestions) for performance evaluation. The patches suggested by Panorama are applied, and before and after performance results are generated using a Chrome-based crawler visiting links randomly for two hours.

As shown in Table II, the performance benefits of these view changes are significant. By changing only one HTML tag, these 15 cases on average achieve 8.6× speed up on the server side and 4.5× speed up for end-to-end page load time. Among the four optimization types, pagination, asynchronous loading, and content removal have cases where the end-to-end page load time achieves about or more than 10× speedup.

A separate user study examined 12 web pages drawn from 5 of the applications in the evaluation. For each of these pages, Panorama was used to generate a new page with just one single change. There resulting 12 pages cover all four view type changes, with three instances of each type. The goal of the study was to figure out if the view changes have harmed user perception of the page, and the short answer is No! On balance, users like the new page at least as much if not more than the original. (Though this wasn’t exactly a full-on online-controlled experiment!).