RecordCount & QueryAllRecords = False

[taylorae]

When QueryAllRecords = False, RecordCount still returns the number of rows that *would* be returned, which is fine. However, is there a mechanism that indicates the number of rows that have been returned so far?

 

[Marco Kalter]

There is no property or function that indicates the number of records that are fetched, but you can use the AfterFetchRecord event to increment a counter, which needs to be reset in the BeforeOpen event.

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

 

[taylorae]

Hi Marco,

When doing this, I notice some strange behaviour. If I set ReadBuffer = 100 and the DataSet is hooked up to a grid that displays 50 rows at a time, the counter in AfterFetchRecord initially shows 50. Shouldn't this be 100?

My users are running some very large queries, so I need to perform some real-time metrics to prevent out-of-memory errors. I need to know how much data is actually sitting in memory at any point in time.

Regards,
Alan

Originally posted by mkalter:
There is no property or function that indicates the number of records that are fetched, but you can use the AfterFetchRecord event to increment a counter, which needs to be reset in the BeforeOpen event.

 

[Marco Kalter]

It's your counter, isn't it? The initial value is controlled by you.

In any case, AfterFetchRecord fires for every record that is fetched, and the value of ReadBuffer is irrelevant.

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

 

[taylorae]

OK, maybe I didn't make myself clear...

I set ReadBuffer = 100.

I have the DataSet connected to a grid that displays 50 rows at a time.

AfterFetchRecord increments a counter so that I can track how many rows have been physically retrieved from the database. The counter is initialised to 0 and incremented each time the event fires.

When I open the dataset, I would expect 100 rows to be retrieved, due to the ReadBuffer setting.

If I hit PageDown, I would expect the AfterFetchRecord counter to remain at 100, since I have already displayed the first 50 rows and now I'm displaying the second 50 rows so there's no need to retrieve anything.

If I hit PageDown again, I would expect the AfterFetchRecord counter to then increase to 200, showing the third block of 50 and cacheing another 50.

This doesn't appear to be the behaviour that actually occurs. 50 rows are retrieved when the DataSet is opened and subsequent rows are retrieved when they are needed, ignoring the ReadBuffer setting.

According to the help file, ReadBuffer is the 'Number of rows that will be transferred across the network at once for select statements. This property can have a great impact on performance.'

Or am I missing something...?

 

[Marco Kalter]

The ReadBuffer property affects how many records are transferred from the Server to the Client at once. This process is completely transparent to the application though, and is only used for network traffic optimization.

If QueryAllRecords is False, the controls that are connected the the DataSource of the DataSet determine how many records are read from the dataset. A DBGrid may initially read 50 records, in which case the following happens:

[*]The DataSet is opened. No records are fetched, because QueryAllRecords is False.

[*]The DBGrid reads 50 records. At this point, 100 records (ReadBuffer) are fetched from the Server. The AfterFetchRecord event is fired 50 times.

[*]You press page down, and the DBGrid reads another 50 records from the DataSet. Nothing is fetched from the Server (we already have 100 records buffered), and AfterFetchRecord is fired another 50 times.

[*]If you press page down again, another 100 records are fetched from the Server, the DBGrid reads 50 records, and AfterFetchRecord is fired 50 times.

[*]And so on...
[/list]
I hope this helps.

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

 

[taylorae]

OK, that explains it. I thought AfterFetchRecord was being fired when the row was retrieved from the database.

Unfortunately, this doesn't help me to determine how much data is physically loaded into memory at any one time :-(

Thanks,
Alan

 

[Marco Kalter]

Basically the amount of memory used by the dataset is equal to the value of your AfterFetchRecord counter multiplied by the record size.

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

 

[taylorae]

Marco,

This doesn't agree with your earlier statement:

The DBGrid reads 50 records. At this point, 100 records (ReadBuffer) are fetched from the Server. The AfterFetchRecord event is fired 50 times.

Surely the amount of memory used in this case would be 100 * RecordSize. The AfterFetchRecord counter would still be set at 50, indicating only half the memory that is actually used?

Originally posted by mkalter:
Basically the amount of memory used by the dataset is equal to the value of your AfterFetchRecord counter multiplied by the record size.

 

[Marco Kalter]

Okay, let's be precise

. The amount of memory used is:

(ReadBuffer + YourRecordCounter) * RecordSize

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

 

[taylorae]

This still isn't correct

. The amount of memory used is:

(ReadBuffer * NumberOfTimesThatBufferHasBeenRead) * RecordSize

but there is no way of knowing how many times the ReadBuffer has been read.

In fact this is also inaccurate. RecordSize represents the maximum size that a record could be. For large queries on large tables containing VarChar2 columns it proves to be highly innacurate. To get around this, I run a pre-query to the effect:

SELECT
MAX(VSIZE(COLUMN_1))
,MAX(VSIZE(COLUMN_2))
etc...
FROM
TABLE;

then add the results together.

I apologise for being so pedantic, but I'm having serious memory problems with large result sets, and I have to get this right.

Originally posted by mkalter:
Okay, let's be precise

. The amount of memory used is:

(ReadBuffer + YourRecordCounter) * RecordSize

 

[Marco Kalter]

I think my previous formula is correct. The ReadBuffer is allocated just once, regardless of how many records you read. The dataset reads from this buffer, and adds records to its result set.

If you are having memory problems, you should consider using a TOracleQuery instead of a TOracleDataSet. Its memory usage is:

ReadBuffer * RecordSize

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