Professional Web Applications Themes

"Progressive fetch" - MySQL

When I execute a query that returns a quite huge quantity of rows, it seems mysql sends ALL the data to the client before that client is able to loop over the result set. This acts the same with e.g Perl DBI's execute(), or PHP's mysql_query(), so this looks like it has something to do with the server's behaviour. I was wondering if there was a way with mysql to do some kind of "progressive fetch" so that I do not end up with a script that uses 3GB of memory? Is it actually something that one cannot change, that ...

  1. #1

    Default "Progressive fetch"

    When I execute a query that returns a quite huge quantity of rows, it
    seems mysql sends ALL the data to the client before that client is able
    to loop over the result set.

    This acts the same with e.g Perl DBI's execute(), or PHP's
    mysql_query(), so this looks like it has something to do with the
    server's behaviour.

    I was wondering if there was a way with mysql to do some kind of
    "progressive fetch" so that I do not end up with a script that uses 3GB
    of memory? Is it actually something that one cannot change, that has to
    be changed in the server setup, or in the client?

    Regards,
    Renaud

    Renaud Guest

  2. #2

    Default Re: "Progressive fetch"

    "Renaud" <com> wrote:
     

    This can be chosen for each single statement:

    http://dev.mysql.com/doc/refman/5.0/en/mysql-store-result.html
    vs.
    http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html

    Both PHP and DBD have bindings to chose whether mysql_store_result()
    or mysql_use_result() is used to retrieve a result set. The former is
    the default because a table becomes read-locked as long as a result
    set is being "used" by a client.

    Beginning with 5.0 you can use server side cursers to materialize the
    result set in the server (without locking the underlying table(s)).


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  3. #3

    Default Re: "Progressive fetch"

    Thanks so much! I can now get rid of that 5GB temp swapfile... :)

    Renaud

    Axel Schwenke wrote: 
    >
    > This can be chosen for each single statement:
    >
    > http://dev.mysql.com/doc/refman/5.0/en/mysql-store-result.html
    > vs.
    > http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html
    >
    > Both PHP and DBD have bindings to chose whether mysql_store_result()
    > or mysql_use_result() is used to retrieve a result set. The former is
    > the default because a table becomes read-locked as long as a result
    > set is being "used" by a client.
    >
    > Beginning with 5.0 you can use server side cursers to materialize the
    > result set in the server (without locking the underlying table(s)).
    >
    >
    > XL
    > --
    > Axel Schwenke, Senior Software Developer, MySQL AB
    >
    > Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    > MySQL User Forums: http://forums.mysql.com/[/ref]

    Renaud Guest

  4. #4

    Default Re: "Progressive fetch"

    One more thing,
    I had the same problem. If you do NOT want to fetch all results at once
    but n-by-n rows you can do the following:
    1.Set the following 2 attributes:


    1.1:
    unsigned long type= (unsigned long) CURSOR_TYPE_READ_ONLY;
    mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (void*) &type);
    AND
    unsigned long prefetch_rows = 5;
    1.2: mysql_stmt_attr_set(stmt, STMT_ATTR_PREFETCH_ROWS,(void*)
    &prefetch_rows);

    2. DO NOT call mysql_stmt_store_result(), because the entire result set
    will be passed to the client. Instead do mysql_stmt_fetch() ,which will
    fetch 5 rows at a time.

    3. If you want to know how many rows are left, i.e. if that is the end
    of the query result do:
    mysql_stmt_num_rows, which will return how many rows are actually
    fetched to the client.

    This way you can allocate smaller space and fetch until EOF is reached.
    One thing to mark ,though. Be careful with BLOB/TEXT fields and the
    stmt_functions() , which return RESULT metadata. If you use Cursor, the
    field maxlength/length will NOT be set correctly.

    Cheers,
    Dragomir Stanchev
    http://www.linkedin.com/in/dragomirstanchev

    Renaud wrote: 
    > >
    > > This can be chosen for each single statement:
    > >
    > > http://dev.mysql.com/doc/refman/5.0/en/mysql-store-result.html
    > > vs.
    > > http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html
    > >
    > > Both PHP and DBD have bindings to chose whether mysql_store_result()
    > > or mysql_use_result() is used to retrieve a result set. The former is
    > > the default because a table becomes read-locked as long as a result
    > > set is being "used" by a client.
    > >
    > > Beginning with 5.0 you can use server side cursers to materialize the
    > > result set in the server (without locking the underlying table(s)).
    > >
    > >
    > > XL
    > > --
    > > Axel Schwenke, Senior Software Developer, MySQL AB
    > >
    > > Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    > > MySQL User Forums: http://forums.mysql.com/[/ref][/ref]

    The|Godfather Guest

Similar Threads

  1. Replies: 2
    Last Post: November 22nd, 08:53 PM
  2. Replies: 0
    Last Post: November 22nd, 05:58 PM
  3. Replies: 0
    Last Post: May 24th, 07:35 PM
  4. #23936 [Fbk->Opn]: fail to "select and fetch"
    By sarusij at yahoo dot com in forum PHP Development
    Replies: 0
    Last Post: July 10th, 04:28 PM
  5. #23936 [Opn->Fbk]: fail to "select and fetch"
    By sniper@php.net in forum PHP Development
    Replies: 0
    Last Post: July 10th, 03:03 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139