The Select API enables you to perform SQL-like queries to retrieve and update Synergy DBMS data. By providing an optimized way to read and filter data, the Select API offers an opportunity to improve application performance, especially across a network.
The Select class object (Synergex.SynergyDE.Select.Select) is the foundation of the Select API. It requires a From class object, which defines where the data is coming from and what the data looks like. You can also optionally do the following:
-
Define the selection criteria to filter the data using a Where class object.
-
Group data by like values using a GroupBy (case-sensitive) or NoCaseGroupBy (case-insensitive) class object.
-
Define the order in which data will be presented using an OrderBy (case-sensitive) or NoCaseOrderByclass (case-insensitive) class object.
-
Optimize network traffic by returning only partial records (so you can select only the fields you actually want) using a Sparse class object.
The above class objects perform queries to retrieve data on individual Synergy DBMS files, but you can combine records from two or more files using the Join method in the Select class, along with the JoinSelect, RowEnumerator, Rows, and On class objects. (See Joining data from multiple sources for more information.)
|
Once created, the From, Where, NoCaseWhere, GroupBy, NoCaseGroupBy, On, OrderBy, NoCaseOrderBy, and Sparse objects must remain in contact with the same records specified in the From objects as well as fields referenced in the Where, NoCaseWhere, GroupBy, NoCaseGroupBy, On, OrderBy, NoCaseOrderBy, and Sparse objects that are contained in the records. In other words, you can’t create one of these objects and pass it to another routine unless that routine has direct access to the original record. If this is not the case, an InvalidOperationException or $ERR_INVOPER error (“Where operator requires at least one field reference”) will result when creating the Select object. On the other hand, all data references outside a From object’s record are copied during the object’s creation and are not required to remain in contact with the original data. |
You can also use the Select API to do the following:
-
Trap record locks by extending the Event class with your own override onLock method.
-
Delete a selected group of records without individually retrieving them first.
-
Make change tracking queries in conjunction with the Synergy Change Tracking API.
Joining data from multiple sources
You can combine data from multiple Synergy DBMS sources by using the Select Join feature. The Select Join feature identifies records from two or more files (aka tables in SQL) to join, providing functionality similar to SQL92’s high-speed inner joins and left outer joins.
|
The SQL terms “table,” “row,” and “column” are equivalent to the Synergy terms “file,” “record,” and “field,” respectively. In the Synergy/DE documentation, we use the latter terminology. |
For example, say you wanted a list of all customers in the customer file and whether each customer had any orders during a particular period, defined by start and end dates. Here’s how you could do that:
| 1. | Each source file must contain one or more matching fields for which a join can be made. The matching fields must be of the same type and size, unless both field and key type are numeric. For example, |
fromOrders = new From("orders.ism", orders)
fromOrderdetails = new From("orderdetails.ism", orderdetails)
fromCustomers = new From("customers.ism", customers)
fromProducts = new From("products.ism", products)
| 2. | Match up the Orders file with the Orderdetails file. For each order there are one or more details, but you want to see only the details that are covered in the Orders file. The Orders file and the Orderdetails file both contain the matching field OrderID. |
joinObj = new Select( fromOrders.InnerJoin( fromOrderdetails, & (On)(orders.OrderID == orderdetails.OrderID) ) ).Join()
| 3. | Here are some ways to link up customers and orders: |
- Match up the Customers and Products files. Again, for each order there is one customer and for each order detail there is one product. The Orders and Customers files both contain the matching field CustomerID, and both the Orderdetails and Products files contain the matching field ProductID. (Because the Select line is too long to fit on one line, it is split up into several lines and grouped similarly to how SQL typically breaks up lines.)
joinObj = new Select( fromOrders & .InnerJoin( fromOrderdetails, & (On)(orders.OrderID == orderdetails.OrderID)) & .InnerJoin( fromCustomers, & (On)(orders.CustomerID == customers.CustomerID)) & .InnerJoin( fromProducts, & (On)(orderdetails.ProductID == products.ProductID)))).Join()
- If you don’t want to keep From class object handles and want your files closed when iteration is complete, provide the From instantiation in-line to the Select.
foreach rows in new Select(new From("orders.ism", order)
& .InnerJoin(new From("orderdetails.ism", orderdetails),
& (On)(orders.OrderID == orderdetails.OrderID))
& .InnerJoin(new From("customers.ism", customers),
& (On)(orders.CustomerID == customers.CustomerID))
& .InnerJoin(new From("products.ism", products),
& (On)(orderdetails.ProductID == products.ProductID)))).Join()
- If you want to join customers in the Customers file with matching orders made in a particular time period (defined by start and end dates), use the From.InnerJoin() method and add an additional filter condition to the on parameter. This returns only the customers that had orders during the specified period. This method is usually more efficient than that in the next bullet.
foreach rows in new Select( fromCustomers & .InnerJoin( fromOrders, & (On)(customers.CustomerID == orders.CustomerID .AND. & (orders.OrderDate .GE. StartDate .AND. & orders.OrderDate .LE. EndDate)).Join()
- If you want to join customers in the Customers file with matching orders made in a certain period, use the From.InnerJoin() method, but instead of using the on parameter, use the where parameter. When you use this method, everything referenced in the where has to be in the first file listed (Orders, in our example). This method can be more efficient than the above bullet, depending on the optimization of where.
foreach rows in new Select( fromOrders & .InnerJoin( fromCustomers, & (On)(customers.CustomerID == orders.CustomerID)), & (Where)(orders.OrderDate .GE. StartDate .AND. & orders.OrderDate .LE. EndDate)).Join()
- If you want to join all customers in the Customers file and indicate which had orders during the time period, use the From.LeftJoin() method. This returns all customers, as opposed to the two bullets immediately above, where we returned a subset of customers.
foreach rows in new Select( fromCustomers & .LeftJoin( fromOrders, & (On)(customers.CustomerID == orders.CustomerID .AND. & (orders.OrderDate .GE. StartDate .AND. & orders.OrderDate .LE. EndDate)).Join()
- If you want to perform a right join, which Synergy DBL doesn’t support, you can swap the order of the From objects to effectively perform a right join using From.LeftJoin(). This is the opposite of the bullet immediately above: It will return list of all orders, indicating the customers associated with those that were during the specified period.
foreach rows in new Select( fromOrders & .LeftJoin( fromCustomers, & (On)(customers.CustomerID == orders.CustomerID .AND. & (orders.OrderDate .GE. StartDate .AND. & orders.OrderDate .LE. EndDate)).Join()
| 4. | Iterate over the Join class object to retrieve the record set that matches. On the completion of each iteration, the Orders record and Orderdetails record are embedded in the Rows object. Use the Rows.Fill() method to extract them to their appropriate data records. |
foreach rows in joinObj
begin
rows.fill(orders)
rows.fill(orderdetails)
.
.
.
end
|
|
If the first file specified on a join is remote on a server, you can only join other files on that same server. However, if the first file is local, you can join any local or remote files. |
Enabling Select debugging helps you determine how to optimize a Select statement by logging optimization details. You can turn on debugging for the Select class by setting the DBG_SELECT environment variable. You can use the DBG_SELECT_FILE environment variable to designate where the output will be written. The DBG_SELECT_INTERNAL environment variable locates Select statements that don’t optimize, or don’t optimize on the primary key, due to a type mismatch.
For additional information, see Optimization and ordering for ISAM files as well as the following articles on the Synergex Blog:
Prefetching records to improve performance with xfServer
To improve record selection performance when using xfServer, a prefetch feature is available that enables the client to prefetch (or buffer) selected records for files of any type that are open in input mode. Prefetching is off by default unless you’re using Join. To turn prefetching on, set the SCSPREFETCH environment variable to a value between 1 and 32, which specifies the size of the prefetch buffer in kilobytes. To turn prefetching off, set SCSPREFETCH to 0. You can enable or disable prefetching on a file-by-file basis by using the SETLOG routine to set or clear SCSPREFETCH before opening the channel specified in the From. The value of SCSPREFETCH is checked on every remote file open.
