My application when run with all the tables open runs uses a large amount
of memory , in fact too much. When I run a certain query my application
seems to literally suck up all memory. I have the following database setup.
I have 7 tables. I use ZeosDBO components and Delphi 7. Firebird 1.5R7
embedded. If I open 5 of the tables(low importance) my app uses 13.8MB ,
15.28MB , 39.9MB current , peak , virtual memory respectively. When I
however add the other 2 tables in a query. My application uses 46.328MB ,
95.444MB , 60.676MB current , peak , virtual memory. Now this is a chunk of
memory. My two table structures look the following :

CREATE TABLE Whitepage /* Wp */
(

WpIDSQ INTEGER NOT NULL ,
CityVBSQ I NTEGER NOT NULL ,
WpVBSQ INTEGER ,
WpName VARCHAR(120) NOT NULL ,
WpPersonal SMALLINT DEFAULT 1 NOT NULL ,
WpOrder INTEGER NOT NULL ,
WpGroup INTEGER NOT NULL ,
WpLevel INTEGER NOT NULL ,

PRIMARY KEY (WpIDSQ) ,
FOREIGN KEY (CityVBSQ) REFERENCES City (CityIDSQ) ON DELETE CASCADE ,
FOREIGN KEY (WpVBSQ) REFERENCES Whitepage (WpIDSQ) ON DELETE CASCADE
) ;

/*------------------------------New Table-----------------------------*/
CREATE TABLE Description /* Descr */
(
DescrIDSQ INTEGER NOT NULL ,
WpVBSQ INTEGER NOT NULL ,
DescrData VARCHAR(120) ,

PRIMARY KEY(DescrIDSQ) ,
FOREIGN KEY (WpVBSQ) REFERENCES Whitepage (WpIDSQ) ON DELETE CASCADE
) ;

CREATE ASCENDING INDEX IDX_ASC_Whitepage_ID_Name ON Whitepage (WpIDSQ,WpName);
CREATE DESCENDING INDEX IDX_DESC_Whitepage_ID_Name ON Whitepage
(WpIDSQ,WpName);

CREATE ASCENDING INDEX IDX_ASC_DescriptionData ON Description (DescrData);
CREATE DESCENDING INDEX IDX_DESC_DescriptionData ON Description (DescrData);

The Query that I run looks like this :
SELECT DISTINCT W.WpIDSQ , W.WpVBSQ , W.WpName , W.WpLevel , W.WpOrder ,
W.CityVBSQ CityDialCode , D.DescrData
FROM Whitepage W LEFT JOIN Description D ON (W.WpIDSQ = D.WpVBSQ)
GROUP BY W.WpIDSQ , W.WpVBSQ , W.WpName , W.WpLevel , W.WpOrder ,
W.CityVBSQ , D.DescrData
ORDER BY W.WpOrder

No I calculated the record of the Whitepage table being (1 x Varchar[120] +
6 x Integer[4] + 1 x SmallInteger[2]) = (120 + 24 + 2) = 146 bytes. The
Description table being (1 x Varchar[120] + 2 x Integer) = (120 + 8) = 128
bytes. Now the whitepage table has 73415 records and the description has
48568 records. So I calculated table at space = record size * amount of records
Whitepage = 146 bytes * 73415 records = 10718590 bytes = 10.7 MB
Description = 128 bytes * 48568 records = 6216704 bytes = 6.2 MB
Total 16 935 294 = +-17MB

Now if I add 13.8MB(without the whitepage and description table)
+ 17MB(calculates table sizes) =
+-31MB which is not equal to 46.328MB. Where does the extra 15MB come from
indexes ?? My Page buffers are = 0 and my Page size = 8192 , OSD 10.1. I am
using Windows2000. NTFS. How do I seriously limit the memory use without
cutting any of the fields away as they are all important ? Different index
, no indexes different , probably different SQL code ? Change of buffers ?
Can anybody help as I am not yet finished developing the application and it
already is killing my RAM. Supposed to become a small distributed application.