Suppose you have worked hard to make an application Web-ready using PowerServer, and, using your test data, it seemed to perform acceptably. Then, when your users provide "live" test data in realistic volumes, you discover that the application takes a long time to load, and worse, a long time to respond to your user's input. What to do?
Well, first you should confirm that your issue is not caused by excessive server calls (see Tuning: Excessive Server Calls). The reason is that majority of the time, PowerBuilder applications are coded such that as additional rows of data are retrieved and logic is executed to validate, manipulate, or handle the data, which can result in server calls. As such, the more rows of data are retrieved the more server calls are made.
Once you are certain the slow-down is not caused by excessive server calls then you can consider reducing the size of data transmission. At a high-level there are several techniques you can employ:
-
The first and most popular is staging the data retrieval into manageable increments. For example, you can expose a Next button, and have the application respond to this button click by getting the next logical segment of the result set just like typical Websites or Web applications. Technique #1: retrieving data incrementally gives you instructions on how to achieve this.
-
Another technique is to create multiple smaller "specific" views rather than one larger "general" view. Consider adding SQL WHERE clauses based on more search criteria, thus retrieving only the amount of data that is absolutely necessary for a particular view of interest.
-
If you have a choice between reducing the number of rows retrieved, and reducing the number of columns, note that a small reduction in columns (described below in Technique #2: minimizing excessive number of columns) can improve performance to an even greater extent than a reduction in rows. This is because most of the time, loops, whether in the application code or in the virtual machine, visit columns first and then rows.
Anything you do to reduce the size of the result set in one way or another can not only improve performance but also improve usability of your application as well.
