Blog | Best Method for Loading Multiple HubDB Tables Simultaneously

So I ran into an issue the other day.

I have been building this really great-looking catalog using HubDB, and I realized that my new shiny website was getting a really poor Google Page Speed score. 

Now, it wasn't for the reason you might think.

I am loading correctly sized and optimized images thanks to the HubSpot resize_image_url() function. No, that wasn't it. It wasn't too much CSS or Javascript on the page.

It was first time to byte. You might be asking, "What the heck is that?"

What is First Time To Byte?

First time to byte is the amount of time it takes for the server to respond to an http request when you visit a webpage.

Normally (especially with HubSpot) this isn't an issue. But I have a very unique problem. I have six HubDB tables that I am querying at the same time, and the total number of records is around 27,000. For HubSpot, this is a lot.

For HubSpot to return those records, it takes a few tenths of a second more time to run the queries and output the data. Seems harmless, but with heavy importance being placed on page speed by Google, this needed to be addressed.

via GIPHY

What Are My Options?

1. Don't Query So Much Data.

Sounds simple right?

But in my case, I needed to make sure and grab every available manufacturer from the parts catalog database I was creating. This meant that I needed to read all 27K + parts and get their manufacturer name and display each unique one in a list.

Is this the best option? No.

2. Make A Separate Database of Manufacturers.

Again, sounds simple. And this would work, but now I have to maintain a separate table only for manufacturers. Easy, but hard to maintain and keep current, especially when you are updating the DB every few days.

Is this the best option? No.

3. Offload the Task of Reading the Data to the Browser.

With today's browsers being super fast and powerful, a simple task of reading data means I can wait until the page loads, then grab the data, sort through it, and output a list of data all within the browser itself. That relieves the server from having to load the table data before page load, making the time to first byte super low, saving precious loading milliseconds.

Is this the best option? Yes!

Ok, So How Do I Do That?

Since jQuery is already loaded on every HubSpot site, we can use a little known method called $.when().

The idea behind $.when is to load the data first and WHEN it is loaded, THEN do something with it. This is called a deferred method.

Using $.when() we load our 6 Ajax Calls, which will fetch the data at each endpoint simultaneously. Then when it is ready, we use the sibling method $.then() and use it like a callback to do stuff with the data.

Like so:

So Skip To The Point, Show Me How You Really Do This.

$(window).load(function(){

  // URLS
  var product1 = "https://api.hubapi.com/hubdb/api/v2/tables/TABLEID/rows?portalId=PORTALID&limit=9999',
      product2   = "https://api.hubapi.com/hubdb/api/v2/tables/TABLEID/rows?portalId=PORTALID&limit=9999',
      product3   = "https://api.hubapi.com/hubdb/api/v2/tables/TABLEID/rows?portalId=PORTALID&limit=9999',
      product4   = "https://api.hubapi.com/hubdb/api/v2/tables/TABLEID/rows?portalId=PORTALID&limit=9999',
      product5   = "https://api.hubapi.com/hubdb/api/v2/tables/TABLEID/rows?portalId=PORTALID&limit=9999',
      product6   = "https://api.hubapi.com/hubdb/api/v2/tables/TABLEID/rows?portalId=PORTALID&limit=9999';

  var objects = [];

  $.when(
    // Get the HTML
    $.getJSON(product1, function(data) {
      product1Data = data
    }),
    $.getJSON(product2, function(data) {
      product2Data = data
    }),
    $.getJSON(product3, function(data) {
      product3Data = data
    }),
    $.getJSON(product4, function(data) {
      product4Data = data
    }),
    $.getJSON(product5, function(data) {
      product5Data = data
    }),
    $.getJSON(product6, function(data) {
      product6Data = data
    })

  ).then(function() {

    if(product1Data) {
      $.merge( objects, product1Data.objects );
  //     console.log(product1Data.objects)
    }
    if(product2Data) {
      $.merge( objects, product2Data.objects );
  //     console.log(product2Data.objects)
    }
    if(product3Data) {
      $.merge( objects, product3Data.objects );
  //     console.log(product3Data.objects)
    }
    if(product4Data) {
      $.merge( objects, product4Data.objects );
  //     console.log(product4Data.objects)
    }
    if(product5Data) {
      $.merge( objects, product5Data.objects );
  //     console.log(product5Data.objects)
    }
    if(product6Data) {
      $.merge( objects, product6Data.objects );
  //     console.log(product6Data.objects)
    }

    //console.log(objects);
    var lookup = {};
    var items = objects;
    var result = [];

    for (var item, i = 0; item = items[i++];) {
      var name = item.values[1];

      if (!(item.values[1] in lookup)) {
        lookup[item.values[1]] = 1;

        // Do Somthing With The data
        console.log(item.values[1]);

      }
    }
  });
});

First, you'll notice that I'm loading this after the page loads. This is to make sure my page load stays fast.

Second, I use variables to store all of my data endpoints. In this case, HubDB json api endpoints.

Third, inside of my $.when() I call all of my json endpoints using $.getJSON() . Then, I store the data from each inside a variable.

In my $.then() I merge the data to the empty objects array I declared earlier.

After that, I use the objects array to loop through and get my data.. In this case I am using console.log() to output the results into the console.

I hope this helps get you started!

This is a little more advanced that my traditional posts, but if you're able to follow along, you'll be able to vastly improve the speed of your website over using the traditional HubDB calls in HUBl.