SQL query engine architecture to know

The query engine parses the query, verifies the semantics of the query against the catalog store, and generates the logical query plan that is then optimized by the query optimizer to generate the optimal physical execution plan, based on CPU, I/O, and network costs to execute the query.

The final query plan is then used by the storage engine to retrieve the underlying data from various data structures, either on disk or in memory.

It is in the storage engine that processes such as Lock Manager, Index Manager, Buffer Manager, etc., get to work to fetch/update the data, as requested by the query.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,735 other followers

SQL engine architecture
Image Source: Pixabay

Most RDBMS are SMP-based architectures. Traditional database platforms operate by reading data off disk, bringing it across an I/O interconnect, and loading it into memory for further processing.

An SMP-based system consists of multiple processors, each with its own memory cache. Memory and I/O subsystems are shared by each of the processors. The SMP architecture is limited in its ability to move large amounts of data, as required in data warehousing and large-scale data processing workloads.

 

The major drawback of this architecture is that it moves data across backplanes and I/O channels. This does not scale and perform when large data sets are to be queried, and more so when the queries involve complex joins that require multiple phases of processing.

A huge inefficiency lies in delivering large data off disk across the network and into memory for processing by the DataBase Management System (DBMS).

SQL query engine
SQL query engine

Please subscribe to “Srinimf Show” and rate and review podcasts:

Follow me on Social Media:

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.