Various tips and tricks can be employed to improve the performance of custom SQL views.

Test the performance of custom views

If a query is slow when is being written, it will be slow in the xMart UI and API. Test the performance of custom SQL views. It should be possible to return the top 100 rows in 2 seconds or less.

If a view is slow, follow the recommendations on this page to improve its performance.

Include Sys_PK

Include the system field “Sys_PK” in your custom view. Sys_PK is a system field present in all system-generated views of storage tables. It is a pre-sorted integer that is used for sorting paged results. Even if you think you are not using paging, you probably are using paging, since the xMart UI and the OData API both rely on paging.

If a different sort field is being used using $orderBy in the API or by sorting a column in the UI, Sys_PK is irrelevant.

Match BPK sequence to most common filters

The sequence of business primary keys (BPKs) is critical for data retreival performance. The most common filters used in data retrieval should be first in the sequence (ie have lower sequence number in the data model). This is because an SQL table index is created based on the sequence of BPKs and, as for all indexes in SQL Server, the sequence of the key fields in the index determines data retrieval performance.

For example, if data is most commonly queried by “country ISO code” and “year” fields, assign “country ISO code” and “year” the lowest sequence numbers in the data model.

Avoid complex custom views

Try not to develop very slow and complex custom SQL views. Strive to model your data in a way that eliminates the need for complex processing later.

Materialize slow custom views

If it’s not possible to further optimize a slow custom SQL view, materialize it as a table with as set of BPKs that match the most common filters and make the materialized table public rather than the view.

“Materialize” just means creating a table matching the structure of the view and populating the table based on the view in a pipeline. This causes two copies of the data to exist but is preferable to an unusable custom view.

The pipeline can be very simple, and performed purely at the databaes level. For example, a pipeline to materialze the view SLOW_CUSTOM_VIEW to the FAST_TABLE table.

IsDbOnly=”true” makes this a pure database-level pipeline (faster).
LoadStrategy=”MERGE” will only result in updates if there are updates.
DeleteNotInSource=”true” will soft-delete any records in the materialized table which disappear from the custom view.

<XmartPipeline IsDbOnly="true">
  <Extract>
    <GetMart TableCode="SLOW_CUSTOM_VIEW" OutputTableName="data" />
  </Extract>
  <Load>
    <LoadTable SourceTable="data" TargetTable="FAST_TABLE" LoadStrategy="MERGE" DeleteNotInSource="true">
      <ColumnMappings Auto="true" />
    </LoadTable>
  </Load>
</XmartPipeline>