Re: resultset is an iterator?
From: mistral (vlamos_playa_at_hotmail.com)
Date: 05/18/04
- Next message: Mark Matthews: "Re: resultset is an iterator?"
- Previous message: Chris Smith: "Re: resultset is an iterator?"
- In reply to: Mark Matthews: "Re: resultset is an iterator?"
- Next in thread: Mark Matthews: "Re: resultset is an iterator?"
- Reply: Mark Matthews: "Re: resultset is an iterator?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Tue, 18 May 2004 19:46:12 GMT
Mark Matthews wrote:
> Silvio Bierman wrote:
>
>> "mistral" <vlamos_playa@hotmail.com> wrote in message
>> news:kyrqc.1017$rl2.168234102@hebe.telenet-ops.be...
>>> Hello,
>>>
>>> 1)If a execute a statement : SELECT * FROM balaba.
>>> And I retrieve this statement as a resultset. Then is this result set
>>> totally filled with the table => the resultset contains the whole table
>>> blabla.
>>> OR is this just an interator and every time I do a next the database is
>>> contacted and the next record is retrieved.
>>>
>>> This is important because you don't want a whole db table in your memory
>> :)
>>>
>>> 2) Is the com.mysql.jdbc.resultset better than java.sql.resultset?
>>>
>>>
>>> Thanks !
>>>
>>>
>>> Tom
>>
>> Sorry Tom, that is completely dependent on the resultset implementation
>> done by the JDBC driver and the database backend. Any decent driver on a
>> database system supporting stream-like processing of resultsets will not
>> drag it all into memory but I have noticed that MySQL seems to do this. I
>> wonder if that is caused by an inability of the MySQL server to provide
>> resultsets as a stream.
>
> Silvio,
>
> MySQL can provide result sets as a stream, however, you can only have
> one open result set in this mode, and you can't issue any other queries
> on the connection until all of the rows are read. You tell the JDBC
> driver to do this with the following 'magic' (from the docs at
> http://dev.mysql.com/doc/connector/j/en/index.html):
>
> "By default, ResultSets are completely retrieved and stored in memory.
> In most cases this is the most efficient way to operate, and due to the
> design of the MySQL network protocol is easier to implement. If you are
> working with ResultSets that have a large number of rows or large
> values, and can not allocate heap space in your JVM for the memory
> required, you can tell the driver to 'stream' the results back one row
> at-a-time.
>
> To enable this functionality, you need to create a Statement instance in
> the following manner:
>
> stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
> java.sql.ResultSet.CONCUR_READ_ONLY);
> stmt.setFetchSize(Integer.MIN_VALUE);
>
> The combination of a forward-only, read-only result set, with a fetch
> size of Integer.MIN_VALUE serves as a signal to the driver to "stream"
> result sets row-by-row. After this any result sets created with the
> statement will be retrieved row-by-row.
>
> There are some caveats with this approach. You will have to read all of
> the rows in the result set (or close it) before you can issue any other
> queries on the connection, or an exception will be thrown. Also, any
> tables referenced by the query that created the streaming result will be
> locked until all of the results have been read or the connection closed."
>
>
> This will change once MySQL has cursors that are available from the
> client side.
>
> There are other drivers which do the same thing, SQLServer comes to
> mind. In 99% of cases, where result sets are small to medium size,
> reading them all off of the wire and caching them in memory is more
> efficient all-around, both because there's less network traffic (i.e. no
> messages back to the server saying 'send me more results'), and locks
> are released earlier (i.e. they don't depend on client processing time)
> thus leading to higher concurrency for _all_ clients connected to the
> database.
>
> It's only when you start talking about datawarehousing, ETL-type
> operations where 100's of thousands if not millions or billions of rows
> need to be transferred that this strategy starts to fall apart. However,
> if you're doing this kind of stuff, you might be better off using the
> respective bulk unload/load tools that your database provides, which
> would in most cases be more efficient anyways.
>
> -Mark
>
>
>
Ok very intresting,
This helps me a lot, the only major drawback is the table locking. But
indeed there is less network traffic & database requests if everything is
loaded into memory. But why is the table locked if the READ_ONLY option is
set? To prevent other queries to modify the table during reading ?
Tom
- Next message: Mark Matthews: "Re: resultset is an iterator?"
- Previous message: Chris Smith: "Re: resultset is an iterator?"
- In reply to: Mark Matthews: "Re: resultset is an iterator?"
- Next in thread: Mark Matthews: "Re: resultset is an iterator?"
- Reply: Mark Matthews: "Re: resultset is an iterator?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
