Hi All,

Interbase/Firebird has a bad performance when a sub-select can't use a index
which compares fields with the 'base' select .
Is it possibly to cache sub-selects (only the fields that are used) in
memory when they are not using an index and for every NATURAL PLAN that is
executed more times in a select/insert/delete !

For example :

CREATE TABLE Relations (
RelationNumber INT NOT NULL PRIMARY KEY,
FirstName varChar(30)
)

CREATE TABLE Categories (
Category Char(15) NOT NULL PRIMARY KEY,
Description varChar(30)
)

CREATE TABLE RelationCategories (
RelationNumber INT NOT NULL ,
Category Char(15) NOT NULL,
CONSTRAINT PK_RELATION_CATEGORIES PRIMARY KEY (RelationNumber, Category),
CONSTRAINT FK_RC_CATEGORIES FOREIGN KEY (Category) REFERENCES Categories
(Category),
CONSTRAINT FK_RC_RELATIONS FOREIGN KEY (RelationNumber) REFERENCES
Relations (RelationNumber)
)

SELECT
r.FirstName
FROM
Relations r
WHERE
r.RelationNumber IN (SELECT rc.RelationNumber FROM RelationCategories rc
WHERE rc.Category = 'newsgroup')

In the above query the "SELECT rc.RelationNumber FROM RelationCategories..."
part is called every time again. Wouldn't it be better if it's cached into
memory. The memory-cache should grow until a match is found. For the next
match is first searched in the cache. Does the cache not contains the whole
sub-select, it's fetching and adding to memory-cache till again a match
found. If for a record no match is found then the complete sub-select is
cached into memory at once. When the memory-cache contains the complete
sub-select from then only the cache is used.


This can also solve the DELETE problem in next example :

DELETE FROM Categories
WHERE RelationNumber IN (SELECT FIRST 5 RelationNumber FROM Categories)


My english isn't that what it should be, but i hope you understand what i
meant.

Regards,
Arno Brinkman