| Home | Previous Lesson: RichText Presentation Style Next Lesson: Prompt for Criteria |
The SELECT statement specified in the DataWindow painter's data source is processed by the database. The WHERE clause filters the data brought to the PowerBuilder application. What if after bringing the data to the PowerBuilder application, users wants to filter the data they see on the screen? The traditional solution would be to send the modified SELECT statement to the database and get the new results to the DataWindow. When there are hundreds of users connected to the database, this puts a lot of load on the database. To shift the load towards the client (PowerBuilder application) side, specify filter criteria in PowerBuilder, by selecting Rows > Filter menu option when in design view.
For example, if trans has, say 100 rows, of which 40 rows are of type receipts and 60 rows are of type issues. The database sends all 100 rows to PowerBuilder for the following statement.
SELECT * FROM trans
The database sends only 60 rows to PowerBuilder for the following statement.
SELECT * FROM trans where tran_type = 'R'
However, if you issue the first SELECT statement and filter it in PowerBuilder, the database server sends all 100 rows and PowerBuilder filters out 60 rows and displays only 40 rows. What exactly does that mean? It is reducing load on server in terms of searching for eligible rows (all receipts), however, it is increasing load on the server since it has to read all the rows and send all the rows to the client. Since database is sending all the rows to the client, the network traffic is increased, which is not good. Basically, filtering on the client side is good only if the user wants to filter data from the result set. DataWindow functions FILTER() when called in the script displays the filter expression dialog box at run-time.
Similarly, the whole sorting load can be moved away from the database, by selecting Rows > Sort menu option and specifying the sort criteria. If the sort order is specified using the ORDER BY clause in the SELECT statement, the database server sorts the results before sending them to the PowerBuilder application. To move the entire sorting load to PowerBuilder, do not specify the ORDER BY clause, instead move the criteria to the Rows > Sort dialog box.
Let's take the same example of trans tables with 100 rows, of which it has 40 receipt rows and 60 issue rows. If you issue:
SELECT * FROM trans WHERE tran_type = 'R'
ORDER BY tran_date, tran_no
SELECT * FROM trans WHERE tran_type = 'R'
The database server sends the same number of rows to PowerBuilder in both the cases. So, there is no increase or decrease of the network traffic. However, in the first case, the database server sorts the results before sending it to the client and in the second case, it doesn't sort the results. Which one to choose? Good question. It depends on the type of client your company wants to implement, a fat client or a thin client. For fat client, use the second statement and use the first statement for thin client implementation.
In short, anything you specify in the SELECT statement is processed by the connected database. To move the load onto the client, specify in the PowerBuilder design view.
| Home | Previous Lesson: RichText Presentation Style Next Lesson: Prompt for Criteria |