Better application design.
Efficient relational access.
Application performance focuses on application code, SQL statements, and how the application interacts with the DBMS.
Weak design can make otherwise simple requests expensive.
Design for performance before the application becomes difficult to change.
Poorly constructed application code can account for a large share of relational database performance problems.
When application performance suffers, examine how the program is designed for relational access.
Choose design decisions that support efficient database access.
1. Check whether the application uses the appropriate type of SQL.
Two early design questions: are we using the right kind of SQL, and is the language environment optimized for database access?
The question is not “which one is always best?” but “which one is appropriate here?”
The language must meet the required performance and support efficient DBMS access.
An application should preserve ACID properties while the transaction processor performs the database work.
A post action begins as one transaction.
Design the unit of work clearly: create post, update feed, write notification event.
Batch programs process many records in an ordered stream so the DBMS can use sequential processing efficiently.
Online applications should return useful information while minimizing how much information is returned in one invocation.
The user only needs one personalized feed page.
Return only what this request needs now.
Illustrative counts · same feed request, smaller and more useful result set.
The application specifies what data is needed; the optimizer decides how to navigate the database efficiently.
The social media app asks for data without needing to know where or how it is physically stored.
The optimizer uses formulas and models to estimate the machine cost of each possible access path.
Estimate the CPU time required for each candidate path.
Consider database statistics, data-cache efficiency, and intermediate work files.
The exact costing formulas vary by DBMS; the key idea is comparing estimated machine work.
The formulation and complexity of SQL help determine which access paths the optimizer will consider.
SELECT post_id, author_id, created_at FROM posts WHERE viewer_id = 'USR-042' ORDER BY created_at DESC;
The statement tells the DBMS which feed data and ordering the application needs.
When multiple tables are needed, the optimizer decides how to combine them and which table to process first.
| ID | Post |
|---|---|
| P-001 | Photo upload |
| P-002 | Status update |
| ID | Metric |
|---|---|
| P-001 | Likes + comments |
| P-002 | Shares + views |
Predicates, statistics, available indexes, and data volume help guide the order.
A table scan is the simplest access method: read every row of the table.
Simple access, but a large table can require substantial I/O because every row is read.
Visual example only · a table scan continues through the table according to the DBMS operation.
The optimizer may use an appropriate index when an indexed column appears in an indexable predicate.
Read rows until the match is found.
Use an existing suitable index when the predicate allows it.
Find P-017. Compare the two possible access styles.
An index does not automatically apply to every WHERE clause; the optimizer still decides whether to use it.
Sorting can be costly, so an appropriate index may help the DBMS avoid a separate sorting step.
These operations can require ordering; the optimizer may use a suitable index to reduce sorting work when possible.
This deck is limited to Unit 5, Topic 3: Application Performance.
Application Performance: designing applications for relational access and relational optimization.
Type of SQL, programming language, transaction design, batch processing, and online processing.
Optimizer, CPU and I/O costs, query analysis, joins and join order, table scans, indexed access, and avoiding sorts.
Database Administration: The Complete Guide to Practices and Procedures. Addison Wesley.
Social media app names, post IDs, and record counts are illustrative examples used only to visualize the concepts.