Improving perfomance of PHPRunner/ASPRunnerPro applications

My application is slow. What do I do?

Usually “My application is slow” means one or more list pages take a long time to load. Here are the things you can check in order to improve performance.

Is this issue server specific?

Test your application on at least two different servers i.e. on your local machine and on the remote web server. If you do not have a local web server upload your application to Demo Account to compare the performance.

Just in case test your remote application from two different machines i.e. from work and from home. This will sort out any issues related to Internet connectivity.

Troubleshooting your application

If application works slow everywhere you need to find what exactly causes the trouble.

1. Turn off “Loading …” box option on Miscellaneous page. While this won’t improve your pages load speed it improves user experience allowing to see the data before the page is fully loaded. The trade-off is that some features like search panel or inline edit may not work until the page is fully loaded.

2. Reduce the number of records displayed per page. Display as many records as you can see on a single screen without scrolling. It improves user experience as well. If the users need to find a specific record they can use the search or loop through pages.

If you absolutely have to display a large number of records on a single page make sure you turn off “Loading …” box.

3. Reduce the number of fields visible on the list page. The golden rule is to fit all data on the screen without having to scroll either horizontally or vertically. You can display more detailed information for each record on the View page.

4. Use features like “Show Add/Edit/View pages in popup” and “AJAX search, pagination and sorting”. This makes your application more responsive and overall faster.

5. If you use search panel with lookup wizard controls this may slow down your application as well, especially if lookup tables are huge. The solution is to remove those fields from the search panel or use Lookup wizard type ‘Edit box with AJAX popup’. In this case no data will be pre-fetched on initial page load.

6. Turn on ‘Case-sensitive search’ option under Choose fields->Search settings. By default PHPRunner search is case-insensitive ( WHERE upper(LastName)=upper(‘Newton’) ) which is slower though more convenient for end users. Turning this feature on results in faster searches.

How to measure load speed

The best approach is to use Firefox with Firebug addon. Open Firebug, proceed to Net tab and reload your page. Firebug shows you what files are loaded, in which order, as well as each file size and load time.

PHP page (cars_list.php) is loaded first. Browser parses HTML code and loads images, Javascript and CSS files. On the screenshot above you can see that cars_list.php takes 546 milliseconds to load. 375 milliseconds (Waiting) is the server processing time while 171 milliseconds is the transfer time from the server to your computer. As a rule of thumb – if server processing time is under one second there is nothing to worry about.

Another thing to keep eye on is the page size. If the size of the page is a few megabytes it will load quite slow. Also, make sure you are not using a search page with large lookup tables.

Lets consider another example.

You can see that server processing time is 36 seconds which is quite slow. Lets see what causes this.

As a first step you need to find the SQL query PHPRunner executes. Slow SQL query execution is the number one reason of bad performance.

SQL Query

To print the SQL Query on the web page change $dDebug to True in the include/appsettings.php file. Reload the list page to see the SQL Queries at the top of the page. In our case we are going to see something like this:

SELECT count(*) FROM votersall WHERE (1=1) and Residence_City='Zolfo Springs'

SELECT County_Code, ... FROM votersall WHERE (1=1) AND Residence_City='Zolfo Springs' ORDER BY Residence_City DESC LIMIT 0,10

On the list page PHPRunner executes two queries. First query calculates the number of records that matches the current filter. Second one retrieves the data. Note limit 0,10 clause at the end. No matter how large your database is PHPRunner only retrieves as many records as required.

If we execute those queries manually using phpMyAdmin or any other database tool we find that they in fact run slow (no wonder – our test database contains 12 million records). To speed up these queries we need to add indexes. Indexes are helping databases sort and find data faster. In this example we run a search for Residence_City field. Once we add an index on Residence_City field, our page takes less than a second to load.

Here is how you can create an index (MySQL specific):

CREATE INDEX Residence_City ON votersall (Residence_City(20));

More info: Creating indexes in MySQL

Note: make sure to turn on ‘Case-sensitive search’ option when you use indexes.

What else I can do to increase the load speed?

Make sure your web host supports HTTP compression. HTTP Compression is a publicy defined way to compress content (mostly textual)      transferred from Web servers across the world wide Web to browsers. The impact of compression is that the number of transmitted bytes is reduced and thus a higher performance is gained.

More info on HTTP compression

ASP specific note

Thread pool model: MTA vs STA

On some web servers all ASP pages generated by ASPRunnerPro load extremely slow.

We found that switching the thread model on the application pool from MTA to STA resolved
this problem. You will need to contact your web hosting provider in order to make this change.

Turn off IIS output caching

On some web servers output caching slows ASP application performance significantly. Try to turn output caching off to see if it fixes the issue.