Solution: DSP
Versions: 6.5+
Have you ever been tasked with fixing a DSP® page that was slow to load? The Page Performance Monitor is a utility included with the DSP intended for technical users (those who are responsible for debugging SQL performance issues). Use it on custom pages to troubleshoot performance issues and to determine which portions of the page run slowly when the record set (or user set) increases dramatically.
The page designer must know the anticipated largest reasonable record set. Some pages can be complex, and may affect a few records, while others may display or process tens of thousands.
Performance issues include any of the following actions taking several seconds:
- Loading the page
- Adding current page to the Quick Links
- Sorting the page
- Filtering the page
Additionally, a page performance issue exists if you click the Edit, or pencil icon, and there is lag time of several seconds before the record is available for editing.
Access the Page Performance Monitor by pressing [Ctrl]+[Shift] +K.
These results from the Page Performance Monitor show that the 'dgResponse' portion has a long and variable duration, while the 'RuntimeUtils' portion has an average and consistent duration.
The 'RuntimeUtils' section correlates with the number of columns displayed on the Horizontal View of a page. To address this issue, move columns to the Vertical View. Any searchable fields must remain on the Horizontal View.
The 'dgResponse' section shows the performance of the page's underlying '%hor' view. Tune horizontal views to execute more quickly by, for example, replacing underlying views with tables or adding indexes to joins in underlying tables.
The Page Performance Monitor also includes these features.
SQL Executed
Click the SQL Executed icon to view the code executed when the page loaded.
Auto Focus
When this checkbox is checked, the Monitor always displays results for the last page you loaded.
When this checkbox is unchecked, the Monitor continues to display the current results, even if no results are displaying. The list on the right side of the Monitor continues to populate.
Auto Collect Data
When this checkbox is checked, every time you load a page, a set of results are stored in the list on the right side of the Monitor.
When this checkbox is unchecked, no results are added to the list.
Tips to Increase Page Performance
Indexes and data type minimization can both lead to a high performing table even with millions of records. Everything in a custom application is built on these tables, so if this foundation is not optimized for performance, the result can be excessive technical debt in the future.
Data Types for Tables
Minimizing data types to their smallest relevant size can improve performance and reduce database bloat. One example of appropriate size limiting is within the System Administration WebApp in the DSP®. Fields that contain SQL view names will only ever contain view names directly from SQL Server. Since SQL Server object names are limited to 128 characters, these fields can and should be limited to 128 characters.
Character data types are important for application behavior as well. Most times, nvarchar is preferred over varchar due to the Unicode support of nvarchar. Names and descriptive fields should be nvarchar to ensure they support the widest range of character sets.
Column Collation for Tables
Column collation allows for overriding the database's default collation with a custom one, which can impact behaviors like case-sensitive comparisons. This can be considered during application design to determine if a field’s case sensitivity is relevant at a business logic level.
Improve Page Load Performance Related to List Boxes
List boxes can often be a source of performance degradation due to their elaborate configurations and lengthy lookup times that may have to occur at a cell level. Even if disabled, on the loading of the page, the cell value must be looked up to display to the user. If several list boxes are located on the Horizontal View, page load time can be severely inflated.
An easy solution is to have a read-only view-level resolution of the list source (assuming it is a view in the same database) on the Horizontal View, and an editable list box on the Vertical View. This keeps the description field visible on the Horizontal View of the page, making it filterable and searchable, while greatly increasing performance.
Alternatively, the list box on the Horizontal View can itself be simplified when it is disabled. There is no need to include a complex WHERE clause that may be present in the list box configuration, if it is only meant to resolve values and not restrict user input or security. Even this simple change can improve page load.
BackOffice recommends using a combo box when the list view has over 100 values. Combo boxes improve page performance. A list box renders on the page when you click the arrow; a combo box displays the values based on what you start typing in. You can also apply the list filter to a combo box, which allows a user to search for a value.
Comments
3 comments