18 messages in com.mysql.lists.javaRE: general question about the JDBC d...| From | Sent On | Attachments |
|---|---|---|
| Lee Breisacher | 18 May 2004 13:54 | |
| Mark Matthews | 18 May 2004 14:03 | |
| Lee Breisacher | 18 May 2004 14:13 | |
| Mark Matthews | 18 May 2004 14:20 | |
| Lee Breisacher | 18 May 2004 16:12 | |
| Lee Breisacher | 18 May 2004 16:42 | |
| GV | 01 Dec 2004 23:52 | |
| Thomas Lundström | 02 Dec 2004 00:17 | |
| GV | 02 Dec 2004 01:45 | |
| GV | 02 Dec 2004 01:46 | |
| Sanjeet | 02 Dec 2004 01:51 | |
| Sanjeet | 02 Dec 2004 02:28 | |
| David Black | 02 Dec 2004 02:54 | |
| Rhino | 02 Dec 2004 03:00 | |
| Alec...@Quantel.Com | 02 Dec 2004 03:38 | |
| GV | 02 Dec 2004 04:21 | |
| Thomas Lundström | 02 Dec 2004 04:49 | |
| GV | 02 Dec 2004 07:09 |
| Subject: | RE: general question about the JDBC driver |
|---|---|
| From: | Thomas Lundström (zod...@home.se) |
| Date: | 12/02/2004 04:49:37 AM |
| List: | com.mysql.lists.java |
Hi GV....
Maybe I've missed what your problem really is but as it is right now with current MySQL servers and corresponding Connect J/JDBC drivers you either have to reduce the amount returned by your query in some fashion (and thus maybe executing several queries) to avoid Out-of-memory-exceptions OR to make sure your memory is sufficient use the "steaming method" as described bellow...
Unfortunately MySQL doesn't have proper support for the setFetchSize-method of JDBC until later versions arrive.
Read also: http://forums.mysql.com/read.php?39,6493,6493#msg-6493
Row-by-row access using the "streaming method" is time consuming but the only way IF you really need to fetch all data of a large table to your client. Usually there is another way of solving problems, possibly by creating temporary tables of aggregated data or something like that?
Maybe if you tell us more about the problem you've got?
Regards,
Thomas Lundström
---------------------------------------- Thomas Lundström mailto:zod...@home.se
-----Original Message----- From: GV [mailto:kp...@gmx.net] Sent: den 2 december 2004 13:22 Cc: ja...@lists.mysql.com Subject: Re: general question about the JDBC driver
that's the only solution? I mean doing this, I have to re-issue the same SQL statement as many times as necessary in order to fetch the whole resultset. I was hoping that there is a way to avoid doing it.
Thanks
On Thu, 2004-12-02 at 12:38, Alec...@Quantel.Com wrote:
"Rhino" <rhi...@sympatico.ca> wrote on 02/12/2004 11:00:25:
----- Original Message ----- From: "GV" <kp...@gmx.net> Cc: <ja...@lists.mysql.com> Sent: Thursday, December 02, 2004 2:53 AM Subject: general question about the JDBC driver
Hi there,
just a quick question! When using the MySQL JDBC driver, is it possible to retrieve (and load in the memory!) only parts of a resultset?
If I have large table scans, I want to be able to split a huge resultset in smaller portions in order to reduce the amount of the memory I need to store it. Is that possible?
I don't understand why you would want to do this. The whole idea of a query is to retrieve only the rows and columns of data you want to use. Otherwise, they wouldn't have bothered to develop all the different aspects of SQL that can be used to limit the rows and columns returned by a query: they would have just let everyone write "select * from tablename" and not bothered with the rest of the SELECT statement.
If you are not using all of the result set, it is probably because you are asking for more rows and/or columns than you need. Wouldn't it make more sense to refine your query to ask for only the rows/columns you will actually use?
Also, in terms of memory use, I don't know this for certain but I'd be surprised if an entire large result set were brought into memory at the same time; I assume that the next() is used to fetch only one row at a time into memory. Of course you should check that out with someone more familiar with the internals of the JDBC driver than me....
The default behaviour is to read the whole result into memory in one operation. This then allows you to jump around the ResultSet at random. In
order to get the "streaming" behaviour which you describe, and which I imagine is what GB wants, you have to do
Statement stmt = conn.createStatement (ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY) ; stmt.setFetchSize(10000); //Number of rows to get each time
which changes the behaviour to economise on memor in the client by reading
only a bit at a tim.
Alec