Table of Contents

Performance Tuning phpLens

1. Tune your SQL statement by
   (a) selecting only columns that are required
   (b) index all important columns in your where clause

2. Don't select and view fields with large amounts of data, such as binary images or large text fields. View these fields on a special page, not in the main browse page.

3. Try to reduce your usage of powerLens, sumLens, colorLens, rowColorLens and lookupLens properties. They are powerful, but there is a performance hit. To reduce lookupLens, use joins instead of lookups in grid views, and use searchLookupLens when editing or creating records [more info].

4. If your database server is overloaded, define a cache directory (eg. set $ADODB_CACHE_DIR='/tmp') and try setting cacheLookups=300. This will cache SQL lookups for 300 seconds. Only available on Advanced and Enterprise versions of phpLens.

5. Since phpLens 3.3.0, we have a phplens tuner that will check your lookupLens for poor behaviour. For example, if you are displaying only 1 row, it is a bad idea to use a lookupLens to map a product code to a product name if the lookup has to pull 10000 product names from the server. Eg. your $lens->sql could look like this:

     select prodcode, ordqty from orders
   

   and your poor performing lookup (which pulls 10000 rows) is:

     select prodcode,prodname from products
   

   Then it is better to remove the lookupLens and change your $lens->sql to use a join:

     select prodname, ordqty from orders, products 
       where orders.prodcode=products.prodcode
   

   The tuner is activated when you define the following variable before the applet is called:

     $PHPLENS_TUNER = array(100,array('products', 'customers'));
   

   The first element of the array (100 here), is the maximum number of rows returned by a lookupLens before a warning is displayed. So a lookup on a table with 10 rows will not initiate a warning. The second element of the array is an array listing all tables that should not be included in lookupLens - this is useful when you have a small test database that you know will grow very big, so even though products and customers have less than 100 records each, you want a warning to appear.

6. Consider using persistent connections if your database is able to handle the higher number of client connections. You can enable them by setting $lens->persist = true (phpLens uses this setting to determine which method to use to make a 2nd connection to the database to read the phplens table), and also use PHPLensPConnect( ) instead of PHPLensConnect( ) when creating phpLens objects. By default $lens->persist is set to false.

   .

7. Instead of using templates, save editing and new record screens as html pages. Then set the phpLens object property securityEditSeq = false. (Enterprise users only)

8. Generate PHP code, then set dynEdit=0. This disables dynamic editing, which means one less SQL query to perform.

9. Move all files in the img directory to another server to reduce the load on the current phpLens server. You will need to change the imageDir property to match the URL of the directory where the images are residing.

10. Use a cache that supports Zend encoded files such as the Zend Cache.
    
    

11. Editing multiple records in a grid at one time is a very powerful feature in phpLens 2.0, but make sure that the user is actually editing multiple records, and not one record at a time. This is because there is a lot of extra checking and html generated (particularly for popup lists) when this feature is enabled.

12. PhpLens 2.0 allows you to define whether a lookupLens is for editing or not. Some lookups are not used when viewing the grid. You can define a lookup for editing using:

      $lens->lookupLens = "COLUMN^select name from table^!VIEW";
      

    This means display a popup when editing or creating records, but there is no need to generate lookups when in the VIEW or FILTERVIEW states.

13. In phpLens 2.0, you can define separate sql statements for the grid, filter/search form, new and edit forms, and another one for the detail view. The main overhead of phpLens is viewing the grid. If the number of grid fields is reduced by simplifying the sql, the query will run faster.

    So if you have complex new or edit forms, but you only want display to a simple grid view, then the grid view sql can be set with: $lens->sql = '...' while the new/edit form sql which accesses more fields is defined with $lens->sqlEditNew = '...'. Similarly, we can define the detail view sql with $lens->sqlDetail = '...', and the filter/search view with $lens->sqlFilter = '...'.

    The properties sqlEditNew and sqlDetail require the keyCol property (primary key) to be defined. You do not need to put the primary key in the where clause of the sql, as the sql will be dynamically modified to access the correct record. Also dynamic editing in EDIT and NEW state is disabled when sqlEditNew is on.

14. When scrolling to the end of the grid is slow, consider setting scrollLinks > 0. This is because setting scrollLinks will force phpLens to perform a count of the recordset, so it has a better idea of what record to scroll to reach the end of the grid.

15. In phpLens 3.0, we have a new function that you can call to remove most of the <font> tags used in the grid. PHPLensLightFonts will use CSS instead of <font> tags to display the fonts. This will reduce the HTML generated by at least 10%.

    	$lens = new PHPLens(...);
    	PHPLensLightFonts($lens);
    	$lens->Render();
    	

    Source code for this function is available in phplens-common.inc.php.