The solidDB server uses a cost-based optimizer that ensures that SQL statements are executed efficiently.
Optimizer
The solidDB SQL optimizer is a cost-based optimizer. It uses the same techniques as a rules-based optimizer, relying on a preprogrammed set of rules to determine the shortest path to the results. For example, the optimizer considers whether an index exists, if the index is unique, and if the index is over single or composite table columns. However, unlike a rule-based optimizer, the cost-based optimizer can adapt to the actual contents of the database, for example, the number of rows and the value distribution of individual columns.
The server maintains the statistical information about the actual data automatically, ensuring optimal performance. Even when the amount and content of data changes, the optimizer can still determine the most effective route to the data.
Query processing
Queries are processed in small steps to ensure that one time-consuming operation does not block other application requests. A query is processed in a sequence that contains the following phases:
▪ Syntax analysis
▪ Creating the execution graph
▪ Processing the execution graph
Syntax analysis
An SQL query is analyzed and the server produces either a parse tree for the syntax or a syntax error. When a statement is parsed, the information necessary for its execution is loaded into the statement cache. A statement can be executed repeatedly without re-optimization, on condition that its execution information remains in the statement cache.
Creating the execution graph
The execution graph, which contains the following features, is created from the query parse tree.
▪ Complex statements are written to a uniform and more simple form.
▪ If better performance can be realized, OR criteria are converted to UNION clauses.
▪ Intelligent join constraint transfer is performed to produce intermediate join results that reduce the join process execution time.
You can use the EXPLAIN PLAN FOR statement to show the execution plan that the SQL optimizer selected for the SQL statement.
Processing the execution graph
The execution graph is processed in three consecutive phases:
▪ Type-evaluation phase
The column data types of the result set are derived from the underlying table and view definitions.
▪ Estimate-evaluation phase
The cost of retrieving first rows and also entire result sets is evaluated, and an appropriate search strategy is dynamically selected based on the parameter values that are bound to the statement.
The SQL Optimizer bases cost estimates on automatically maintained information about key value distribution, table sizes, and other dynamic statistical data. Manual updates to the index histograms or any other estimation information are not required.
▪ Row-retrieval phase
The result rows of the query are retrieved and returned to the client application.
Optimizer hints
Optimizer hints are an extension to solidDB SQL. They are directives that are specified through embedded pseudo comments within query statements. The optimizer detects these directives or hints and bases its query execution plan accordingly. Optimizer hints allow applications to be optimized under various conditions to the data, query type, and the database. They not only provide solutions to performance problems occasionally encountered with queries, but shift control of response times from the system to the user.
solidDB network services
solidDB network services are based on the remote procedure call (RPC) paradigm, which makes the communication interface simple to use. When a client sends a request to the server, it resembles calling a local function. The network services invisibly route the request and its parameters to the server, where the actual service function is called by the RPC server. When the service function completes, the return parameters are sent back to the calling application.
In a distributed system, several applications can request a server to run multiple operations concurrently. For maximum parallelism, solidDB network services use the operating system threads when available to offer a seamless multiuser support. On single-threaded operating systems, the network services extensively use asynchronous operations for the best possible performance.
Communication session layer
The solidDB communication protocol DLLs (or static libraries) offer a standard internal interface to each protocol. The lowest part of the communication session layer works as a wrapper that takes care of choosing the correct protocol DLL or library that relates with the address information. After this point, the actual protocol information of the session is hidden. solidDB can listen to several protocols simultaneously.
Types of threads
The solidDB threading system consists of general-purpose threads and dedicated threads.
General-purpose threads
General-purpose threads execute tasks from the tasking system of the server. They execute such tasks as serving user requests, making backups, executing timed commands, merging indexes, and making checkpoints (storing consistent data to disk).
General-purpose threads take a task from the tasking system, execute the task step to completion and switch to another task from the tasking system. The tasking system works in a round-robin fashion, distributing the client operations evenly between different threads.
The number of general-purpose threads can be set in the solid.ini configuration file.
Dedicated threads
Dedicated threads are dedicated to a specific operation. The following dedicated threads can exist in the server:
▪ I/O manager thread
This thread is used for intelligent disk I/O optimization and load balancing. All I/O requests go through the I/O manager, which determines whether to pass each I/O request to the cache or to schedule it among other I/O requests. I/O requests are ordered by their logical file address. The ordering optimizes the file I/O since the file addresses accessed on the disk are in close range, reducing the disk read head movement.
▪ Communication read threads
Applications always connect to a listener session that is running in the selector thread. After the connection is established, a dedicated read thread can be created for each client.
▪ One communication select thread per protocol (known as the selector thread)
There is usually one communication selector thread per protocol. Each running selector thread writes incoming requests into a common message queue.
▪ Communication server thread (also known as the RPC server main thread)
This thread reads requests from the common message queue and serves applications by calling the requested service functions.