This article is outdated, you can access the most current version here: https://doc.onedata.de/apps/apps-docs/odml-documentation/AppBuilder/sync-sets/SyncSets.html#optimizing-the-performance-of-syncsets

Article Content

1. Introduction
2. Technical Approaches for Computing SyncSets
   1. Client-Side Filtering
   2. Server-Side Filtering
3. Optimizing Performance With Server-Side Filtering
   1. Create Database Indexes with Curl
   2. Create Database Indexes with Postman
   3. Summary

Introduction

SyncSets are a way to propagate filters between different datasources and elements. They allow you and the users of your applications to slice and dice individual subsets of the datasets and take a closer look at the data that matters.

If you want to know how to integrate SyncSets to your App, have a look a the SyncSets article. Here, we will show how you can optimize the performance of your app when using SyncSets. 

Therefore the next subsection will give an introduction to the different technical approaches for SyncSets supported by ONE DATA Apps. The subsequent sections will show how the performance of the SyncSet can be tweaked by using server-side filtering with appropriate database indices.

Technical Approaches for Computing SyncSets

A SyncSet links two or more datasources by a number of columns that share a common value range. The target datasource is then filtered by the values present in the columns of the source datasource. ONE DATA Apps supports two approaches to compute the filtered results of a SyncSet, which will be explained in the following sections.

Client-Side Filtering

By default the SyncSet computation is done on the client-side. This means that the distinct values of the filtered datasources are computed on the ONE DATA server and then sent to the Apps client running in the users web browser. Then the Apps Client requests filtering of the target datasource by these distinct values from the ONE DATA server according to the received values. This communication of distinct values via the users web browser is depicted in the following figure:

The main advantage of this approach is that it supports a wide variety of datasource types. You could for example connect a Parquet-based data table with a data table that is based on a connection to a PostgreSQL database. While this gives some flexibility when building your app, using client-side filtering also has some drawbacks.

It relies on filtering based on distinct values as described above. Thus it is limited by the maximum number of distinct values that the ONE DATA server can compute. Every ONE DATA instance has a specific value configured that defines the maximum number of distinct values that can be retrieved. The default value for this configuration option is 120. Therefore you should only use client-side filtering if you know that the number of distinct values in the columns specified in the joins-section of your SyncSets will be small. Apart from that, using client-side filtering can negatively impact the overall loading speed of your app when using complex or cascading SyncSets, as the datasources need to be filtered one after another.

In summary client-side filtering is a good choice when using data from various sources. This can be especially helpful in a prototyping-phase. It is not the right choice if you expect to have lots of different values in the columns of the SyncSet or want to cascade your SyncSets.

Server-Side Filtering

In order to mitigate the shortcomings of client-side filtering, you can use server-side filtering in your app. It can be enabled by setting the following flag in the global-section of your app configuration:

When using server-side filtering the evaluation of the SyncSets is done on the ONE DATA server allowing for improved performance. As the computation is done at the backend, the SyncSets are not limited by the distinct value limit. Thus it is possible to filter based on columns with large amounts of distinct values. Apart from that it is not affected by the dependencies between your datasources imposed by the SyncSets. Thus the data can be computed in parallel speeding up the overall loading time of your app.
The main limitation for using server-side filtering is that it can only be used with data tables that have the PostgreSQL storage type. Apart from that it is necessary that the ONE DATA instance is configured to support execution of data requests using native SQL instead of Spark. Please contact your ONE DATA server administrator to enable this option if it is not available.

In summary, using server-side filtering can improve the overall performance of your app and is not affected by the limitations of client-side filtering. It requires that all your data tables are based on tables in a PostgreSQL database. If this is possible in your use-case, server-side filtering is the right choice. To get the best results, please read the next section on optimizing your data storage for server-side filtering.

Optimizing Performance With Server-Side Filtering

When using SyncSets with server-side filtering, the filter conditions imposed by the SyncSets are mapped to a SQL-query executed on the relational database where the data is stored. This requires that the distinct values of the columns defined in joins of your SyncSets are computed by the database management system (DBMS). This is a very expensive operation - especially if your dataset contains a large number of rows. Thus it can slow down the performance of your app and may also impact other apps that rely on data from the same database.
By using appropriate database indices this performance bottleneck can be mitigated. This section will describe how you can define indices on your data tables in order to speed up SyncSet evaluation.

An index is a data structure maintained by the DBMS storing information about the data and where it is located in order to allow fast access to it. When an index is defined, the distinct values can be read directly from it. Thus it is not necessary to load the complete data and compute the distinct values from it. As the number of distinct values is likely rather small compared to the complete data, this leads to a significant speed-up of the query execution.

If a SyncSet is used with server-side filtering and no index is defined on the join-columns, a warning similar to the following one is shown in the App Builders error console:

In this case it is recommended to define an index on the columns listed in the warning. If you have access to the PostgreSQL database used by your ONE DATA instance, you can connect to it with a tool like DBeaver and create an index with the following command. More information about how to create database indices in PostgreSQL can be found in the official documentation.

CREATE INDEX index_name ON your_schema.table_name (column1, column2);  

curl --location --request PUT 'https://internal.onedata.de/api/v1/data/content/db-index?dataId=f6f54798-0674-42fd-a5bf-18530514ff54' \
--header 'Authorization: <your ONE DATA access token>' \  
--header 'Content-Type: application/json' \  
--verbose \  
--data-raw '{  
   "unique": false, 
   "columns": [ 
     {"name": "country"}, 
     {"name": "region"} 
   ]
 }'  

...  
< HTTP/1.1 200  
< Access-Control-Allow-Origin: *  
< Vary: Origin  
< Access-Control-Max-Age: 31536000  
< Access-Control-Allow-Credentials: true  
< Access-Control-Expose-Headers: Authorization  
< Access-Control-Expose-Headers: x-auth-token  
< Authorization: Bearer eyJhbGciO...  
...