Using a CURSOR to retrieve data

BIZ TMS Aurelius
1

I want do dump a master detail table into an XML structure. Table master has 20.000 records, tabledetail has 120 records each. Using a cursor to retrieve the master recs and a criteria to get the details, I tried to walk thru the data., However, I ran into memory issues (out of memory). The data (about 200 bytes each rec) gets dumped into a TStreamWriter and a file, but the problem persits if I dump only the masters data.
Additionally perfomance is quite poor, because every cursor.next issues a new sql statement. When I changed retrieving the data to use a TCriteria to get the whole table as a List, I have no memory problems. The app is not leaking memory, so I assume that usinga cursor somehow uses memory.

Does this make sense? It is possible, that using a cursor uses all that memory? Or is the TStreamWrite allocating memory?

tx for an answer!

2

No, same problem when using TList. Iterating over the table craetes entries in the Objectmanager for every record thus leads to OutOfMemory.

How can I traverse the table without having each record as an entry in the objectmanager?

3

Ah, maybe paging the result is an option?

4

Yes, it's better you page the results so after you processed a page, you discard them (destroying and recreating the TObjectManager, for example) to free memory you won't use anymore.
Luckily paging the result with Aurelius is rather simple, just use Take and Skip in your criteria:
https://download.tmssoftware.com/business/aurelius/doc/web/paging_results.html

5

Yes, paging really is easy. I had to figure out, how to free the memory, but now it´s working.

BTW: What does TobjectManager.Clear do?

Additionally: Is there any way to use Aurelius without having every object in memeory? Like my example: I just want to iterate over a table. Can I release an object after I have used it?

And one more: Using a cursor is far more slow than retrieving via Criteria.List. I think this because of the SQL statement for every record. What are your experiences or recommondations for that?

Thanks for your excellence support!

Bernd

6

For now there is not a stateless object manager. So every object loaded by the manager "stays inside" it.

You can use TObjectManager.Clear exactly to remove those objects from the manager (and destroy them), so the manager discards what has been already loaded.

Retrieving data from a cursor should not be slower than from the list. It doesn't execute SQL statement for every record, unless you have proxied associated objects/lists that are being retrieved in lazy mode. But that would be the same case for either cursor or list.