Developers

Omnis Technical Note TNEX0012 Jan 2021

Optimizing Omnis Data File Queries for the Omnis Data Bridge (ODB)

For Omnis Studio 10.x or above
By Richard Mortimer, Omnis Technical Consultant.

Introduction

The Omnis Data Bridge (ODB) is a key element of an Omnis Data File application that is deployed into a macOS environment, since this operating system does not permit for multiple clients to concurrently access a shared data file. The ODB is also required in a mixed macOS and Windows environment and can be beneficial in a pure Windows Omnis data file application.

Due to the way that the ODB operates, certain types of queries may not operate as efficiently as in a non-ODB configuration and consequently the speed of operation of those queries may be compromised.
The purpose of this tech note is to discuss some coding techniques that can be applied in order to optimize the performance of your application using the ODB.

ODB Use and Function

The Omnis Data Bridge runs as a separate executable on a Windows, Mac or Linux server and manages one or more data files located on the server. The Omnis client run-times communicate with the ODB using TCP/IP. However, the ODB is not a database management system in the form of a SQL server such as Postgres and the management of the data is still carried out in the client run-time, as would be the case with traditional direct access to an Omnis data file.

Consequently, it is important that data file queries use an appropriate index and that queries do not result in an unnecessarily large number of records being transferred from the ODB to the client side resulting in slow execution.
A single record read using a Find, Single file find or an OmnisSQL SELECT query with an indexed field is unlikely to result in slow execution. However, this may not be the case where a Search is involved, or the command is sorting the results.

Optimizing Queries for the ODB

The types of queries that are likely to use an inappropriate index are Omnis DML queries that use a search or sort the results, for example:

Set search as calculation fOrdHeader.OH_ORDER_DATE=#D
Build list from file on fOrdHeader.OH_ENQ_NO (Use search)

In this case the index OH_ENQ_NO has been specified for the query, however the use of a search will likely result in another index being used to acquire the results.

Similarly, the use of a sort clause may result in the sort field being used as the indexed because the data is already in sorted order, however this may not be the most efficient way to process only those records that are required by the query.

Set search as calculation fOrdHeader.OH_ORDER_DATE=#D
Clear sort fields
Set sort field fOrdHeader.OH_ENQ_DATE
Build list from file on fOrdHeader.OH_ENQ_NO (Use search,Use sort)

To ensure that the query uses the most efficient index, it may be necessary to redesign it in such a way that it does not use a search and the data is sorted after the data has been returned.

A search may incorporate several elements combined using the logical operators & (and) and | (or) to return only the required records. In this case the search may need to be replaced with a simpler query that returns more records than are required into a list and the superfluous records are subsequently removed by searching the list and removing non-matching rows.

The following are some code examples of original code that was found to be inefficient in a ODB environment and the optimized versions.

Original DML Code:

Begin reversible block
  Set main file fOrdHeader
End reversible block

Set search as calculation fOrdHeader.OH_STATUS=1& fOrdHeader.OH_CHASE_PROOF>#D-365&fOrdHeader.OH_CHASE_PROOF<=#D

Find on fOrdHeader.OH_CHASE_PROOF (Use search)
If flag true
  No/Yes message {Do you wish to print the enquiries outstanding report?}
  If flag true
    Do $cinst.$outstandingEnquries()
  End If
End If

In this case the original DML code was not using the OH_CHASE_PROOF index that would return the fewest records. The presence of the OH_STATUS in the search resulted in the use of a different index and inefficient execution of the query.

Optimized DML Code:

Begin reversible block
  Set main file fOrdHeader
  Set current list lOrderList
End reversible block
Define list fOrdHeader
Calculate fOrdHeader.OH_CHASE_PROOF as #D-365
Find on fOrdHeader.OH_CHASE_PROOF
While flag true
  If fOrdHeader.OH_STATUS=1&fOrdHeader.OH_CHASE_PROOF<=#D
    No/Yes message {Do you wish to print the enquiries outstanding report?}
    If flag true
      Do $cinst.$outstandingEnquiries()
      Break to end of loop
    End If
  Else
    Next
  End If
End While

The optimized DML code with the Find command forces the use of the indexed field OH_CHASE_PROOF that returns the fewest records and a separate If statement is used to ensure that the record matches the required OH_CHASE_PROOF value and the date is in range.

Original OmnisSQL code

Begin reversible block
  Set current list iOrdList
End reversible block
Define list fOrdHeader
Begin statement
Sta:select * from fOrdHeader
Sta:where OH_CUST_CODE='[C_CUST_CODE]'
Sta:and OH_STATUS=2
If iInvoiced
  Sta:and OH_INVOICE_NO>0
Else
  Sta:and OH_INVOICE_NO=0
End If
Sta:
If iOrdFrom<>''
  Sta:and OH_ORDER_DATE>=@[iOrdFrom]
End If
If iOrdTo<>''
  Sta:and OH_ORDER_DATE<=@[iOrdTo]
End If
Sta: order by OH_CUST_CODE
End statement
Do $ctask.tSqlObj.$execute() Returns #F
Do $ctask.tSqlObj.$fetch(iOrdList)

In this second example, the complex nature of the OmnisSQL Where clause and the fact that it contains an Order By clause caused inefficient execution of the query.

Optimized OmnisSQL code:

Begin reversible block
  Set current list iOrdList
End reversible block
Define list fOrdHeader
Begin statement
Sta: select * from fOrdHeader
If iFromDate<>''
  Sta: where OH_ORDER_DATE>=@[iFromDate]
  If iToDate<>''
    Sta: and OH_ORDER_DATE<=@[iToDate]
  End If
Else If iToDate<>''
  Sta: where OH_ORDER_DATE<=@[iToDate]
End If
End statement
Do $ctask.tSqlObj.$execute() Returns #F
Do $ctask.tSqlObj.$fetch(iOrdList)
Do iOrdList.$search($ref.OH_STATUS<>2| $ref.OH_CUST_CODE<>C_CUST_CODE,kTrue,kFalse,kTrue,kTrue)
Do iOrdList.$remove(kListDeleteSelected)
If iInvoiced
  Do iOrdList.$search($ref.OH_INVOICE_NO=0,kTrue,kFalse,kTrue,kTrue)
Else
  Do iOrdList.$search($ref.OH_INVOICE_NO>0,kTrue,kFalse,kTrue,kTrue)
End If
Do iOrdList.$remove(kListDeleteSelected)
Do iOrdList.$sort($ref.OH_CUST_CODE)

The optimized OmnisSQL code initially only tests the indexed column OH_ORDER_DATE and returns superfluous records into the list iOrdList which is not in the required sort order.
Subsequent list searches are then used to identify and remove unwanted rows and finally the list is sorted into the required order.