SQL Select optimizations

Home

4D v15

SQL Select distinct statements

Originally introduced in 4D v14 R4

SELECT DISTINCT operations executed through SQL statements have been optimized when they are applied to a single table and a single column.

Typically, in 4D v15 and later versions, the following code will be executed faster:

 Begin SQL

    SELECT DISTINCT Names FROM Employees INTO :$Emp;

 End SQL

SQL Select Group by and Order by statements

Originally introduced in 4D v14 R5

SQL SELECT statements with GROUP BY and ORDER BY clauses have been optimized in different configurations:

SELECT FROM GROUP BY or SELECT FROM ORDER BY applied to a single table,
SELECT FROM GROUP BY or SELECT FROM ORDER BY applied to several tables and using inner joins.

The optimization concerns only simple column references (not expressions).

Typically, in 4D v15 and later versions, the following cases will be executed faster:

Order by examples

 Begin SQL

    DROP TABLE IF EXISTS T1;

    DROP TABLE IF EXISTS T2;

    CREATE TABLE T1 (C1 INT);

    CREATE TABLE T2 (C2 INT);

    INSERT INTO T1(C1) VALUES (1);

    INSERT INTO T1(C1) VALUES (2);

    INSERT INTO T1(C1) VALUES (3);

    INSERT INTO T2(C2) VALUES (2);

    INSERT INTO T2(C2) VALUES (3);

 End SQL

 

 ARRAY LONGINT($result;0)

 ARRAY LONGINT($result1;0)

 ARRAY LONGINT($result2;0)

 

  // A simple example of ORDER BY with T1 and T2 joined.

  // $result will contain [2, 3].

 Begin SQL

      SELECT C1

        FROM T1, T2

        WHERE C1=C2

        ORDER BY C1

        INTO :$result  

 End SQL

 

  // ORDER BY also works if several columns are used.

  // Both $result1 and $result2 will contain [2, 3].

 Begin SQL

      SELECT C1, C2

        FROM T1, T2

        WHERE C1=C2

        ORDER BY C1, C2

        INTO :$result1, :$result2

 End SQL

 

  // Only simple column references are optimized. If an expression

  // as shown below (C1 + 1) is used for the selection, then execution will not

  // be faster. $result will (still correctly) contain [3, 4].

 Begin SQL

      SELECT C1 + 1

        FROM T1, T2

        WHERE C1=C2

        ORDER BY C1

        INTO :$result  

 End SQL

 

  // You can use an index to refer inside the selection.

  // As stated before, the selection must only contain simple column references for fast execution.

  // $result will contain [ 2, 3 ].

 Begin SQL

      SELECT C1

        FROM T1, T2

        WHERE C1=C2

        ORDER BY 1

        INTO :$result  

 End SQL

Group by example

 Begin SQL

    DROP TABLE IF EXISTS T1;

    DROP TABLE IF EXISTS T2;

    CREATE TABLE T1 (C1 INT, C3 INT);

    CREATE TABLE T2 (C2 INT);

    INSERT INTO T1(C1, C3) VALUES (3, 1);

    INSERT INTO T1(C1, C3) VALUES (1, 1);

    INSERT INTO T1(C1, C3) VALUES (2, 1);

    INSERT INTO T1(C1, C3) VALUES (3, 1);

    INSERT INTO T1(C1, C3) VALUES (2, 1);

    INSERT INTO T1(C1, C3) VALUES (3, 1);

    INSERT INTO T2(C2) VALUES (2);

    INSERT INTO T2(C2) VALUES (3);

 End SQL

 

 ARRAY LONGINT($result;0)

 ARRAY LONGINT($result1;0)

 ARRAY LONGINT($result2;0)

 

  //A simple example of GROUP BY with T1 and T2 joined.

  //$result will contain [2, 3]. Note that there is no guarantee that groups will be returned

  //in increasing order, i.e., $result can be [3, 2].

 Begin SQL

    SELECT C1

        FROM T1, T2

        WHERE C1=C2

        GROUP BY C1

        INTO :$result  

 End SQL

 

  // Here is a simple example of an aggregate request.

  // Since C3 is always 1, for each group (unique value of C1),

  // SUM(C3) is the number of repetition(s).

  // $result1 will contain [2, 3].

  // $result2 will also contain [2, 3].

 Begin SQL

    SELECT C1, SUM(C3)

        FROM T1, T2

        WHERE C1=C2

        GROUP BY C1

        INTO :$result1, :$result2

 End SQL