Data Volumes

This article explains how data volumes can influence performance and user experience. Apart from technical Firebird Database settings, Configuration settings within Ostendo can also affect performance. As sites run Ostendo and Freeway over time, data volumes should be managed. Whilst initially a site may have not experienced performance issues in the early days of an implementation, as time goes on, their data volumes should be monitored and configuration settings potentially amended to suit their environment.

• Symptom:
• List Grids Slow to load (Orders, Payments, Batch Invoicing): Large volumes of Closed Orders, Fully Applied Payments and Fully Paid Invoices can affect the speed Ostendo takes to load the Grid when Grid options Filtering & Sorting and "Included Closed", etc.. are selected by the user on a List Grid. It should be noted, when Filtering and Sorting is turned on, Ostendo has to load 'all' relevant records into memory (not just one screen full) before it can display the Grid.
• Slow search results from a List Grid
• Recommendation:
• If a site experiences this, we suggest you consider turning on Dynamic Archive Days (File->System Settings->Environment Settings Tab). This parameter (No of days) filters out Closed Orders, Fully Applied Payments and Fully Paid Invoices older than 'today' less the Dynamic Archive Days setting. The number of days you specify may well be different from site to site. There is no 'one fits all approach'. Typically though most don't need to refer to closed transactions older than 2 years. You should note, this doesn't actually remove data, but filters it out for everyday operations. Data is still available for reporting purposes.
• Consider each Users Grid Option settings:
• Save List Filter Conditions and Auto Activate Filtering & Sorting: These settings are retained when a user turns on Filtering and Sorting. If these features are NOT selected the screen is not filtered and the response will be quicker. These user settings are found under (File->System Configuration->User Security & Options->Options Tab-> Grid Options)
• Consider amending the status of records, which can influence the load speed of a Grid. eg: You may have 5000 items, however only a small number are ever used. Consider marking un-necessary items as Obsolete. Same goes for Customers, Suppliers, Descriptors etc being marked as InActive if no longer in use.
• Review each user Grid Customisations. Do they really need to show all the fields they have set?. Remember, the more data to be shown the slower the screen will take to load into memory.
• Check whether Advanced searching is turned on (File->System Configuration->System Settings->Environment Settings tab). This form of searching allows a deeper search of data. This is extremely useful in some environments however can cause the searching to slow down and produce numerous search results. Consider turning this off only if it is not required by the site.
• Symptom:
• Slow Screen Load when launching an Ostendo option
• Recommendation:
• Check if Activate Screen Logging is turned on (File->System Configuration->System Settings-Environment Settings tab). This function is useful to determine what screens / reports etc.. each user launches. If this function is turned on, each time they access a screen, report, workflow etc, a record is written to the SCREENLOGGING table. This information can be helpful when a site is being implemented, however can cause an overhead on the system if left on. It is suggested that this table is purged of unnecessary records periodically if this feature is in use. A simple SQL statement can be used for this.
• Check if Audit Logging is turned on (File->System Configuration->Audit Logging). If this is in use, each time a field value is potentially Inserted, Updated or Deleted, Ostendo will write a record to the AUDITLOGGING table. If this is in use, ensure that it is still required and consider purging unnecessary records periodically.
• Check if Notification Logging is turned on (File->System Configuration->Notification Logging). If this is in use, each time a field value is potentially Inserted, Updated or Deleted, Ostendo will write a record to the NOTIFICATIONLOGGING table. If this is in use, ensure that it is still required and consider purging unnecessary records periodically.
• Symptom:
• General performance and occasional Deadlock Conflicts may occur
• Recommendation:
• When a financial transaction is written to the Financial tables in Ostendo, Journal records are also written to the Journal tables. These journals are used to post to modern Accounting Software (eg: Xero, MYOB Live etc..) Journal record volumes can quickly become huge, and affect performance and also contribute to Deadlock Conflicts on some occasions. Therefore we recommend that regular Archiving Journal Data be periodically performed. This download link has all the necessary information to set this up and maintain these tables. http://www.ostendo.info/downloads/ostendo/JournalArchiving.zip
• If the general performance of a screen becomes an issue, we recommend you investigate any Custom Scripts: (specifically, Screen Data Scripts). These can affect performance or even generate database messages if badly written. (File->Scripting Configuration->Screen Data Scripts)
• Custom Triggers or Procedures within a Database can also dramatically affect performance and behaviour of a database. These are not so easy to find but can generally be found by connecting a copy of the database to a third party utility eg: IB Expert. If you are unsure of this, it is suggested you contact a Developer Partner for assistance to determine if any custom Triggers or Store Procedure exist in the database.
• Symptom:
• Slow generation of the Inventory Replenishment routine: This routine is a complex routine in itself and therefore must determine data from multiple sources within the database. Therefore it can genuinely take sometime to generate, especially, if you are including the 'Multi-Level' Explosion' option.
• Recommendation:
• Unless explicitly required, we recommend the Replenishment Horizon be set to 'Weekly' rather than 'Daily'. This achieves the same results, however the Projected requirements are summarised in Weeks rather than by Day. Because of this there is less data involved when generating the Replenishment. (Requirements->Settings-> Requirement Rules)
• Consider reducing the Replenishment Horizon, as this will also influence the volumes of Projected Requirements records for each item. (Of course in some environments, this may not be practical). 
• Symptom:
• Assignment Board slow to load: The Assignment Board is loaded based upon the Assignment Profile. (Requirements->Assignment Profiles). That profile allows you to control the following:
• Recommendation
• These settings can potentially affect performance: 
• Horizon - Days Back (recommended no more than 30 days)
• Horizon - Days Forward (recommended no more than 30 days)
• Auto Refresh Rate (set this as high as practical)
• Check for Warning on Load (consider leaving this turned off and allowing the user to manually run this from the Assignment Board if / when required)
• Conditioning of Data (eg: Include / Exclude Departments / Employees etc..) Consider limiting the volumes of Employees or Departments etc.. maybe create one Assignment Board Profile for each Department.
• Symptom:
• Slow launch of an Inquiry, Pivot, Analysis View or running a report: Check the Queries specified in the Report & View Developer for the Inquiry, Report etc.. concerned.
• Recommendation
• Ensure each query is only calling the fields it needs to call. Typically when people write queries for reports etc, they select all fields, and end up only using a few. The less fields to be returned in a query will always be the most efficient.

