Database-Centric Applications
Most web applications require persistent storage, usually a shared database. In this kind of application you can identify several layers:
While clients may connect from the Internet or a local Intranet, the web application server needs a much closer connection to the database server to achieve reasonable performance when answering client requests.
Accessing the database directly from the web application server (i.e., having the data access layer as part of the web app) increases resource usage on that server, risking stability and scalability.
Given limited bandwidth between clients and server, it is important to limit the amount of information sent to the client (and coming back). Since one web server answers requests from many clients, limit the amount of data required for client functionality. A shared database server should also be optimized for multiple connections.
Below are a few best practices for handling data-centric applications.
Database
An enterprise-class application using a shared database as persistent storage usually follows these best practices:
Instead of using the same tables for all purposes, separate them by tasks. It is even possible to have different databases hosted on different servers. One database can handle online transaction processing (OLTP) while another provides data for complex reports (OLAP). See OLTP and OLAP: https://en.wikipedia.org/wiki/Online_transaction_processing and https://en.wikipedia.org/wiki/Online_analytical_processing
The OLTP database should be in 3rd Normal Form (3NF) to preserve integrity. Optimize it for fast data entry, updates, and simple queries (indexes tuned to expected use). Use transactions for complex updates so they can be rolled back as a whole.
If transaction volume slows the database, consider moving older (now read-only) transactions to a secondary historical database or as a data source for the OLAP database.
The OLAP database should be optimized for complex queries with fast response time: aggregate data and add dimensions. A typical architecture is a large fact table in a star schema.
Perform heavy processing on the database server (for example, modifying millions of records should be done server-side). Enterprise-class database servers can host business logic as stored procedures, which often dramatically improve performance.
Avoid triggering synchronous actions based on client requests unless strictly necessary. For example, rather than calling a remote server or web service synchronously when a DB update occurs, leave a notification in a local table and process notifications in a background job.
Note: None of these best practices are specific to uniGUI; they apply to any data-centric application.
Database Access
Having a powerful database server and a fast connection doesn't mean you should request information you don't need. A table may contain millions of rows, but you should not request ALL of them.
Consider accessing the database using a data module, a database connection (FireDAC TFDConnection or similar), and a query. Asking for ALL records from a huge table can eventually raise an "Out of Memory" exception. The key principle: request only the information you need, never more.
Also be aware of the specifics of your data access technology:
Some technologies are smart enough to open a query but deliver data using a paging algorithm; the developer sees the total record count, but rows are fetched only when needed.
Other technologies require manual configuration to avoid massive data loads (e.g., fetching all records just to report RecordCount).
Many middleware products handle these issues (for example RemObjects DataAbstract http://www.dataabstract.com/da/, kbmMW http://components4developers.com/, and DataSnap https://en.wikipedia.org/wiki/DataSnap).
Simplest solutions:
Ensure any user-requested information is filtered. If the result set exceeds a predefined limit, reject the request and ask the user to narrow the filter.
Alternatively accept the request but implement paging if the data access layer does not handle it.
Principle: the amount of information a user can use is always small — never send superfluous data.
Data Controls (server and client)
In an N-Tier layered approach, each layer should only handle translating requests from the next layer into requests for the previous layer.
uniGUI data controls make requests to the previous layer through datasets. The client-side Ext JS components (rendered dynamically by the uniGUI server) display received data and accept user input that triggers further Ajax requests back to the server. Keep this traffic to a minimum.
Examples of how uniGUI supports these best practices:
TUniEdit — Property CheckChangeDelay allows triggering the OnChange event only after a delay (useful when filtering a dataset based on the changed value).
TUniDBLookupComboBox — Supports a custom RemoteQuery that can be optimized based on what the user types (also includes RemoteDelay).
TUniDBGrid — Provides column filtering, paging, client-side navigation, etc.