Professional Web Applications Themes

select * from bigTable, with JDBC - MySQL

For batch processing I need to process all rows in a table. If the table is too big, this leads to an OutOfMemoryException. I've found this article as an solution: http://bugs.mysql.com/bug.php?id=7698 with using stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWA RD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); The problem is, that while streaming the table is locked. The article says: "This is going to be fixed in MySQL 5.0". I assume that the "select * from bigTable" was meant, but I've tried it with MySQL 5.0.32 and it still throws the OutOfMemoryException, when trying without the two lines above (with the latest JConnector). So my question is: How ...

  1. #1

    Default select * from bigTable, with JDBC

    For batch processing I need to process all rows in a table. If the table is
    too big, this leads to an OutOfMemoryException. I've found this article as
    an solution:

    http://bugs.mysql.com/bug.php?id=7698

    with using

    stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWA RD_ONLY,
    java.sql.ResultSet.CONCUR_READ_ONLY);
    stmt.setFetchSize(Integer.MIN_VALUE);

    The problem is, that while streaming the table is locked. The article says:
    "This is going to be fixed in MySQL 5.0". I assume that the "select * from
    bigTable" was meant, but I've tried it with MySQL 5.0.32 and it still
    throws the OutOfMemoryException, when trying without the two lines above
    (with the latest JConnector).

    So my question is: How can I iterate all rows of a big table, without
    locking the table (allowing parallel read/write), with JDBC?

    --
    Frank Buss, de
    http://www.frank-buss.de, http://www.it4-systems.de
    Frank Guest

  2. #2

    Default Re: select * from bigTable, with JDBC

    On Fri, 25 May 2007 11:15:13 +0200, Frank Buss <de>
    wrote:
     

    Piece by piece with a
    LIMIT n,m
    and iterate over
    n+=m ?
    subtenante Guest

  3. #3

    Default Re: select * from bigTable, with JDBC

    subtenante wrote:
     

    This could fix the OutOfMemory problem, but how long will the table be
    locked, when using e.g. LIMIT 10000000, 100 and how much time needs the
    database to skip x times for each request the first n records?

    --
    Frank Buss, de
    http://www.frank-buss.de, http://www.it4-systems.de
    Frank Guest

  4. #4

    Default Re: select * from bigTable, with JDBC

    On Fri, 25 May 2007 11:35:39 +0200, Frank Buss <de>
    wrote:
     
    >
    >This could fix the OutOfMemory problem, but how long will the table be
    >locked, when using e.g. LIMIT 10000000, 100 and how much time needs the
    >database to skip x times for each request the first n records?[/ref]

    Wait... Hmmm... my magic cristal ball is telling me... hmm... what ?
    Oops sorry, that was not my cristal ball.

    Give it a try ? See how many rows you have try to benchmark some data
    about how long it gets to treat one row, make a division.
    What is the engine of your tables ?, are you sure the whole table will
    be locked and not only selected rows ?

    I'm trying t o help but you don't give much hints.
    subtenante Guest

Similar Threads

  1. Slow mySQL select - mySQL/JDBC/CFMX issue?
    By funkymusic_austria in forum Coldfusion Database Access
    Replies: 5
    Last Post: July 17th, 07:19 AM
  2. Replies: 4
    Last Post: April 4th, 08:21 AM
  3. Replies: 0
    Last Post: September 24th, 03:24 AM
  4. Replies: 0
    Last Post: April 15th, 01:22 PM

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