What is query execution?
Query execution refers to the process of actually running and performing the operations specified in a query execution plan to produce the results of a database query. It takes the chosen query plan and compiles it into low-level procedures for the database engine to follow step-by-step to insert, retrieve, join, aggregate data as needed for the query.
Query compilation and optimization happens before execution to pick the optimal plan. But query execution does the real work of orchestrating the complex steps like table scans, index lookups, network transfers, sort operations, and final result set assembly as designed by the query plan.
There are different strategies to execute a query plan efficiently such as parallel execution across multiple cores/threads, distributed execution across clustered nodes, and partitioning data into independent chunks. Careful query execution significantly impacts overall query latency and scalability.
How does it work?
The database engine processes a query plan recursively with steps like:
Modern engines try to overlap IO, CPU, and network transfers for parallelism during execution.
Why does it matter?
Good query execution ensures operations in the optimal query plan translate to efficient utilization of database resources like IO, memory, network bandwidth.
Poorly executing a query negates benefits of picking a good query plan. Attention to execution details like dispatching work optimally, minimizing intermediate trips to disk or network, using parallelism, not thrashing memory/caches is key to getting good query performance.
FAQ
How can query execution be tuned?
Some ways to optimize query execution itself:
What execution methods are used for parallelism?
Parallel execution methods to speed up query processing on modern hardware:
How does distributed query execution work?
In distributed databases, queries execute by:
References:
Related Topics
Partitioning
Database partitioning refers to splitting large tables into smaller, independent pieces called partitions stored across different filegroups, drives or nodes.
Distributed Execution
Distributed execution refers to techniques to execute database queries efficiently across clustered servers or nodes, dividing work to utilize parallel resources.
Parallel Execution
Parallel execution refers to techniques for speeding up database query processing by leveraging multiple CPUs, servers, or resources concurrently.
SQL Compatibility
SQL compatibility refers to the degree to which a database or analytics system supports the SQL query language standard, enabling the use of standard SQL syntax and features.
ETL Data Processing
ETL (Extract, Transform, Load) data processing refers to the steps used to collect data from various sources, cleanse and transform it, and load it into a destination system or database.