... sorry for the wall ...
this is maybe kind of a general question. One customer asks for a performance update of a software. Especialy he wants the implemented lists to perform smoother.
These lists (grid) are based on a repository-version developed/updated for years (startet originally for IE6). Because they are quite old, the design isn´t that shiny of an actual web application.
They have many features
- Row selections
- Paging / Sliding incl. pagesize limiter
- Dynamic cell content for I10N / L14N / Links / Images
- Tooltip (Containing full content of a long line or a special content)
- Column configuration UI (Order, Size, Hide, Textsize)
- Export to CSV/XLS/PDF
- Multiple levels of caching
Maybe there is more that I forgot. The platform is a LAMP VM environment. Most users are conected directly via theire intranet. Some of then needs to use a VPN.
Now some detail to one list
- 74 different colums - most users have displayed 10 of them
- 7 joined tables, 10 columns requires subselects to display additional/agregated data
- A full set has ~25000 entries. The real value varies because of explicit/implicite permissions
- The default pagesize is 20
- 10 Filter. Most are dropdown boxes which display all possible values to the user and return the reference IDs to filter. Some of them contains additional combined values. One ID and one freetext filter
On my development pc client+server are on the same machine. Times are "Overall elapsed" by Fiddler, content sizes are compressed
- The initial load of a page with NumRows of 20 takes 1.7sec for 54kb
- Changing the NumRows to 100 takes 1sec for 30kb
- Changing back to 20 takes 0.35sec for 15kb
- The initial load of a page with NumRows of 100 takes 1.85sec for 70kb
- Changing the NumRows to 20 takes 0.85sec for 15kb
- Changing back to 100 takes 0.45sec for 30kb
- The times for using filters takes between 0.3 and 3sec depending of the filter and value. Not each of them can be done on an index and some are applyed on "joined table X"
Overall the test times are not that bad, but the customer tells about a "bad experience" - the application is to slow for him and/or the end users. They use bigger page sizes (400 is max), so they didn´t need to reload parts of the that often - but this increases the initial load time overall (Think about the number of DOM elements when creating a grid of this size).
I looked at some demos of other grids (phpGrid, SenchaGrid, DojoGrid, SlickGrid). They all looks prettier, some have nice additional features - but noone has all the features of the existing lists. To aggree with the lack of one feature the grid must offer a huge bonus in performance which i didn´t believe on.
It supports smooth performance for "unlimited" records. But the load of all data of the example list above will be ~5MB. Therefore we need to do AJAX requests to load the data. The given filter didn´t support comboboxes - the user never knows, what possibilities of data in the lists exists. Because we use AJAX, we cannot filter the existing list on client and must do this on the server.
The search requests of the actual list already delivers the ID the user searches for. But now we must first execute a query to get the IDs (which may be an amount of 20k in a bad case), and then use these IDs to query for the user data
On another project (.Net) I have used DevExpress - which has all the features that the PHP lists have. But at the end these lists doesn´t have any performance plus vs. our PHP one
Do you think or know about a Grid which could perform all this features in a performanter way? Or did you think the problem can only be solved in another way (Removing features/attributes, using precalculated instead of live data, Upgrading clients, Providing more "specialized" lists to minimize attributes/filters, ...)?