TOracleDataSet with millions of records

[Kristoffer]

We have a query that returns a large number of records and we would like to scroll through the results one way only but TOracleDataSet appears to be retaining previous records as I navigate via Next thus causing the computer to run out of memory.

Is there a way to tell TOracleDataSet not to retain previous records?

I am using the latest 3.x version. Our code runs significantly slower with 4.x so upgrading is not an option at this time.

 

[Marco Kalter]

In 3.4 you can only use a TOracleQuery instead of a TOracleDataSet for efficient batch processing of millions of records.

I'm curious when and how 4.0 is slower than 3.4 though. Can you shed some light on this?

 

[Kristoffer]

We have a batch oriented system that runs 4 threads all doing simultaneous work on different entities in the database. Each thread has its own connection. Just swapping out the 3.x component for 4.0 added 20% to our run-time. It has been a while since I profiled the section of code but if you are interested I can go back and re-do them to see where the problem lies.

 

[Marco Kalter]

That might be interesting, but I don't want to impose too much work on you. The only thing that has changed that is relevant to multi-treaded applications is that all sessions now share the same OCI environment. In 4.0 you can revert to the 3.4 situation where each session has its own environment by setting the global boolean variable UseSingleEnvironment to False. This variable is located in the OracleCI unit, so you may need to add that to the Uses clause. You need to set it before connecting any session.