After creating your own dashboard in the previous section, you might notice that some of the queries take a few seconds to render the charts. This is because the database must re-run your original query, then build and re-render the chart.

What are Materialized Views?

If you want your dashboard to run very quickly, both for you and other people, you can create a "materialized view" of your datasets. A materialized view is a cached dataset - that is, a very small dataset containing just the answer to your query. So when your dashboard runs, it picks up a few hundred rows of data with almost zero compute, rather than having to re-calculate your metric from hundreds of billions of rows in the core index tables. This change makes your charts return instantly, rather than in a few seconds.

The trade-off: the data is only refreshed once a day (or by an API call, more on that in the "Building Apps" section). This is fine for 90% of analytic use-cases, where you are looking at full days only - for example, looking at Token Counts by Day, you wouldn't want to include the partial day that is today. For reports that include data from today (for example, an hourly TPS report, which could include today's data), it might not be appropriate.

Creating a Materialized View Using Studio

Fortunately, creating a materialized view is very easy - in fact, Space and Time Studio will prompt you to create one!

When you run a query, create a chart, and attempt to save, you'll be prompted with the above message. If you "Save Chart As Is", the chart will be created using your original query.

However, if you "Go Back" and navigate to the "Visualization Details" section (where you assign a "Query Name" and "Chart Name"), at the bottom of that section you'll find a "Materialized View (Cache) Configuration" section.

Space and Time Studio will create the materialized view for you automatically, if you provide a few pieces of information to the three inputs requested:

• "Cache this query result as a materialized view" is a checkbox - if checked, Studio will create the materialized view automatically for you (on chart save), as well as expose the other two required prompts:

• "Enter View Name" is a text box that is partially filled in with SXT_DAPP_VIEWS.Enter View Name

  • The first part (SXT_DAPP_VIEWS) is the database schema name, and cannot be changed
  • The second part is the table name, which you get to choose (characters, numbers, and underscores only)
    For example, you could call your view SXT_DAPP_VIEWS.Ethereum_vs_Polygon_ERC20Token

• "Auto Refresh" is a toggle (on/off) that if set to "On" will auto-refresh your materialized view once a day automatically

  • "On" is recommended, unless your data changes very infrequently (i.e., you're doing historical analysis that will never change, or only change annually)
  • You can manually refresh materialized views with an API call at any time, more on that in "Building Apps"

Update Our Chart: "Token Count, ETH vs Poly"

Let's go to the charts we previously created and update them to be materialized views:

👍

Congrats! You've created your first materialized view!

The "Token Count, ETH vs Poly" now has a small indicator on the thumbnail, saying "in a day". This is showing the approximate amount of time until the materialized view (and therefore the chart) will be refreshed (see image above). The "TPS by Hr, Eth vs Poly" chart thumbnail beside it does NOT have the same tag, since it is based directly on a query that will execute every time the chart is displayed.

How Much Faster?

Let's see how much faster that query returns as a materialized view!

1. Go to "My Dashboards" and open up your Ethereum & Polygon dashboard we created earlier (or use this example dashboard)
2. Because the charts and queries may have already been cached on your local browser memory, force a browser hard refresh
3. Watch the charts load! The materialized view based "Token Count, ETH vs Poly" should always be loaded first, often by a fair margin

All of these queries run within a few seconds, so depending on the speed of your internet, the effect may not be dramatic. But imagine you have a massively complex query which could take several minutes to run - that's a great opportunity for a materialized view!

Repeat the above steps a few times, just to see how fast a materialized view can perform!

Not Just for Charts

Materialized views are not part of charts, they are independent database objects - meaning you can query them directly! This allows you to build many charts on just one materialized view, which is another great way to speed up your analytics!

Try this:

1. Click on the top level tab "Queries" to open the query editor
2. Paste this query into the Query Editor (not the AI prompt editor):

   select * from SXT_DAPP_VIEWS.Ethereum_vs_Polygon_ERC20Token_<your_userid>
   

3. Click "Run Query"

This returns the content of your materialized view, as if it is a small table - because essentially, it is! Per the example table below, it only stored 29 rows, which it could retrieve in a small fraction of a second:

Click on "Query Visualization" to start building new charts on this same data!

Get Credit:

You'll get credit for this activity if you:

• Create a materialized view called:
  SXT_DAPP_VIEWS.Ethereum_vs_Polygon_ERC20Token_<your_userid>
  If the name above + your UserID is too long for the 50 character limit, that's fine - just enter what fits.
  If your UserID has special characters that aren't accepted, simply replace them with underscores ().
• Run a query directly against that materialized view:

  select * from SXT_DAPP_VIEWS.Ethereum_vs_Polygon_ERC20Token_<your_userid>