Performance Considerations

Top  Previous  Next

This section describes how to produce optimized applications.

Define Only the Fields You Use

With the SQL Accelerator drivers your Table definitions in the Dictionary only need to define the fields that you actually use, so if on the SQL server you have a tabke defined with 50 columns and you only need to access data in 10 columns, then define a Table in the Dictionary with just those ten columns. This reduces your network traffic.

For another example, if your SQL table contains 200 columns but only three are needed for a particular program, retrieving only those three fields dramatically reduces the amount of data sent over the network. If each column contains 20 bytes, then three columns would require only 60 bytes to be transferred whereas all 200 columns requires a 4,000 byte transfer.

After you have imported the table definition into your Clarion Data Dictionary, delete the fields/columns you don't use.

Matching Clarion Keys to SQL Constraints and Indexes

The Clarion KEY serves to supply the appropriate ORDER BY clause for driver generated SELECT statements.

However, if the Clarion KEY does not match an SQL key or index, then the SQL server must build a temporary logical view every time you access the table using the unmatched KEY. This can be slow for large data sets.

The best way to guarantee the Clarion KEYs have a matching SQL constraint or index, is to import the table, view, or synonym definition into the Clarion Data Dictionary. See Import the Table Definitions.

 

Most, but not all, SQL servers sort case insensitively. So, for example, when you issue the following SELECT statement:

 

SELECT Name FROM table ORDER BY Name

 

You will get back:

 

Alan

arthur

Brian

chris

James

 

And not:

 

arthur

chris

Alan

Brian

James

 

If a key does not have the Case Sensitive option checked in the dictionary editor, then the SQL Accelerator is forced to issue a SELECT statement with ORDER BY UPPER(key component) and WHERE clauses including calls to the UPPER function. In most cases this is not necessary and may impact on performance, depending on the sophistication of your server. A more advanced server will notice the unnecessary calls to UPPER and ignore them. A less advanced server will not notice them and probably do a full table scan to process the SELECT statement.

 

Filter (Contracting) Locators

Using Filter Locators on your BrowseBox controls rather than Incremental or Step Locators can reduce the volume of data sent between client and server. See BrowseBox Control Template for more information on Filter Locators.

Approximate Record Count

By default, the Clarion templates generate code to count the total number of records to be processed for a report. This total record count allows for an accurate progress bar display during report generation. However, for large tables, the resulting SELECT COUNT(*) can be very slow.

Therefore, for large reports, we recommend providing an approximate record count to suppress the SELECT COUNT(*) as follows:

1.

In the Application Tree dialog, RIGHT-CLICK the (Report) procedure, then choose Properties from the popup menu. This opens the Procedure Properties dialog.

2.

Press the Report Properties button to open the Report Properties dialog.

3.

In the Approx. Record Count field, type an approximate record count for the report, such as 5000.

4.

Press the OK button to close the Report Properties dialog.

5.

Press the OK button again to return to the Application Tree dialog.

6.

Press the button to save your work.

Fixed Thumbs and Movable Thumbs

By default, Clarion's code generation Wizards use Fixed Thumbs when Browsing SQL tables because Movable Thumbs can cause major performance slow downs on large tables in Clarion / SQL applications. For this reason, we recommend that you specify Fixed Thumbs for your manually place BrowseBox controls as follows:

1.

In the Application Tree dialog, RIGHT-CLICK the Browse procedure, then choose Extensions from the popup menu. This opens the Extension and Control Templates dialog.

2.

In the list box, select Browse on ..., then press the Scroll Bar Behavior button. This opens the Scroll Bar Behavior dialog.

3.

In the Scroll Bar Type drop-down list, select Fixed Thumb, then press the OK button.

4.

Press the OK button again to return to the Application Tree dialog.

5.

Press the Save button to save your work.

SQL Batch Transaction Processing

Most SQL databases operate in auto-commit mode. This means that any operation that updates a table (ADD, PUT, or DELETE) executes an implicit COMMIT. This can be very slow for a series (batch) of updates.

To optimize batch processes, surround any batch processing in a transaction frame (that is, with LOGOUT and COMMIT). The LOGOUT command prevents any subsequent implicit COMMITs until the transaction frame ends with either a COMMIT or a ROLLBACK. For example:

 LOGOUT(.1,OrderDetail)              !Begin Transaction

  DO ErrHandler                      !always check for errors

 LOOP X# = 1 TO RECORDS(DetailQue)   !Process stored detail records

  GET(DetailQue,X#)                  !Get one from the QUEUE

   DO ErrHandler                     !check for errors

  Det:Record = DetailQue             !Assign to record buffer

  ADD(OrderDetail)                   !and add it to the file

   DO ErrHandler                     !check for errors

 END

 COMMIT                              !Terminate good transaction

 

ErrHandler ROUTINE                   !Error routine

 IF NOT ERRORCODE() THEN EXIT.       !Bail out if no error

 ROLLBACK                            !Rollback the bad transaction

 MESSAGE('Transaction Error - ' & ERROR())!Log the error

 RETURN                              !and get out

You may want to issue intermittent calls to COMMIT and LOGOUT to save data at regular intervals. See the Language Reference for more information.