Re: [metabase-dev] RE: [PEAR-DEV] numRows in the oracle driver

Hello, On 03/10/2003 07:25 AM, Lukas Smith wrote:

I don't have a preference, I am just a bit afraid that solution 1)

might

take a lot of memory.

Well that is another story. Currently MDB reads in all the data into an array and then returns the data as needed. This is of course horrible for larger result sets. I am also investigating this. A client of mine

Memory is not the greatest problem because when you buffer rows in advance to count all rows and then get the rows to your application variables all you are doing is increasing the reference count to the same data, not exactly reallocating row memory multiple times. Keep in mind that when you use mysql_query(), MySQL API also buffers all rows in memory, that is why you can obtain the number of rows with mysql_num_rows(). The greatest problem of buffering rows before you can use them is that you loose an opportunity to fetch the rows in parallel while the server is still retrieving them from the database.

is using the oracle driver and right now he is at down to 60% performance versus the native API in the worst cases, which is surprisingly good from my POV. This is all due to the Metabase heritage. There Manuel probably did things like he did because he was doing per "cell" fetches and not "bulk" fetches.

No, the reason why it is done that way is because users want to have a function that tells them how many rows are in the result set before start processing it. That is a bad habit supported by MySQL API but many users can't live without it because it is very convinient. The way to make it work like with MySQL is to buffer the result set rows so you can count the rows that are indeed in the result set. Trying to do a separate SELECT to count the rows is often not the same thing because between two SELECT the actual row count may change (due to possible concurrent inserts) unless you encapsulate the two SELECT in a transaction. I would not advise that solution because it would not be faster and would eventually introduce complications that I don't think you want to deal with. -- Regards, Manuel Lemos