OK, Shawn, nice tip. I really didn't know if it was actually performed by server or by client. But I'll study the MySQL client protocols.
But it is still strange and needs investigation, because as I remember, I've submit a "prove fire" to the oracle server. The prove was: I've sent a really really really heavy query, containing several tables cartesian joins, resulting in too many data (billions of billions of records), and we've imaginate it could spend at least many hours. For my surprising, the server reponse was imediate, with a only first page. Do you think that the server continues the actual processing until the end, despite no more pages being requested anymore? My Regards FM -----Mensagem original----- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviada em: sexta-feira, 4 de novembro de 2005 02:06 Para: Fabricio Mota Cc: mysql@lists.mysql.com Assunto: Re: RES: Delivery by Demand Yes, it is a client-side behavior to the extent that the MySQL server does not "page" through data. It gets the complete results unless you ask for a LIMIT, then it stops building results after it meets the criteria of your LIMIT. I really do not know much about Oracle administration and communication protocols so I am just guessing.....I believe that even your Oracle clients had to ask for data in "pages" instead of the full set. Are you sure your Oracle server was really "holding" those results for you and only delivering batches of 100 records? That seems very much like a client-side behavior that was just hard for you to notice. It could have been designed as part of the client library.... As I said, I just don't know but I know others on the list have had some extensive Oracle experience. Maybe one of them can weigh in on this.... With MySQL, the behavior you want to emulate is definitely something you control from the client-end either by using the LIMIT clause or by pulling down single rows in batches. You have to remember, though, that while the client is processing it's latest "batch" of rows that the server still holds onto a complete result set and has to maintain an open connection to your client. It's really in the best interest of performance for your client to spend as little time getting the data from the server. That means that you should only write queries that ask for the data you actually need and you should get the data out of the server as soon as possible. That way the server has more resources available to deal with other queries. Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Fabricio Mota" <[EMAIL PROTECTED]> wrote on 11/03/2005 10:52:34 PM: > Shawn, > > So are you telling me that it's a configuration in Client, but not in > Server? > > FM > -----Mensagem original----- > De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Enviada em: quarta-feira, 2 de novembro de 2005 17:37 > Para: Fabricio Mota > Cc: mysql@lists.mysql.com > Assunto: Re: Delivery by Demand > > > > > "Fabricio Mota" <[EMAIL PROTECTED]> wrote on 11/02/2005 10:23:46 AM: > > > Hi all, > > > > In the past, I worked as a Oracle user. I've noted that in oracle (or > maybe > > in that configuration), when we request a great amount of data, such > like: > > > > select * from million_records_table > > > > It does not delivers the entire table at the first moment. It delivers a > > little amount of data - such like a single page containing about 100 > > records - and awaits the cursor request the Record No 101 for fetch the > next > > set of data. > > > > In MySQL - at least, with the default configuration I have used - it > does > > not happens. It delivers all the million of records existent in the > table, > > inconditionally. I know that there is the clause LIMIT <N>, to limit the > > first N records existing in the query, but is there a way to warrant a > > "delivery by demand", such Oracle does, without to have to alter the > > application's SQL code? > > > > Thank you > > I know that in the C-API (and others) there are two commands to retrieve > records from the server. One is mysql_store_result() which will bring all of > your results into your machine in a single pull. > > The second is mysql_use_result(). That command sets up a transfer process > of pulling the rows from the server one at a time. If you need 100 rows of > data, you issue 100 mysql_fetch_row() commands. You are responsible for > storing the records for later re-use. > > Is that the flexibility you are looking for? > > http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine
