Database Query Optimization for Energy Reduction Study Guide
IT7014: Digital Intelligence for Sustainability
Module 2: Green Software Engineering
Topic 2: Database Query Optimization for Energy Reduction
1. Introduction
Energy Consumption of Database Systems:
Database systems are among the most energy-intensive components in modern software infrastructure.
Each query triggers a sequence of physical operations including:
Disk pages read
Memory buffers allocated
Rows compared
Results assembled
Energy Cost Determinants:
Determined by the number of operations performed.
Access type: fast memory versus slow disk storage.
Identical queries can differ by a factor of 100+ in physical work depending on:
Query writing style
Indexes available
Structure and organization of underlying data
Previous topics indicated index-based lookups as critical in affecting computational cost.
2. How Database Queries Translate to Physical Operations
Stages of Query Processing:
The execution of a database query involves multiple, progressive stages:
Parsing:
SQL text checked for correct syntax and converted into an internal representation.
Energy Significance: Minimal energy is required, as parsing is a fixed-cost operation irrespective of data volume.
Planning:
Query planner evaluates multiple strategies to retrieve data, selecting the one with the lowest estimated cost.
Energy Significance: Moderate; affects the execution stages significantly concerning page reads.
Execution:
The chosen plan is executed, involving reading pages, filtering rows, performing joins, and assembling results.
Energy Significance: Dominant stage; accounts for 90%+ of total query energy and is determined by data access volume.
Energy Significance of Disk vs Memory Reads:
Cost differences between disk reads and memory reads are pivotal to energy consumption.
Page Data Storage:
Data is stored in fixed-size pages (typically 4 or 8 kilobytes).
3. How Indexing Mechanisms Reduce Query Work
Function of Indexes:
An index is a data structure maintained alongside a table allowing fast access to specific rows.
Without an index:
Database reads all pages, culminating in high energy consumption.
With an index:
Database locates relevant pages directly, bypassing unnecessary reads.
Index Lookup Mechanism:
Stores a sorted copy of indexed columns and pointers to corresponding full rows.
Querying an indexed column involves searching the index to find matches and accessing corresponding rows.
Example:
Querying a university database for students enrolled in a specific major.
Without Index:
Database examines all 12,500 pages (98% irrelevant).
With Index:
Approximately 3 index pages accessed and 250 data pages for relevant rows.
Index Maintenance Energy Costs:
Each row addition/removal requires DB to update its indexes too:
For a table with 5 indexes, adds 5 additional write operations per row.
Trade-off depends on read/write ratio:
Favorable for frequently read tables.
Higher writes in heavily inserted tables.
4. How Query Structure Affects Computation
Different structures impact physical resource consumption, despite returning identical results.
Column Selection:
Retrieving all columns (e.g., SELECT *) results in unnecessary data reads.
Restricting columns saves energy; focus on specific columns (e.g., SELECT student_id, name, gpa).
Join Operations:
Energy costs vary based on join methods (e.g., nested loop vs. hash join). Efficiency is determined by factor like table sizes and available indexes.
5. Schema Design and Energy Trade-offs
Schema Structure's Impact:
Decisions made during the schema design have long-term implications for energy consumption; challenging to amend after deployment.
Normalization:
Reduces redundancy, each fact is stored once, but requires joins for complete information assembly.
Denormalization:
Combines data into fewer tables, cutting down on joins, but increases update costs due to duplicated data.
Characteristic | Normalised Schema | Denormalised Schema |
|---|---|---|
Read query energy | Higher (joins are necessary) | Lower (no joins required) |
Write query energy | Lower (single-row updates) | Higher (multiple rows cannot be updated simultaneously) |
Storage per row | Smaller (no duplication) | Larger (due to duplicated columns) |
Data consistency risk | Low (single source) | Higher (possible inconsistencies) |
6. Measuring Query Energy Cost
Energy Consumption Estimation:
Cannot be measured directly; must use proxy metrics (e.g., pages read, rows examined).
Database Systems Commands:
Commands like EXPLAIN show the execution plan before queries execute, indicating relative energy costs.
7. Case Studies
Case Study 1: University Student Information System
Current vs Optimized Execution:
Various queries optimized, resulting in significant energy reductions:
Daily energy consumption reduced from 173 watt-hours to 6 watt-hours (96.5%).
Case Study 2: IoT Environmental Monitoring Platform
Data optimizations led to dramatic decreases in query read operations and duration.
Case Study 3: E-commerce Product Search
Utilizing composite indices and application caching reduced individual query energy consumption by up to 93%.
8. Application Scenarios for Analysis
Scenario A: Healthcare records optimization query against a large patient database with normalization; assessment of different indexing impacts.
Scenario B: Logistics shipment tracking focusing on partitioning strategies for rapid access to data across numerous updates.
Scenario C: Social media notification system troubleshooting for notification performance improvement via indexing and query specificity.
Conclusion
Topic summary emphasizes the impact of database query design, indexing strategies, join mechanisms, and schema choices on energy consumption in software systems.