strange behaviour TMyTable

[arthurhabraken]

I notice, IMHO very strange behaviour when posting a record to a table. The situation is as follows.

I have a table with a respectable number of 13 million records. I need to load more records in that table and I'm using a pretty straightforward way of doing that :

TMyTable.Append;
TMyTable.FieldByName('FIELD1').AsString := 'FIELD1';
TMyTable.FieldByName('FIELD2').AsString := 'FIELD2';
TMyTable.FieldByName('FIELD3').AsString := 'FIELD3';
TMyTable.Post;

This code raises an out-of-memory-exception on the post. It seems that when I post a record, the TMyTable is reading all of the records in the table to finally append a new record. Since I have so many records in that table already, I get an out of memory since the memory needed exceeds the Windows XP addressable memory limit. I can see this through the Task Manager.

I am using MyDac version 5.5 on Delphi 2007, MySQL version 5.1.36. I just downloaded the latest version of MyDac, giving me the same results.

I will try using SQL-statements to insert records, but I need to change an application hat currently uses Paradox tables, to use MySQl as the database. And that application is using a lot of FIELDBYNAME-code etc.

How can I solve this problem ?

[Dimon]

Try to set the TMyTable.Limit property. You can find more detailed information about this property in the MyDAC help.

[arthurhabraken]

I already had that property set to 2, but that doesn't work. What helped was adding 'WHERE 1=0' to the SQL-property, so no record was queried at all. That helped in the way that TMyTable didn't load the existing records.

But still, every appended record seems to be kept in memory.

[Dimon]

That appended record is not kept in memory, call Refresh after appending every record.

[arthurhabraken]

That appended record is not kept in memory, call Refresh after appending every record.

I see that as a workaround; a refresh will query the server, if I use the (not working) limit-property or not. Even with my own workaround, adding 'where 1=0'to the SQL-property will still query the server. Normally that doesn't matter. But my current import-file contains 22 million records, and that is one of 9, maybe 10 files I have to import. Calling refresh after every append will cost me too much time.

If that appended record is not kept in memory, can you explain why the memory use of my application, which I monitor using the task-manager, is growing on every post ?

[Dimon]

If you simply use the Append method in TMyTable then appended record will be kept in memory and you can't avoid this.
To solve the problem use the TMyQuery component and set the SQL property to the INSERT SQL query.
Or you can use the TMyLoader component for fast data loading to the server.

[arthurhabraken]

So, after all you're saying that an appended record using the append-method IS kept in memory !! I may not be able to avoid this, but YOU can !!

I consider this behaviour as a bug, because it will crash when the computer's memory is full. And is a database not invented to get data out of computer's memory ? I don't want appended records in the memory, I want it in the database, that's why I'm using MySQL AND your components.

I suggest you modify TMyTable's code that it doesn't store the appended record in memory, or add a property to give the user the possibility to choose to have an appended record in memory or not.

Is it not for my dislike of SQL, then it is for being compatible with Delphi-code...

Kind regards,
Arthur

[Dimon]

If you simply use the Append method in TMyTable then appended record will be kept in memory and you can't avoid this.

Such behaviour is used in all standard datasets, like BDE's TTable or ADO's TADOTable.