TStoredProc; TOracleDataSet, TOracleQuery

[cosmini]

hello all,

I have created a small benchmark app for my company to determine if DOA components are faster then BDE counterparts. We make extensive use of packages and cursor based packages. Here are the times benchmarks to open/traverse a 50,000 recordset through a TStoredProc vs a TOracleDataSet:

TStoredProc:
Open: 20 milliseconds
Traverse: 5167 milliseconds

TOracleDataSet
Open: 5700 milliseconds
Traverse: 140 milliseconds

Any pointers on how to improve both the Open and Traverse times, if using DOA!?

Also, when I tried to use a TOracleQuery for the cursor based package I got: "Cursor Variable :CCUR cannot be nil." Any clues?

Thanks much,

Cosmin

 

[cosmini]

...getting the TOracleQuery to work, I got:

TOracleQuery:
Open: 10 milliseconds
Traverse: 4897 milliseconds

which is pretty much, the same speed as the TStoredProc. For ease of use, I'd still want to use a TOracleDataSet if possible...

thanks for any feedback on these issues.

Cosmin

 

[Marco Kalter]

The time to open a TOracleDataSet with a result set of 50,000 records can be decreased by setting QueryAllRecords to False. Note that the TOracleDataSet was not really designed to perform batch operations on large result sets. You should probably use the TOracleQuery.

The time to traverse the result set can be improved by setting RecordBuffer to a larger values than the default of 25. This determines the number of Client/Server roundtrips, which can greatly affect performance in certain circumstances. Try setting it to 100. This will reduce the number of roundtrips from 2,000 to 500.

------------------
Marco Kalter
Allround Automations

 

[cosmini]

hello there Marco,

per your advice, I've set ReadBuffer to 1000, QueryAllRecords (also CountAllRecords) to False and here's what I'm getting:

TStoredProc:
Open: 40 ms
Traverse: 7981 ms

TOracleDataSet:
Open: 70ms
Traverse 3746

Looks like the Traverse time is better then 50% which is great. Now, can something be done about the Open (Active:=True) as that seems to be about 50% higher then the BDE counterpart...

Thanks much,

Cosmin

 

[Marco Kalter]

When the dataset is opened, it already fetches the first 1000 records. What is more important, the time to open or the time to traverse the dataset? You need to find a compromise.

Note that it is still a good idea to use a TOracleQuery when processing 50,000 records.

------------------
Marco Kalter
Allround Automations