CompTIA DataSys+ (DS0-001)Database FundamentalsMedium
A database developer is writing a complex SQL query that involves joining five different tables and performing several aggregations. This query is executed frequently by multiple applications. To improve performance and reduce the parsing/compilation overhead each time it runs, what database object should the developer consider creating?
- AA database trigger
- BA database view
- CA stored procedure
- DA database index
Show answer & explanationAnswer & explanation
Correct answer: C. A stored procedure
A stored procedure is a precompiled set of SQL statements. When executed, the database uses the precompiled execution plan, which reduces parsing and compilation overhead, leading to improved performance for frequently run, complex queries.
Why the other options are wrong
- A. A database trigger executes automatically in response to DML events and is not primarily used to encapsulate and optimize frequently run SELECT queries.
- B. A database view simplifies the SQL query by abstracting the underlying tables but doesn't precompile the execution plan or inherently improve performance beyond what the underlying query would achieve.
- D. A database index helps speed up data retrieval for specific columns but doesn't encapsulate or precompile a complex multi-table query.
Stored Procedure
A precompiled collection of one or more SQL statements (and optional control-of-flow statements) stored in the database and executed as a single unit.
- Reduces network traffic and improves performance via precompilation.
- Encapsulates complex business logic.
- Enhances security by granting permissions only to the procedure, not underlying tables.
Memory trick: Procedures are pre-written scripts for efficiency.