Query Options

Query options are the framework's implementation of filtering, sorting and paging for a view.

By default, the ODBC interface uses the standard implementation of the Ometa framework for its query options. Generally speaking, the framework will execute the query from the interface script, and then process the data to apply paging, filtering and sorting onto it. For most queries, this is sufficient, as it removes the overhead of implementing the query options through the interface script.

However, when working with larger datasets or more complicated queries, this can have an impact on the performance.

To allow optimizations, the framework offers a few context fields that can be used in the interface script. This will effectively move the processing time from the Ometa framework server, to the database server where it can optimize the query.

As an example, given this query:

SELECT Id, ProductName, Price FROM [dbo].[Product]

When applying a filter for the ProductName column from ADM: The framework will execute the query and evaluate each record on the framework server. With a lot of records (and ignoring paging for simplicity’s sake), this will be slow.

Using one of the framework placeholders as such:

SELECT Id, ProductName, Price FROM [dbo].[Product] WHERE {$@ometa.filter}

the framework will translate it to this:

SELECT Id, ProductName, Price FROM [dbo].[Product] WHERE ([ProductName] = 'Bike')

With this query, the SQL server will now do the filtering work which can make use of indexes and such for better performance. It will now return less data back to the Ometa framework which will also speed up the processing on the Ometa side.

Keep in mind that these context fields are optional. The framework will continue to support filtering, paging, and sorting regardless.

Sorting

Sorting is applied when end users click on columns in an ADM. This context field is available to implement this:

{$@ometa.sort}: the external name of the field to order, followed by the direction: ProductName DESC, Id ASC, excluding the ORDER BY keyword.

The external name will come from the input method field if it exists, or the output method field otherwise.

If no query options are available, or no column is sorted:

{$@ometa.sort} will default to 1 ASC.

Query options can be missing if the method is not executed through a view, for example when testing it in the business connector, or when running as a method job. The default is provided to not require additional logic and prevent SQL syntax issues.

The sorting can be used regardless of the other context fields for paging or filtering, without impacting data integrity.

This benefits performance because the framework does not have to process all data to sort it locally, and can stop reading once it has reached its page size.

Example

The example only does sorting through the interface script. Filtering and paging still works and is done by the framework locally.

SELECT * FROM [dbo].[Product]
ORDER BY {$@ometa.sort}

SELECT * FROM [dbo].[Product]
ORDER BY [ProductName] DESC

SELECT * FROM [dbo].[Product]
ORDER BY [ProductName] DESC, [Id] ASC

SELECT * FROM [dbo].[Product]
ORDER BY 1 ASC

Filtering

Filtering can be done on the ADM by clicking on the side of the header in ADM. The ODBC interface can interpret these filters and translate them into a SQL statement, this filter can then be used in the interface script:

{$@ometa.filter}: the SQL statement equivalent to the current query options, excluding WHERE keyword.

The external name will come from the input method field if it exists, or the output method field otherwise.

If no query options are available, or no column is sorted:

{$@ometa.sort} will default to 1=1.

The sorting can be used regardless of the other context fields for paging or sorting, without impacting data integrity.

The performance benefit from using this in the interface script comes from:

The database server can optimize its query plan, for example by using indexes.
Ometa has less data to sift through.

Example

The example is showing a combined filter, where users get to see all red coloured products and can apply additional filters through the ADM.

SELECT * FROM [dbo].[Product]
WHERE Color = 'Red' and {$@ometa.filter}

SELECT * FROM [dbo].[Product]
WHERE Color = 'Red' and ([ProductName] LIKE '%Bike%')

SELECT * FROM [dbo].[Product]
WHERE Color = 'Red' and ([ProductName] NOT LIKE '%Bike%')

SELECT * FROM [dbo].[Product]
WHERE Color = 'Red' and ([ProductName] LIKE 'Bike%')

SELECT * FROM [dbo].[Product]
WHERE Color = 'Red' and ([ProductName] LIKE '%Bike')

