Causes of slow page navigation?

[kingcarol]

Just wondering if anyone has any 'off the top of the head' tips on what can cause slow navigation between pages of data on a grid? Are there any particular Alpha widgits that cause slowdown? Several of my grids move slowly on page navigation, so I created a quick one for a grid that brings in tens of thousands of records and didn't add anything to it and it was very quickly going from page to page. I'm going to start adding things to it to see what might slow it down, but before I spend the time, I thought I'd throw this question out there. Thanks!

[kingcarol]

Re: Causes of slow page navigation?

P.S. One thing I wondered about is that a lot of my slow grids began their development in 10.5 and I often copy them for new grids I need. Could it be that grids created from scratch in V11 have better performance?

[Runback]

Re: Causes of slow page navigation?

I've also noticed that the navigation's gotten slower with v11. I don't know why. Your theory sounds interesting. This is a very important subject. - Hasn't anyone else noticed anything?

[kingcarol]

Re: Causes of slow page navigation?

Rune, I guess nobody has much to say on this. I'm really wrestling with slowness on page navigation (moving from page to page) and am trying to figure out whether there is some callback or requerying happening upon page navigation that is slowing things down.

[kingcarol]

Re: Causes of slow page navigation?

Not that this has solved my problem, but here's a great tip from Selwyn for taking a look at internal timing:

go to the server side events - ongridexecute event - and add this code
e.tmpl.timer = .t.

then run your grid.
it will show internal timing information.

[Selwyn Rabins]

Re: Causes of slow page navigation?

Carol


i spent some time analyzing your grid.

the bottom line: the problem is Access (which your are using as your database), as the videos explain - and NOT Alpha Five.
Access is just not a high performance SQL database.

if you were running on a good server and using a proper database you would have dramatically better results.

[kingcarol]

Re: Causes of slow page navigation?

Selwyn, I very much appreciate your time to analyze. It's good to know that Access is causing the slow down. Trouble is, I'll be stuck with Access for quite awhile due to several hundred customers that are currently using CHS via my front end Access application. I have to have the Alpha app access their back end data in Access until I have completely migrated to Alpha, which could take a couple more years, I'm afraid.

[Richard Rabins]

Re: Causes of slow page navigation?

A general comment to anyone reading this grid.

If you have an Access app that you want to migrate to the web with Alpha Five -- we would recommend highly that you first move your Access database over to a SQL database. As you probably know, there is a very good "upsize to SQL" wizard already in Access. I believe Selwyn will be commenting further on this but he indicated to me that when he was analyzing Carol's app, he moved the data to MySQL and the performance improvements were an order of magnitude greater.

Carol - on the business front, If you want to get your whole app migrated to Alpha and the web sooner rather than later, to be in a better position competitively, I have some ideas re how we can help you. If you are interested please let me know.

Thanks

[Selwyn Rabins]

Re: Causes of slow page navigation?

Here is some further informatation on why Carol's Grid was slow when navigating from page to page.

1. First, we turned on a flag in the Grid to show timing information.
In the Server side events, onGridExecute() event, we added this line

e.tmpl.timer = .t.

With this flag turned on, the Grid displays timing information for how long various parts of the Grid execution are taking.

This showed that that the initial query to get the result set for records to show in the Grid was taking 1.5 seconds. This is extremely slow.
This time has nothing to do with Alpha Five. It is because the Grid was based on a View (Query in Access terminology) that joined several tables and Access is just extremely slow in executing this query.

Next we noticed that the time taken to render the grid (the process of merging the data in the row HTML template in the Grid) was taking about 6 seconds. This too is extremely slow.

We noticed that the Grid used the Group break feature (introduced in V11) and has summary calculations in the Group Footer.

If we removed the summary calculations from the Grid footer, the time to render the Grid went from 4 seconds to about .15 seconds.

The Grid page we were testing had four group breaks on it. That meant that Alpha Five was executing four SQL Summary queries against the Access database to get the summary values for each Group break.
We had already determined that Access was taking approximately 1.5 seconds for the initial query. So, adding in 4 additional summary queries to the Grid Render time meant that 6 second (4 * 1.5) was being taken up by slow Access summary queries.

So, that means a total of 7.5 seconds was being consumed by Access execuing the initial SQL query and then the 4 summary queries.

We then moved the data from Access to MySQL and the initial query time went from 1.5 seconds to .008 seconds and the overall performance of the Grid was inreased by an order of magnitude.

The bottom line: Access is not a substitute for a good, modern SQL backend, like MySQL, SQL Server, etc.

[kingcarol]

Re: Causes of slow page navigation?

A bit of clarification regarding the above test that Selwyn did. In his test, a flat table was used for the MySQL test, and not the queries being used in the Access test. This means that a test has not been run that compares like-kind actions. I connected the grid to a flat table in Access, rather than the layer of nested queries, and it runs at about same speed as the My Sql speed reported by Selwyn. The slow down may come from the queries in Access, but that has not been tested using MySql. Based on everything I've heard, I have no doubt that MySql could provide improved speed performance, but it may not be the 187 % difference reported in Selwyn's test. I think further testing would be required before moving your Access tables to MySql if that would be a problematic transition for someone using Access. It is problematic for me because I have a front end Access program that is accessing the same back end Access databases as the web app is accessing. It is very, very stable and we do not want to do anything that might rattle that stable Access program for our 100's of different clients and users.

[kingcarol]

Re: Causes of slow page navigation?

Well, I have taken my first plunge into MySQL and have managed to import tables and create a few views to match ones I have in Access. I then duplicated one of my grids and connected it to a view in MySQL . I set the timer on both grids and compared. Below are results after navigating to Pg 7 on each grid (53 records, 3 per pg, query on both has a left join)

Results from connection to Access are: http://screencast.com/t/4XDOSW0jhm
Results from connection to MySQL are: http://screencast.com/t/8fJSJyRmFjl

The time to execute the query on Access db was 146 milliseconds.
The time to execute the query on MySQL db was 109 milliseconds.

The time to expand the grid template was 177 milliseconds on both.
So, this test shows MySQL being about 34% times faster. I don't have any group breaks on the grid I am testing.

[chadbrown]

Re: Causes of slow page navigation?

Carol glad to see your getting results already. Is the order and filtering being done in the view or are you using the grid for that?

[kingcarol]

Re: Causes of slow page navigation?

On that test, filter was in the view, but order was in the grid. I just put the order in the view and took it out of the grid and the time to 'Compute grid filter and order' went down by by about 1 millisecond for each page navigate. But it is a simple ordering on one field, and a simple filter that is a not equals to. In your experience, Chad, is it best to put the filter and order in the view, or in the grid?

[kingcarol]

Re: Causes of slow page navigation?

I just tried moving both the filter and the order to the grid. Doing that makes the execution of the query seem to slow down by 5 to 9 milliseconds over having both of them in the view.

[Steve Workings]

Re: Causes of slow page navigation?

MySQL views can be terribly slow. They don't perform as well as MS SQL views. If you do use them, be sure the key fields are indexed.