• Symptom:
• Freeway slow to synchronise: Primarily the speed of this is controlled either by the Internet connection or by the volume of data.
• Recommendation
• The quality of internet connection is out of our control, however there are things you maybe able to do to ensure only the relevant data is sent to Freeway. These include reviewing the following:
• If a Company Logo has been specified against the System Settings, that logo will always be sent to the device (even if it is not used on any Freeway Reports). Ensure this logo is no more that 25 Kb in size (Optimal Size is 25Kb however if the image is greater than 100kb the image will be excluded from Freeway)
• Employee Mobility Settings:
• Ensure only relevant Selections are set for each Employee (eg: Jobs, Customer, Suppliers etc..)
• Avoid using a selection range 'All Open 'Jobs', 'Assemblies' or 'Deliveries' unless there are small data volumes or this is the most practical manner of deploying work to Freeway (ie: rather than using the Assignment Board). Think of how many records maybe returned if you specify this without a Restriction Condition.
• Make use of Restriction Conditions to limit the volumes of records being sent to the device.
• Consider how many Items and Descriptors are in the sites database. We recommend using either a Restricted Materials List, or an Items Restriction Condition if possible. NB: Obsolete Items and InActive Descriptors are never sent to Freeway by default.
• Check Job, Assemblies and Delivery Horizon days settings are not set too far forward for the employee
• Check Mobility Rules setting Assigned Transfer Horizon Days is not set too far forward for the site.
• Check The number of days forward to display ToDo activities setting is relevant to the employee
• Check Job, Assemblies and Delivery Include Non-Finished Assignment a number of days back setting is relevant to the employee
• If using Stock Counts in Freeway, ensure the following Mobility rules are not set too high. (Blank values  = 50. A maximum of 200 is allowed however in some environments this should be left as 50 or blank)
• No. of Remaining Count Lines to Display
• No. of Counted Count Lines to Display
• Backgrounds & Buttons: Ensure no images are loaded against the Employee that are greater than 25Kb in size. (Optimal Size is 25Kb however if the image is greater than 100kb the image will be excluded from Freeway)
• If Mobility Images (Descriptor or Item) are used, ensure no image is greater than 25Kb in size. (Optimal Size is 25Kb however if the image is greater than 100kb the image will be excluded from Freeway)

As you can see there are numerous settings within Ostendo that can affect performance. Our recommendation would be to regularly review these for each site to ensure current configuration settings are still applicable to that site and their data volumes.