james-server-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Danny Angus" <da...@apache.org>
Subject RE: Improving JDBC Spool responsiveness
Date Fri, 14 Feb 2003 11:02:03 GMT
I like this, particularly if it helps.

> -----Original Message-----
> From: Noel J. Bergman [mailto:noel@devtech.com]
> 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:sergek@lokitech.com]
> 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. sergek@lokitech.com
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: james-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: james-dev-help@jakarta.apache.org
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: james-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: james-dev-help@jakarta.apache.org
> 
Mime
View raw message