Morris Development

Custom System Development and Integration

Using IndexedDB to enhance performance

As our focus is entirely on internal database systems, we deal a lot with large datasets that remain open on people’s screens. Obviously, this can cause problems with synchronization, where someone at one desk made a change and it did not appear on the screen of another user. This could be done with Push notifications via websockets, and that is actually part of the solution we use. However, many of our clients need a large amount of local data to work with, using online pivot tables and graphs, often changing from primary screens back and forth, sometimes even having 5 or 10 windows open at the same time – needing them all to sync in real time.

Initially, we did some long-polling of the server and maintained a javascript variable to hold the main datastore of active “Jobs”, which is where they have about 10k records of about 100 columns. Obviously, this was not recommended, but the business model they have simply requires it. (The server database is relational and holds this data in multiple tables). We display the data in a tool called “AG-Grid”, which is quite remarkable. AG-Grid allows for server-side filters and paging, but the customer was not satisfied with the results, so we decided to work with a local copy.

After an initial success using LocalStorage to maintain a full copy of the data in AG-Grid, whereupon the user would login and instantly see their data, and we could update it in the background, we discovered the limits of LocalStorage and the system automatically failed-over to simply downloading a fresh copy with each load. While we could have attempted a variety of tweaks to the structure, we decided to avoid the issue entirely by using IndexedDB to maintain a local datastore.

With local storage, we simply stringified the entire JSON array and put it in LocalStorage. With the new process, we’re keeping each job as an indexed record. Here is the current synchronization flow:

  1. A new user logs in and the server creates an empty Jobs datastore in IndexedDB.
  2. The JobMaster page, which holds the AG-Grid, loads the records from the datastore using the “list” command and populates an object variable jobMaster.data, held in an injected service, with the records
  3. We then bind ag-grid to that jobMaster.data. In this case, displaying an empty recordset.
  4. We then call an api called JobMasterUpdate, requesting all records with an update/create date greater than our last value in jobMaster.data. We call that the ExpireDate. In this case, it is null, as there are no records in IndexedDB, so we’re asking for all of them.
  5. Here we display a dialog box with a spinner, to notify the user that this initial load might take a while. Perhaps over 15 seconds.
  6. Upon reception of the data, we append all records to jobMaster.data, and refresh the cells in the grid, close the dialog, and displaying the populated grid to the user.
  7. That same data will now be inserted into the object store “Jobs”, using the JobID as it’s key and the updatedDate as an indexed item. This is done asynchronously, starting at the oldest and moving forward, in the event of a page closed prior to the process finishing we can be ensured the last updated date is accurate and no data is skipped.

The benefit of this, as you may be able to see, is that the future login and page load events will be coming from IndexedDB into the grid almost instantly, where the rendering time of AG-Grid, which is very efficient, is actually the longest wait. As the users are daily-users, logging in each day from 7am-3pm the secondary load from the server is generally quite small, often no more than a few records.

Now, that is the initial load, and the reason it is so small is due to the fact that the recordset is maintained during the day. Each time any user updates any values in the database via the API server, a broadcast is made using SignalR. It is a non-specific broadcast, simply notifying every user that a change has been made. It is much akin to “John has joined the chat” on a web meeting. Each browser is listening for this alert and uses it to trigger and update request to the main database. Here is the process flow.

  1. User 1 logs in at 7am and gets the latest data from the server into their IndexedDB and their jobMaster.data variable
  2. As there is an Index on the UpdatedDate, we simply us that to get the updatedDate from the most recently updated record and set it as the “expireDate”. In this case, we’ll say it is 6:30am from an early riser updating some records.
  3. Now User 1 has the JobMaster page open on their screen, but is drinking his coffee and reviewing data from last night.
  4. User 2 updates a record to set the status of job 123456 to “Complete”. The update is done in real time via a REST call to an api.
  5. The API server implements the update and triggers a SignalIR broadcast that an update has been made.
  6. User 1’s browser hears the broadcast and triggers a request for any updates to the JobMasterUpdate api, sending the expire date of 6:30am.
  7. The API server will return all the records changed since 6:30am, which will include the status update of job 123456 by User 2
  8. We have IndexedDB check the object store for a job with the id 123456, and when it finds it, it replaces it with the new record.
  9. Upon a successful update, we also update the jobMaster.data variable, to which AG-Grid is bound
  10. Once that data variable is updated, we call for a “refreshCells()” on the AG-Grid and the updated value from User 2 will simply appear on the screen of User 1.

Now this process is not designed to work asynchronously, whereby a user could be offline. The goal is to have a system where a large group of users can manage a large amount of data that they need to see all at once, and not update their own browsers at all.

At this point, we have only a small subset of users with this beta system using IndexedDB instead of reloading the entire jobMaster.data every time they login/refresh. I believe we’re looking at a successful model, but I’m certain there will be a series of issues to be addressed along the way. I’ll try to update this as we go.

  • The first failure! One issue with IndexDB is the requirement to have “versions”. When you make a change to the object store, you are required to increment the version of the database, which triggers and updaterequired event, which is necessary to take place before you can call for the data. This initialization check is often done prior to every request, but it isn’t instant and IndexedDB is ansychronous, so you must do that call immediately and wait for it to be done prior to calling for any data. We found the best thing to do was to call it in a config service using angular’s APP_INITIALIZER. Once it is initialized, we’re good to go and can place the database in a service variable. (many have advised against this, but the failure of a single call isn’t a big deal, where accidentally triggering multiple initializations can cause the entire site to freeze. I’m sure there’s a way to make up for it, but for the life of me I can’t imagine why it’s a bad thing when using angular.)
  • The second failure! Remember those versions? Well, guess what? If you have to roll back a change to the production site and push an older version, the older version may work perfectly on a clean QA machine, but if you’ve been on the same domain with a newer version of the database installed, and your site requests an older version of the database, you get an error. We knew this, but didn’t consider the ramifications in the event of a roll-back.
    • For now, the hack solution has been to pull a branch from Git, modify the version and then push the rebuild. However, I believe we will be initiating an API call to the server to get the latest version first and then initialize the database. The problem is going to be the pause. if we use APP_INITIALIZER, it only works with the primary module. The speed of the initialization is negligible, but an API call is not. I don’t want the system to pause before it even gets to the login page. I believe the solution will be to place the DB Initializer in a route-resolver for the submodule used for this jobs page (it’s lazy-loaded). It is likely, however, that we’ll be moving the login page to a simple HTML doc, outside the Angular framework and then redirect to the primary system after authentication (we use Google oAuth, so we’re leaving the framework anyway)