Large tables in administrative console's grids.
- Erik S.
Imported From: http://groups.google.com/group/in-portal-dev/browse_thread/thread/27da671865b184ad#
not optimized by indexes, the SQL-queries will be executed for a long
time. If such a large table is shown as grid in administrative
console, then it is possible that the admin interface will be blocked
due to excessively long SQL-query.
To prevent such a trouble, should be optimized to index all possible
filters, custom-like filters over a grid and the system filters, as
well as all the sorting options.
For example, a standard grid users of the site - u.regular - will
obviously have problems when large amounts of users in system,
because:
1) Is not index-optimizable system filter by the field from the joined
table - 'ug.GroupId <> 11 OR ug.GroupId IS NULL'
2) Has the ability to search and sort on non-indexed fields FirstName
and LastName
I suggest:
1) To add duplicate field PrimaryGroup in the PortalUser table, and
make the system filter on it, and not on joined UserGroup table.
2) In the Unit Configs, under Grids => Fields, for each field make
possible options BlockSortingOnManyRecords and
BlockFilteringOnManyRecords, exposure which will mean that a large
number of entries in the list for him will be automatically banned by
sorting and/or filtering (admin can not put a filter or sorting via
the administrative console on the relevant columns).
3) In the general site configuration add NonIndexedRecordsLimit
parameter - the number of entries above which should affect the
mapping of elements of the filtering and sorting through the
configuration options BlockSortingOnManyRecords and
BlockFilteringOnManyRecords.
4) Make changes in the grid blocks in the templates of the
administrative console to ensure that lock elements to sort and search
if too many entries in the list in accordance with the parameters
BlockSortingOnManyRecords and BlockFilteringOnManyRecords.
5) Arrange options BlockSortingOnManyRecords and
BlockFilteringOnManyRecords in Unit Configs for tables in which the
number of records may actually be too big. For example, the Agents
will hardly be so many records, and for her, these parameters need not
put, but in u.regular be sure to put them.
6) Check the system filters for tables in which the number of records
may actually be too big, and if there are non-optimized by indexes
system filters, then make structural changes to alter this situation.