Performance
Performance is a critical part of the user's experience, which is why we have designed features to reduce query response times.
Field usage
This requires a version of the Vizzly Query Engine > 0.2.11.
One feature of our data sets, allows you to choose which fields can be used as dimensions and measures, and limit the granularity that can be applied to date time fields. For example, you could only allow grouping by hour, day or year instead of per second or per minute.
If you have an SQL integration, this can be used to your advantage by creating indexes based exclusively from the fields you intend to be used as dimensions.
Another time to consider turning to indexes is for fields you think will be commonly used in filters and sorts. However, be aware that whilst indexes are optimal for read heavy tables, they can impact write performance.
Usage in the config
Each field in a data set can optionally provide a canBeDimension or canBeMeasure key with a boolean value. For example;
{
"canBeDimension": true,
"canBeMeasure": false
}By default, the values of these properties are both true, and setting false will remove the field as an option in the chart editor.
NOTE: This requires a version of the Vizzly Query Engine > 0.2.11 and of our dashboard react library (opens in a new tab) > 0.0.35
SQL table structure
Although we do support joins in SQL integrations, they can be the cause of slow queries and therefore we'd recommend avoiding them if at all possible.
In an ideal world, each Vizzly data set would pull data from its own table, thus eliminating the need for joins.
Caching
This requires a version of the Vizzly Query Engine > 0.2.11.
Vizzly comes packaged with a smart client side cache for all queries, which will reduce the load on your underlying database. The Vizzly query engine also has a shared cache for queries that only include fields which you've specified are cache-able.
To flag a field as being allowed for use in a public cache, you will need to set the "canBeUsedInSharedResultCache": true property on the field in your data set.
The key points to note are;
- The result will only be cached if all fields used in the query have the
canBeUsedInSharedResultCacheproperty set to true in the data set. - Any secure filters which make the query personalized will not be added to the query.
- Only certain queries from the Vizzly dashboard allows the shared cache to be used.
By default, the canBeUsedInSharedResultCache property is set to false for all fields.
An example use case
Commonly, local and global filters are based upon fields that don't contain private information. For example, say we have this table of data;
| Order ID | Customer Email | Total Paid | Category |
|---|---|---|---|
| 1 | james@example.com | 35.00 | Entertainment |
| 2 | sophie@example.com | 35.00 | Sports |
Clearly, the "Customer Email" field contains private information, however the "Category" field does not, and the "Category" field is the most likely field that you'd want to specify a global or local filter for.
Therefore, if we were to mark the "Category" field with "canBeUsedInSharedResultCache": true in the Vizzly data set, then the Vizzly query engine will use this information
to optimize the performance of queries used to offer the global or local filter based on the "Category" field.
Query engine
Workload
The docker image we provide does not do computationally heavy lifting. The noteworthy operations include validating JWTs and transforming Vizzly queries into the language your underlying database requires.
Hardware considerations
The recommended hardware requirements will depend on a number of different factors, including:
- The number of concurrent users you expect to be viewing the dashboard.
- How many charts a typical user has on their dashboard.
- How many additional data controls exist on each chart.
To increase performance, consider scaling horizontally by adding more containers running the Vizzly docker image, or vertically by provisioning larger compute resource.
Resiliency
The query engine in the docker image is stateless and so it can be easily scaled horizontally to reduce performance or resiliency concerns and easily auto-scaled on all major cloud providers if your traffic pattern is spiky.