List:General Discussion« Previous MessageNext Message »
From:Fabricio Mota Date:November 5 2005 2:06am
Subject:RES: RES: Delivery by Demand
View as plain text  
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: SGreen@stripped [mailto:SGreen@stripped]
  Enviada em: sexta-feira, 4 de novembro de 2005 02:06
  Para: Fabricio Mota
  Cc: mysql@stripped
  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" <fabricio.mota@stripped> 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: SGreen@stripped [mailto:SGreen@stripped]
  >   Enviada em: quarta-feira, 2 de novembro de 2005 17:37
  >   Para: Fabricio Mota
  >   Cc: mysql@stripped
  >   Assunto: Re: Delivery by Demand
  >
  >
  >
  >
  >   "Fabricio Mota" <fabricio.mota@stripped> 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

Thread
Checking MySQL securityfredzy padzy2 Nov
  • Delivery by DemandFabricio Mota2 Nov
    • Re: Delivery by DemandSGreen2 Nov
      • RES: Delivery by DemandFabricio Mota4 Nov
        • Re: RES: Delivery by DemandSGreen4 Nov
          • RES: RES: Delivery by DemandFabricio Mota5 Nov
            • Re: RES: RES: Delivery by DemandPaul DuBois5 Nov