phpLens manual: Performance Tuning
Table of Contents
Performance Tuning phpLens
The keys to good performance are:
- Tune your SQL statement by
(a) selecting only columns that are required
(b) index all important columns in your where clause
- 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.
- 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
- 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.
- 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
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.
- 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.
- Instead of using templates, save editing and new record screens as html
pages. Then set the phpLens object property securityEditSeq = false.
(Enterprise users only)
- Generate PHP code, then set dynEdit=0. This disables dynamic editing,
which means one less SQL query to perform.
- 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.
- Use a cache that supports Zend encoded files such as the Zend Cache.
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.
- 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.
- 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.
- 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.
- 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(...);
Source code for this function is available in phplens-common.inc.php.
Field Naming in the Lens
If you have multiple tables with the same field names, when you perform joins, it is possible that phplens will
get confused as to which field is owned by which table. This is because of limitations of the database APIs.
For best results, avoiding further heartache later, try to give your fields unique names.
For example, if you have a table called ACCOUNTS, prefix all field names with ACC_.
This documentation system is maintained using phpLens