Hi,

I am facing a serious problem with memory tables and system memory.  I have this scenario:

1- A memory table is created.  This memory table is connected to a DBISAM session.
2- Some data (About 200,000 records) is moved from an external table (somewhere on the hard drive) to the memory table using SQL (INSERT
INTO "MEMORY\Table_M" SELECT * FROM "C:\Table_B").  While the execution of this statement the used memory is increased by 100 MGB.
3- Next a run a couple of UPDATE statements on that memory table and no memory usage increase is happening.
4- Now the data in the memory table is moved to another table inside the database using SQL (INSERT INTO Table1 SELECT *
FROM "MEMORY\Table_M").  Another 100-200 MGB of RAM is lost!!!  If I repeat this scenario for another 10 tables the system run-out-of-memory.

What is happening?

I use this procedure to run the SQL statements:


procedure RunSQL(const sSQL : String);
var
         qrySQL : TDBISAMQuery;
begin
         qrySQL := TDBISAMQuery.Create(Application);
         qrySQL.SessionName    := dmAccount.DBISAMSession1.SessionName;
         qrySQL.DatabaseName := dmAccount.DBISAMDatabase1.DatabaseName;
         qrySQL.SQL.Clear;
         qrySQL.SQL.Add(sSQL);
         qrySQL.ExecSQL;
         qrySQL.Free;
end;

Hi,

I don't know why the insert consumes memory except maybe that it is
buffering the insert in memory until the transaction completes & maybe
you haven't seen windows garbage collection kick in to recover this
after the memory is released. I use the fastMM manager just to make sure
I have no memory leaks & I bet it would give a more accurate picture of
memory use than the system memory being reported by Windows... maybe
someone else has some experience with this.

Did you remember to drop MEMORY\TABLE_M? You created the table & it will
continue to exist until you drop it or close the application.

Sean

Abdulaziz Jasser wrote:
> Hi,
>
> I am facing a serious problem with memory tables and system memory.  I have this scenario:
>
> 1- A memory table is created.  This memory table is connected to a DBISAM session.
> 2- Some data (About 200,000 records) is moved from an external table (somewhere on the hard drive) to the memory table using SQL (INSERT
> INTO "MEMORY\Table_M" SELECT * FROM "C:\Table_B").  While the execution of this statement the used memory is increased by 100 MGB.
> 3- Next a run a couple of UPDATE statements on that memory table and no memory usage increase is happening.
> 4- Now the data in the memory table is moved to another table inside the database using SQL (INSERT INTO Table1 SELECT *
> FROM "MEMORY\Table_M").  Another 100-200 MGB of RAM is lost!!!  If I repeat this scenario for another 10 tables the system run-out-of-memory.
>
> What is happening?
>
> I use this procedure to run the SQL statements:
>
>
> procedure RunSQL(const sSQL : String);
> var
>           qrySQL : TDBISAMQuery;
> begin
>           qrySQL := TDBISAMQuery.Create(Application);
>           qrySQL.SessionName    := dmAccount.DBISAMSession1.SessionName;
>           qrySQL.DatabaseName := dmAccount.DBISAMDatabase1.DatabaseName;
>           qrySQL.SQL.Clear;
>           qrySQL.SQL.Add(sSQL);
>           qrySQL.ExecSQL;
>           qrySQL.Free;
> end;
>

Hi Sean,

<<Did you remember to drop MEMORY\TABLE_M? You created the table & it will
continue to exist until you drop it or close the application.>>

Thanks for the reply.  However I do delete the memory table at the end of the code.  This is a sample of my code:

var
         sSQL   : String;
         oTable : TDBISAMTable;
begin
         //Create the memory table;
         oTable := TDBISAMTable.Create(Application);
         oTable.SessionName  := DBISAMSession1.SessionName;
         oTable.DatabaseName := 'MEMORY';
         oTable.TableName    := 'Table_M';
         oTable.FieldDefs.Add('Field1',ftString ,20,False);
         oTable.FieldDefs.Add('Field2',ftInteger, 0,False);
         oTable.CreateTable;

         //Load the data from a local table.
         sSQL := 'INSERT INTO "MEMORY\Table_M" SELECT * FROM "C:\Table1.Dat"';
         RunSQL(sSQL);

         //Update some fields in the memory table.
         sSQL := 'UPDATE "MEMORY\Table_M" SET Field2 = 0';
         RunSQL(sSQL);

         //Now move the data from the memory table to another table inside the database.
         sSQL := 'INSERT INTO Table1 SELECT * FROM "MEMORY\Table_M"';
         RunSQL(sSQL);

         //Now delete the memory and free the memory table.
         oTable.DeleteTable;
         oTable.Free;
         oTable := Nil;
