"TUPLE Constructor" implementation
William Pristia, Feb. 02 2005

Abstract
"Tuple constructor", a way to improve the SQL Firebird engine
performance.
________________________________________

Table of contents

1 Introduction
2 Analysis
3 Conclusion
4 Author's address
________________________________________

1 Introduction

The "Tuple constructor" is a particular Sql standard expression with
can be
expressed in the following example :

TABLE_1 and TABLE_2 are defined by :

CREATE TABLE TABLE_1 (
COL1 ANY_DATA_TYPE,
COL2 ANY_DATA_TYPE,
…
COLn ANY_DATA_TYPE);

CREATE TABLE TABLE_2 (
COL1 ANY_DATA_TYPE,
COL2 ANY_DATA_TYPE,
…
COLn ANY_DATA_TYPE);

The following query is an implementation of the "Tuple constructor"
expression :

(1) SELECT COL1,…,COLn FROM TABLE_1 T1
WHERE (T1.COL1,T1.COL2) IN (SELECT T2.COL1,T2.COL2
FROM TABLE_2 T2 [WHERE …])

Unfortunately this query does not work in Firebird!

________________________________________

2 Analysis

To obtain the same results (1) in Firebird we can use the following
query :

(2) SELECT COL1,…,COLn FROM TABLE_1 T1
WHERE T1.COL1 IN (SELECT T2_1.COL1 FROM TABLE_2
T2_1 [WHERE…]) AND
T1.COL2 IN (SELECT T2_2.COL2 FROM TABLE_2
T2_2 [WHERE…]);

The tests executed on the schema instance are :

Test 1 :

in TABLE_1 have been inserted 3 records
in TABLE_2 have been inserted 2 records

the following results express the number of reads on the instance by
the query (2) :

TABLE_1 reads 3
TABLE_2 reads 6

Test 2 :

in TABLE_1 have been inserted 8 records
in TABLE_2 have been inserted 4 records

the following results express the number of reads on the instance by
the query (2) :

TABLE_1 reads 8
TABLE_2 reads 24

The Sql Firebird engine does not execute any optimization on the
stream duplicates.

I have performed manually the calculus of the reads number; the
results is that the
tuple constructor improve the SQL Firebird engine performance (it
reduce reads on TABLE_2).
________________________________________

3 Conclusions

The "tuple constructor" improve the SQL Firebird engine performance
and also improve the Sql code writing.

4° Author's Address

William Pristia
Firebird Foundation Associate Member
EMail: william.pristia@...; william.pristia@...