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]
