I like this, particularly if it helps.
> -----Original Message-----
> From: Noel J. Bergman [mailto:[EMAIL PROTECTED]]
> Sent: 14 February 2003 07:25
> To: James Developers List
> Subject: RE: Improving JDBC Spool responsiveness
>
>
> Serge,
>
> Yes, using Statement.setMaxRows(int max) is probably the right
> thing to do.
> In the MySQL Connector/J drivers it costs an extra message to the server,
> I'm not sure if PostgreSQL handles it at the server or the client, and who
> knows what other drivers do, but setMaxRows is standard in the API, and
> LIMIT isn't supported in the query language by at least Oracle and SQL
> Server.
>
> Thanks. Good point.
>
> What would you think of changing the spool config in config.xml as:
>
> <config>
> <sqlFile>file://conf/sqlResources.xml</sqlFile>
> <filestore>file://var/dbmail</filestore>
> <limit> N </limit>
> </config>
>
> "Limit" could be renamed cachesize, maxrows, prefetch, or
> whateverwewanttocallit, but the value would be kept and used in
> loadPendingMessages():
>
> listMessages =
> conn.prepareStatement(sqlQueries.getSqlString("listMessagesSQL",
> true));
> listMessages.setString(1, repositoryName);
> listMessages.setMaxRows(limit);
> rsListMessages = listMessages.executeQuery();
>
> while (rsListMessages.next() && pendingMessages.size() < limit && ...)
> {...}
>
> Thoughts?
>
> --- Noel
>
> -----Original Message-----
> From: Serge Knystautas [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, February 13, 2003 3:10
> To: James Developers List
> Subject: Re: Improving JDBC Spool responsiveness
>
>
> Noel J. Bergman wrote:
> >>It consists in appending to the listMessagesSQL query from
> >>sqlResources.xml a LIMIT xxx. This way, MySQL does not send
> >>back a huge ResultSet which makes the JDBC connection to expire
> >
> >
> > Good thought regarding LIMIT. There is code in JDBCSpoolRepository for
> > limiting the number of messages loaded into an internal working set, but
> the
> > SQL query still has to generate the large result set. There are two
> places
> > where we use listMessageSQL:
> >
> > JDBCMailRepository.list()
> > JDBCSpoolReposittory.loadPendingMessages()
> >
> > The former is only used by the POP3 handler when listing the contents of
> the
> > user's mailbox. The latter is used internally to load the
> working set. I
> > was thinking that perhaps it doesn't make sense to limit the
> list given to
> > the POP3 handler, although it would simply require the user to clear out
> > their messages before they could retrieve more of them. Or I
> could clone
> > the listMessagesSQL to separate the queries, which is probably
> a good idea
> > for other reasons.
>
> Another approach is to use Statement.setMaxRows(int max). Many JDBC
> drivers can then figure out how to execute this so you achieve the goal
> of not returning all the records.
>
> --
> Serge Knystautas
> President
> Lokitech >> software . strategy . design >> http://www.lokitech.com/
> p. 1.301.656.5501
> e. [EMAIL PROTECTED]
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
