Indexing and partitioning

Indexing

Use indexing to improve query performance on frequently searched columns within large data sets. Without an index, Cinchy begins a data search with the first row of a table and then follows through the entire table sequentially to find all relevant rows. The larger the table(s), the slower the search.

If the table you are searching for has an index for its column(s), however, Cinchy is able to search much quicker.

In the below example, we will set up a query for a Full Name field. When you create an index for that field, an indexed version of your table is created that's sorted sequentially/alphabetically.

When you run your query on this index, that table will be searched using a binary search.

A binary search won't start from the top record. It will check the middle record with your search criteria for a match. If a match it not found, it will check whether the found value is larger or smaller than the desired value. If smaller, it reruns the data check with the top half of the data, finding the median record. If larger, it reruns the data check with the bottom half of the data, finding the median record. It will repeat until your data is found.

Set up an index

This example uses a table with employee names (Image 1). We want to search for John Smith, using the Full Name column.

1. To set up your index, select Design Table from the left navigation tab.

2. Click Indexes.

3. Select "Click Here to Add" and fill out the following information for a new index. Click save when done (Image 3):

• Index Name.
• Select the column(s) to add to your index. In this example, you want to select the Full Name column to be indexed.
  • You can select more than one column per index.
• Select the Included column(s) to add to your index, if applicable.
  • The difference between regular columns and Included columns is that indexes with included columns provide the greatest benefit when covering your query because you can include all columns your query may reference, such as columns with data types, numbers, or sizes not allowed as index key columns.
  • For more on Included Columns, click here

1. You can now query the full name column for John Smith and receive the results quicker than if you hadn't set up the index (Image 4).

info

Note that there is no UI change in the query builder or your results when running a query on an indexed column. The difference will be in the speed of your returned results.

Full-text indexing

A full-text index is a special index type that provides index access for full-text queries against character or binary column data. A full-text index breaks the column into tokens and these tokens make up the index data.

Set up a full-text index

1. Click on Design Table > Full-text Index
2. Add in the desired column(s) and click save when done (Image 5).

Columnar indexing

Columnar Indexing (also known as Columnstore indexing) is available when running SQL Server 2016+. It's not currently available on a PostgreSQL deployment of the Cinchy platform.

Columnar indexes are used for storing and querying large tables. This index uses column-based data storage and query processing to improve query performance. Instead of rowstore or b-tree indexes where the data is logically and physically organized and stored as a table with rows and column, the data in a columnstore indexes is physically stored in columns and logically organized in rows and columns.

You may want to use a columnar index when:

• Your table has over 1 million records.
• Your data is rarely modified. Having large numbers of deletes can cause fragmentation, which adversely affect compression rates. Updates are also processed as deletes followed by inserts, which will adversely affect the performance of your loading process.

Set up columnar indexing

1. Click on Design Table > Columnar Index
2. Add in the desired column(s) and click save when done (Image 6).

caution

When using a Columnar Index, you won't be able to add any new columns to your table. You will need to delete the index, add your column(s), and then re-add the index.

Partitioning data

Partitioning data in a table is essentially organizing and dividing it into units that can then be spread across more than one file in a database. The benefits of this are:

• Improved efficiency of accessing and transferring data while maintaining its integrity.
• Maintenance operations can be performed on one or more partitions more efficiently.
• Query performance is improved based on the types of queries most frequently run.

When creating a partition in Cinchy, you use the values of a specified column to map the rows of a table into partitions.

Set up a partition

This example sets up a partition that divides the employees based on a Years Active column (Image 7). You want to divide the data into two groups: those who have been active for two years or more, and those who have only been active for one year.

1. Click on Design Table > Partition

1. Fill in the following information and click save when done (Image 8):

• Partitioning Column: this is the column value that will be used to map your rows. This example uses the Years Active column.
• Type: Select either Range Left (which means that your boundary will be <=) or Range Right (where you boundary is only <). In this example we want our boundary to be Range Left.
• Add Boundary: Add in your boundary value(s). Click the + key to add it to your boundary list. In this example we want to set our boundary to 2.

Once set up, this partition will organize the data into two groups, based on our boundary of those who have a Years Active value of two or above.

2. You can now run a query on your partitioned table (Image 9).

info

Note that there is no UI change in the query builder or your results when running a query on a partitioned table. The difference will be in the speed of your returned results.

For more formation on creating, modifying or managing Partitioning, please visit Microsoft's Partitioned table and Indexes documentation.

Best Practices for large tables

For optimal performance in Cinchy when dealing with large tables, particularly those with over 10 million records, we recommend creating an index on the [Deleted] and [Cinchy Id] columns. This index has proven effective in reducing render times and preventing timeouts.

Add the [Deleted] & [Cinchy ID] index

1. Select the target table: Select the table you want to change from the Cinchy home screen.
2. Access Index Settings: Navigate to Design Table > Indexes.
3. Create New Index: Select "Click Here to Add" and fill out the necessary details for your new index.
4. Select Columns: Add [Deleted] and [Cinchy ID] columns to Columns in Index.
5. Save Configuration: Verify and select Save.

By following these guidelines, users managing large datasets in Cinchy can achieve a more efficient and responsive data interaction experience.