SELECT * FROM [dbo].[Product]
WHERE Color = 'Red' and ([ProductName] = 'Bike')

SELECT * FROM [dbo].[Product]
WHERE Color = 'Red' and ([ProductName] != 'Bike')

SELECT * FROM [dbo].[Product]
WHERE Color = 'Red' and (([ProductName] = 'Bike' OR [ProductName] = 'Helmet') AND [ReleaseDate] > '2023-06-16 00:00:00.000')

*The date format will use the culture settings from the input field.

Paging

Most views on the framework have paging enabled, these context fields are available to implement this:

{$@ometa.firstItem}: the number of items to skip. This will be the page number * page size for the current view.
{$@ometa.pageSize}: the numbers of items to return. This will be the page size of the view plus one to check if a next page is available.

If no query options are available:

{$@ometa.firstItem} will default to 0.
{$@ometa.pageSize} will default to the BCSL.BCS_ODBC.MaxPageSize config setting, or 1.000.000 if not configured. Changing this setting requires the process to be restarted.

Query options can be missing if the method is not executed through a view, for example when testing it in the business connector, or when running as a method job.

Important

Ometa can only forward the paging if the sorting and filtering is also forwarded. Otherwise, the data could be incorrect, since Ometa would be sorting and filtering locally on a limited dataset. If filtering or sorting is not forwarded, or could not be parsed, paging is not forwarded either to ensure correct results.

Example

In the example, only paging was specified, there was no specific filter or sorting set. The view is configured to have page size 25.

SELECT * FROM [dbo].[Product]
WHERE {$@ometa.filter}
ORDER BY {$@ometa.sort}
OFFSET {$@ometa.firstItem} ROWS 
FETCH NEXT {$@ometa.pageSize} ROWS ONLY

SELECT * FROM [dbo].[Product]
WHERE 1=1
ORDER BY 1 ASC
OFFSET 0 ROWS 
FETCH NEXT 26 ROWS ONLY

SELECT * FROM [dbo].[Product]
WHERE 1=1
ORDER BY 1 ASC
OFFSET 25 ROWS
FETCH NEXT 26 ROWS ONLY

SELECT * FROM [dbo].[Product]
WHERE 1=1
ORDER BY 1 ASC
OFFSET 0 ROWS
FETCH NEXT 1000000 ROWS ONLY

*{$@ometa.pageSize} will default to the BCSL.BCS_ODBC.MaxPageSize config setting, or 1.000.000 if not configured. Changing this setting requires the process to be restarted.

Performance Impact

The performance benefit from using the query options in the interface script is not predictable, as it completely relies on the database server, the amount of data, schema configuration (like indexes) etc...

Nonetheless, this example will compare the performance of the regular query against the performance of a query that integrates the query options.

The dataset used is the Ometa BAM logging with currently ~800.000 records (~5 GB).

	Default Query	Integrated SQL Query Options
First Page	2,067	0,858
Second Page	2,054	0,820
Filter	2,873	0,304
Sorting	20,118	0,859

The performance gain accross all test cases are due to the database server optimalizations which is returning data much quicker.

The sorting benefits the most, because Ometa does not have to process all 800.000 records to sort the data locally. Instead, the framework can stop reading after 25 records.

SELECT *
FROM [Ometa BAM Logging].[dbo].[Log]

SELECT *
FROM [Ometa BAM Logging].[dbo].[Log]
WHERE {$@ometa.filter}
ORDER BY {$@ometa.sort}
OFFSET {$@ometa.firstItem} ROWS
FETCH NEXT {$@ometa.pageSize} ROWS ONLY

The sample was created from a view with page size 25. Filtering was done on a single column ([Severity] = 'Warning') through ADM. Sorting was done by clicking on a single column ([Component]) through ADM.

The timings correspond to the method execution times which excludes any network delays, or rendering times. Averaged over 5 runs each, with no caching.

The database was SQL Server 2019 (version 15.0.2110.4).

Table of Contents

Query Options

Sorting

Example

Filtering

Example

Paging

Important

Example

Performance Impact