Last Updated:

Query development in SQL

Related subqueries and associations.

A subquery is a SELECT query that is included in another query as a parameter or expression. They are typically used to generate a value or set of results that are used in the conditions of the master query.

The correlated subquery is the only difference between the records for the master query because it depends on the values that vary from record to record. Interbase performs this subquery many times, once for each parent subquery entry. Calculating each record is a big loss in performance relative to an unrelated subquery. Interbase optimizes unrelated subqueries outside the loop, executing them only once, and then using the results as a separate dataset.

Here's an example of a related subquery:

SELECT *
	FROM DEPARTMENT D
	WHERE EXISTS
		( SELECT *
			FROM EMPLOYEE E
			WHERE E.EMP_NO = D.MNGR_NO
			AND E.JOB_COUNTRY = 'England')

And here's an example of an identical query using aggregation:

SELECT D.*
	FROM DEPARTMENT D JOIN EMPLOYEE E ON D.MNGR_NO = E.EMP_NO
	WHERE E.JOB_COUNTRY = 'England'

The execution plan for the query. The plan describes the scenario in which the optimizer decided to execute the query. For some types of queries, the optimizer is not able to select the actual optimal plan. A person can analyze various plans and, choosing the right one, overlap the plan chosen by the optimizer. The result can be a stunning increase in speed on some types of queries. In the most advanced cases, you can reduce the execution time of a 15-minute request to three seconds.

The ability to specify a query plan has been added to GPRE and DSQL/ISQL. Thus, directly specifying the query plan will work in the view body, stored procedure, or trigger.

This method is discussed in more detail here.

Query Preparation and Query Parameters

Interbase supports parameter queries in DSQL for cases where a specified query will be executed multiple times with different values. For example, populating a table with data might require a series of INSERT commands with values for each record. Using parameterized queries directly improves performance because Interbase stores an internal view of the query and its optimized plan after a single query preparation.

Using parameterized queries in Delphi is done in the following steps:

Place the named parameter in the query in place of the constant value. Interbase does not support parameters anywhere except in constants, tables and field names cannot be described by a parameter.

Prepare the query by using the Prepare method of the TQuery component. Delphi prepares the query if it is not already prepared each time before execution. After the request is executed, its preparation is automatically unpublished. Thus, preparing a request in advance prevents pointless preparation and cancellation of preparation whenever the request is called.

Assign parameters. For example, for the TQuery component, use the ParamByName method.

Execute the query. SELECT statements must be opened by the Open method, and INSERT, UPDATE, and DELETE must be activated by execSQL.

If necessary, repeat paragraphs 3 and 4.

Cancel the preparation of the request. This is done by the Unprepear method of the TQuery component.

in some real-world scenarios that involve repetitive actions using parameterized queries increases performance by 100%