| **Navigation:**  SQL Drivers > [[optimizing set processing using the where driver string.htm|Optimizing using the WHERE driver string]] >====== Performance Considerations ====== | [[optimizing set processing using the where driver string.htm|{{btn_prev_n.gif|Previous page}}]][[optimizing set processing using the where driver string.htm|{{btn_home_n.gif|Return to chapter overview}}]][[using embedded sql.htm|{{btn_next_n.gif|Next page}}]] | | || 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 {{savebutton.jpg|savebutton.jpg}} 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 {{apptb4.jpg|AppTb4.jpg}} 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.