end;


If I run the above code more than 10 times I got a message "System out of Memory".  In fact I can see the used memory growing inside the
Windows tasks list.

Abdulaziz,

<< 4- Now the data in the memory table is moved to another table inside the
database using SQL (INSERT INTO Table1 SELECT *
FROM "MEMORY\Table_M").  Another 100-200 MGB of RAM is lost!!!  If I repeat
this scenario for another 10 tables the system run-out-of-memory. >>

Is there a Table1 present in the "Memory" database ?  What is the
DatabaseName property being set to when the above query is run ?  Is it a
disk-based database or the "Memory" database ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Abdulaziz Jasser wrote:
> Hi,
>
> I am facing a serious problem with memory tables and system memory.
> I have this scenario:
>
> 1- A memory table is created.  This memory table is connected to a
> DBISAM session. 2- Some data (About 200,000 records) is moved from an
> external table (somewhere on the hard drive) to the memory table
> using SQL (INSERT INTO "MEMORY\Table_M" SELECT * FROM "C:\Table_B").
> While the execution of this statement the used memory is increased by
> 100 MGB.

That should be ok, 500 byte or so per record.

> 3- Next a run a couple of UPDATE statements on that memory
> table and no memory usage increase is happening. 4- Now the data in
> the memory table is moved to another table inside the database using
> SQL (INSERT INTO Table1 SELECT *
> FROM "MEMORY\Table_M").  Another 100-200 MGB of RAM is lost!!!  If I
> repeat this scenario for another 10 tables the system
> run-out-of-memory.
>
> What is happening?

The embedded SELECT statement creates a new copy of your in-memory table, I
don't know if this copy is in memory or on disk. If it's in memory, it
should be freed after the INSERT statement is done. And I am pretty sure
that it is, and that you encounter a problem with the default memory
manager. Try FastMM or DBISAMMM and see if there is any difference.

....
> procedure RunSQL(const sSQL : String);
> var
>          qrySQL : TDBISAMQuery;
> begin
>          qrySQL := TDBISAMQuery.Create(Application);

try

>          qrySQL.SessionName    :=
>          dmAccount.DBISAMSession1.SessionName; qrySQL.DatabaseName :=
>          dmAccount.DBISAMDatabase1.DatabaseName; qrySQL.SQL.Clear;
>          qrySQL.ExecSQL;
>          qrySQL.SQL.Add(sSQL);

finallly

>          qrySQL.Free;

end;

> end;

Ralf

Tim,

<<Is there a Table1 present in the "Memory" database ?>>
No.  It is located inside the database that is linked to the session and database components in the program.  In other words, it's a disk-based
database table.

<<What is the DatabaseName property being set to when the above query is run ?>>
The database name and session name are set to the database and session components in the program. (Please see RunSQL procedure in my
previous reply).

<<Is it a disk-based database or the "Memory" database ?>>
Table1 is a disk-based database table while Table_M is a memory table.

Ralf,

<<Try FastMM or DBISAMMM and see if there is any difference.??

No difference.

Abdulaziz,

According to the help file, "Call the DeleteTable method to delete an existing table.  A
table must be closed before this method can be called."  I don't see an oTable.Close in
your procedure--maybe the memory is still in use when you Free the table and it gets
orphaned?  (Though I wonder why DBISAM or FastMM aren't throwing exceptions if that's the
case).

Charles Tyson

Charles,

<<Charles,According to the help file, "Call the DeleteTable method to delete an existing table.  A
table must be closed before this method can be called."  I don't see an oTable.Close in
your procedure--maybe the memory is still in use when you Free the table and it gets
orphaned?  (Though I wonder why DBISAM or FastMM aren't throwing exceptions if that's the
case).>>

I don't close the table because it is not opened, otherwise the "DeleteTable" method would have giving me some errors.

You might try inserting this statement after each RunSQL():

    SetProcessWorkingSetSize( GetCurrentProcess, DWORD(-1) , DWORD(-1) );  // or
$FFFFFFFF, $FFFFFFFF

This is supposed to force Windows to rethink how much RAM the application actually
needs--it has the same effect as minimizing & maximizing the application.  A lot of Delphi
experts say that calling this procedure is useless window dressing (Windows ought to be
taking care of low-memory situations on its own), but it doesn't seem to be dangerous or
cost much execution time.