SELECT COUNT(s.id), c.id FROM tomcat_sessions s, tomcat_sessions c GROUP BY c.id;
While this query is a clever way to get the count back as part of the query, it does not scale well to larger numbers of sessions. I had about 5000 sessions in a moderately loaded system with a MySQL database and this query would take about 80 seconds to run by hand because of the N-squared nature of joining the table with itself.
This much simpler query is not as clever but gets the job done much more efficiently (it only takes 0.00 seconds according to mysql):
SELECT id FROM tomcat_sessions;
It means a little more work determining the number of IDs after the query runs but it's more than worth it.
Attached is the patch file.
~Tom
Index: JDBCStore.java
===================================================================
RCS file:
/home/cvspublic/jakarta-tomcat-4.0/catalina/src/share/org/apache/catalina/session/JDBCStore.java,v
retrieving revision 1.8
diff -u -r1.8 JDBCStore.java
--- JDBCStore.java 9 Dec 2002 15:05:55 -0000 1.8
+++ JDBCStore.java 4 Feb 2003 21:37:19 -0000
@@ -8,7 +8,7 @@
*
* The Apache Software License, Version 1.1
*
- * Copyright (c) 1999 The Apache Software Foundation. All rights
+ * Copyright (c) 1999,2003 The Apache Software Foundation. All rights
* reserved.
*
* Redistribution and use in source and binary forms, with or without
@@ -80,6 +80,7 @@
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.util.ArrayList;
import org.apache.catalina.Container;
import org.apache.catalina.LifecycleException;
import org.apache.catalina.Loader;
@@ -388,9 +389,7 @@
*/
public String[] keys() throws IOException {
String keysSql =
- "SELECT COUNT(s."+sessionIdCol+"), c."+sessionIdCol+
- " FROM "+sessionTable+" s, "+sessionTable+" c"+
- " GROUP BY c."+sessionIdCol;
+ "SELECT "+sessionIdCol+" FROM "+sessionTable;
Connection _conn = getConnection();
ResultSet rst = null;
@@ -405,13 +404,13 @@
preparedKeysSql = _conn.prepareStatement(keysSql);
rst = preparedKeysSql.executeQuery();
- if (rst != null && rst.next()) {
- keys = new String[rst.getInt(1)];
- keys[0] = rst.getString(2);
- i=1;
-
- while(rst.next())
- keys[i++] = rst.getString(2);
+ if (rst != null) {
+ ArrayList ids = new ArrayList();
+ while(rst.next()) {
+ String id = rst.getString(1);
+ ids.add(id);
+ }
+ keys = (String[])ids.toArray(new String[ids.size()]);
} else {
keys = new String[0];
}--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
