Order Property & Indexes

One of the dysfunctions of Sage300 is that index use is explicit. Whenever Sage300 executes a query, rather than letting the database devise which index to use, the query generated by Sage300 expressly states which index the database must use. I can only blame Sage300’s early days of running on Btrieve for this, but the result is that you must know how the index will affect the underlying query.

To alter the index is simply a matter of altering the Order property of the view.

One of the key discoveries of our research is that the Order property of a view can drastically affect the functionality of the search operation.

Cursor Accessed Indexes

It is worthy to note that Sage300 employs two strategies for retrieving records: simple and cursor accessor.

Simple indexes simply specify a where clause with an SQL statement.

Cursor accessed indexes use a stored procedure (with a server side cursor for positioning) to setup the query and subsequent calls to the stored procedure to move the cursor and return the next record. The code below illustrates the initial setup of a cursor based index.

Please note the explicit field list has been removed and replaced with * for brevity.

Sage300 then calls the stored procedure to return the next record from the query:

Whilst our testing indicates the index type is transparent to calling operation, the speed of cursor accessed indexes is significantly slower than simple indexes because of the SQL call to retrieve the next record.

Our investigation was a little inconclusive, and we’d be happy to hear from others on this point; but the use of cursor accessed indexes appears in Sage300 to be dynamic:

• A stored procedure/cursor would be used when an index was specified but the search did not include any index fields. This would only occur on non-composed views or the parent view of a composed view.
• Certain indexes always use cursors composed or otherwise. The API doesn’t provide any means to query if an index is cursor accessed or simple.

Read

The first access method is the most simple: the Read function is designed to return the single record pointed to by the values of the fields of the corresponding Order or index. The following code returns a single O/E Order, using the Order Number:

SQL Trace

If we look at the SQL trace for the Read method, not only does it select just a single record with the top 1 directive; it also provides a hint to use the index corresponding to the Order selected.

DBSpy

DBSpy issues a similarly short statement involving a single Get operation.

In terms of database access and speed, Read is certainly the most efficient since it only ever retrieves a single record based on an index.

Using Read to iterate multiple records

Read will only ever return the first matching record based off the values set to the fields used in the index. To iterate multiple records therefore it is not really a viable solution as it would be necessary to first set the key field values and then perform a read, and repeat.

Browse & Fetch

The Browse and Fetch operations combine to allow multiple records to be retrieved and iterated, and in contrast to the Read operation they allow you to search for records using non-indexed fields.

Specifying the Index of a Browse Operation

Like the Read function, the index used changes the way Browse and Fetch retrieve records. In the example below we’re retrieving the Customer record using the Customer’s Name, changing the index to 2 and specifying the single field in the ‘Put’ method used of that index.

SQL Trace

One would expect from the above the Browse operation would override any field set previously, however it does not. Instead, because the field is a part (or all) of the index the value of the field is incorporated into the query.

Please note this query has been cleaned of the stored procedure setup code to illustrate the scenario.

Browse & Non Indexed Fields

Using Browse on non-indexed fields will result in Sage300 omitting them from the resulting query and instead using its own filtering mechanism executed client side in the Sage300 view code.

For example, a search of A/R Customer records using only the customer name will result in returning every single customer record back to Sage300 where each record is evaluated according to the Browse before the resultant dataset is return to the calling client.

TIP: We recommend to search using indexes where ever possible, or use the CS0120 view to execute a query directly on the database (where the database can perform more efficient filtering).

Fetch is not GoTop

As mentioned previously there are several functions (GoTop, GoNext & GoPrevious) which work in conjunction with Browse. The scant inline help provided in VBA & VB6 seems to indicate the Fetch and GoTop functions act equivalently on the fetch of the first record after a Browse.

They do not!

Whereas Fetch always incorporates index field values, GoTop behaves erratically where it includes the values from the Key fields most of the time and then omits values from the key fields at other obscure times (see below). Using the same Customer query as previously the resulting query using GoTop instead of fetch results in the following SQL:

TIP: We don’t have one… why does this happen?

Composed views and searching Child Views

Thus far the focus has been on searching either views not composed or the parent view of a composed view. There are some gotchas when querying composed views!

A view composition is Sage300’s implementation of record relationship and association similar to an Object Relational system such as Hibernate. A view within a composition defines the relationship to one or more other views and in turn how that view is related to other views, etc.

Iterating through a Child View

Once a set of views are composed and a record is retrieved on the parent view, all child records associated to the parent record and only those child records will be available to iterate.

The following example retrieves an O/E Order and Fetch iterates through each of the records of that particular order.

SQL Trace

The SQL trace shows something equally elegant, where on the Fetch operation of the Order Detail View simply retrieve every record for the Order.

It is worth noting that each subsequent call to Fetch does not result in a corresponding SQL query, so Sage300 must hold an in-memory cache of the resulting dataset in this instance.

Searching the Child View

To search for a record(s) using the Browse operation simply requires a simple query and because the records being searched are only those related to the parent record it is not necessary to specify order header fields such as ORDUNIQ. For example, to search for all items within an order containing the character ‘3’.

Beware the Dangling Reference!

If we attempted to re-iterate the same set of detail records using the same Browse operation the result would iterate only the last record of the previous iteration and the SQL Trace would show something odd also:

The query includes a filter to restrict to a single record where the LINENUM field is 256!

This is because the LINENUM field at the time the Fetch is set to the last value of the iteration (in this example it was 256) and Browse incorporates any values set to the index fields.

To reset the iteration, either set any key fields back to their default values or use GoTop (which ignores key field values).

Killing the View Composition

Up to this point the examples provided have used the default (0) index on the Child View. It is possible to set the Order property to use a different index on a composed child view.

It is important to note the view composition will hold only when the index’ first field(s) is the primary key of the parent view’s primary key/default index. Using an index where the Parent View’s primary key is not the first field in the view being searched will result in the query returning records not associated with the parent record.

For example, searching using indexes 0, 1, & 6 of the O/E Order Detail view would return only records of the composed order, whereas indexes 2, 3, 4 & 5 will not.

The following example searches a composed O/E OrderDetail view using index 2.

Generates the following SQL:

Irrespective of whether Browse or GoTop are used the resulting SQL generated by Sage300 is based on the order in which the fields of the index are listed. The 1st and 2nd parts of the where clause include the ORDUNIQ field and since the ORDUNIQ field is the 3rd field within the index the 3rd & 4th sub-clauses do not include ORDUNIQ, thus returning non-associated records.

TIP: Note this!

GoTop Revisited

In some Child Views on certain indexes we’ve found GoTop acts like Fetch and includes values from key fields in the query.

If for example, we search the O/E Order Detail View using index 6, the GoTop operation on second iteration includes the ITEM field value in the query (if it’s not explicitly cleared prior to GoTop).

The following Trace shows the SQL generated on second iteration of the above scenario.

FilterSelect & FilterFetch

From our tests FilterSelect is shorthand for setting the Order property and the Browse operation, and FilterFetch is simply the corresponding fetch operation.

Conclusion

The trigger for this post was to educate ourselves on the different search methods available in Sage300 and their underlying functionality.

Our investigation has certainly educated us on some of the gotchas and caveats, which I know for certain, have tripped me up at one point or another. Internally we’ve also found a number of places where we can make existing searches more efficient, particularly those where we search for details within an existing transaction.

For example, prior to this post we were using a FilterSelect & FilterBrowse to locate a detail record within a transaction.

We could re-engineer this to use GoTop and a manual record evaluation thereby avoiding the re-querying of the database on each line.

As always, we welcome your comments.