Specifics of working with virtual table of calculation register schedule data

A virtual table CalculationRegister.<RegisterName>.ScheduleData is defined for calculation registers that support an action period. A calculation register of this type should be linked to a nonperiodic information register that will provide schedule data.

To simplify getting schedule data, the system has a virtual table defined that includes the following virtual fields in addition to other fields:

  • <Name of schedule resource>BasePeriod
  • <Name of schedule resource>ActionPeriod
  • <Name of schedule resource>RegistrationPeriod
  • <Name of schedule resource>ActualActionPeriod

This means that for each numeric resource of the information register assigned as a schedule for a calculation register, you can get its sum for register rows, taking into account the base period of the calculation register row, action period, registration period, and actual action period.

Upon generation of a virtual table of schedule data, the tables of the calculation register and information register are joined and when the field <Name of schedule resource>ActualActionPeriod is retrieved, the third joined table is the table of the actual action period of the calculation register. Since the data for all the four listed fields are received by joining with the information register table based on various conditions, it means that up to four joins with the information register table are executed.

Upon getting virtual tables, the system attempts to act in a sensible way, i.e., it only performs as many joins as are needed to get the virtual table fields listed in the "SELECT" section.

This ensures that when you write query code, you do not need to select virtual table fields "just in case". This recommendation may sound too general, but remember that getting an unnecessary field in a query to actual tables or other virtual tables will generally increase network data traffic and result in a slight decrease in performance. It is obvious that getting five register fields is hardly any different from getting another, the sixth field unless we are referring to large fields. But when you select from a virtual table of the schedule data, you should note that adding one more virtual field from the list changes the situation dramatically. Query execution times may vary cardinally. So the recommendation of coding queries in an optimum fashion with a careful selection of the number of selected fields is particularly valid for this virtual table.

Also, note that the performance of a virtual table query is impacted significantly by the number of virtual field types rather than the number of fields.

Let us consider an example. The information register assigned as a schedule has the resources NumberOfDays and NumberOfHours. Let us review the following three queries.

1.

SELECT 
    NumberOfDaysRegistrationPeriod
FROM 
    CalculationRegister.Payroll.ScheduleData(Company = &Company)

2.

SELECT 
    NumberOfDaysRegistrationPeriod,	
    NumberOfHoursRegistrationPeriod
FROM 
    CalculationRegister.Payroll.ScheduleData(Company = &Company)

3.

SELECT 
    NumberOfDaysRegistrationPeriod,	
    NumberOfHoursActionPeriod
FROM 
    CalculationRegister.Payroll.ScheduleData(Company = &Company)

When you compare execution times, you will notice that the second query execution time is almost the same as that of the first query, despite the fact that a new virtual field is added. But the execution of the third query takes notably more time (possibly a few times more) than the second one, even though the number of fields selected is the same.

This is due to the fact that in order to get the fields NumberOfDaysRegistrationPeriod and NumberOfHoursRegistrationPeriod, a single join to the information register by the value in the RegistrationPeriod field (irrespective of the number of register resources to be summed) is executed. And when you also get the field NumberOfHoursActionPeriod, yet another join to the information register is established by the value of the ActionPeriod.

Next page: Using custom expressions in query language totals

Be the first to know tips & tricks on business application development!

A confirmation e-mail has been sent to the e-mail address you provided .

Click the link in the e-mail to confirm and activate the subscription.