Early last week, I was given a copy of the beta build of Synergy/DE 9.3. My task was to do some testing of one the exciting new features it includes: The Select classes.
Now, testing isn’t always fun, and it can be frustrating trying to figure out if a bug is really a bug, or just a problem born of having no clue what I’m doing. This time, however, any minor problems I encountered were completely overshadowed by the sheer awesomeness of the new classes.
The Select classes provide a SQL-like syntax for communicating with Synergy databases, and it’s amazing just how simple they are to use. Once I had a basic understanding of how they worked, I was able to compress a simple READS loop – complete with “filters” and error checking – into a single line.
Consider the following code, which loops through active customer records and prints out the customer number, name and last sales date of anyone with no sales for more than a year:
reads(ch_cusmas,cusmas) [err = eof]
if (cusmas.status .ne. ‘A’); If customer is not Active, ignore it
if (cusmas.last_sale.year < lastYear)
The basic syntax and usage of the Select Class is:
foreach myRecord in @Select(@From[, @Where][, @OrderBy])
And so, using the Select classes, I condensed everything into:
customers = new From(ch_cusmas,cusmas)
noNewSales = new Select(customers,(where)status.eq.’A’ .and. last_sale.year < lastYear)
foreach cusmas in noNewSales
(I actually condensed the first three lines into just one foreach statement, but the result is a line of code that doesn’t fit nicely into a blog entry, and therefore becomes more difficult to read.)
The syntax is neat, but it’s not the best part; the really cool stuff is happening under the hood. The actual database I/O layer is now handling all of the “filter” logic, and it’s doing it faster than a regular READS loop can handle. In fact, during my tests, a filtered return of around 18,375 records showed a performance benefit that ranged from 11 to 21 percent. Now, that’s a small data set and we’re only talking about milliseconds, but it demonstrates a performance boost nevertheless – and that’s for a local application, running against a local database. The savings over a network connection to a remote database (i.e., xfServer) is likely to be enormous, as the I/O layer on the server is now doing the filtering, rather than returning the data to the client to handle.
Other features include the OrderBy class, which (as expected) sorts the returned data in either ascending or descending order based on the key being read. The classes also provide for a sparse record population, in which only the fields needed by the application are actually returned. There are even methods available to get at each individual record returned in the set, write back to the file, etc.
The fact that an update to Synergy/DE 9.3 is all that’s required is impressive as well. There’s no need to perform any database conversions, or add additional services or products; the Select classes work right out of the box.
The Select classes represent a significant addition to the language, and I can imagine a time in the not-too-distant future when they become the primary Synergy database access mechanism. My hat’s off to the Synergex development team; it appears that they’ve hit this one out of the park.