Hi Thomas, Ah, your patch is nicer than my patch! Nice...
The auto-analyze performance problems I observe happen with a large database (~5GB), after a couple of hours of running, when the maximum available JVM heap space is close to exhausted. This is indeed not an everyday case... :) Regards, Steve --------------------------------------------------- Steve McLeod Founder, Poker Copilot http://www.pokercopilot.com On Nov 10, 6:34 am, Thomas Mueller <[email protected]> wrote: > Hi, > > If I understand correctly, H2 doesn't allow indexes on CLOB or BLOB > > > columns. > > Yes. > > > If I still understand correctly, column selectivity is used solely to > > calculate which index to use. > > Yes. > > The auto-analyze that H2 performs calculates the selectivity for CLOB > > > and BLOB columns. This seems of no use. > > That's true. > > > It seems to me that it can > > also cause a large H2 database to pause for a significant amount of > > time, causing unnecessary memory churn. > > That would be bad. The auto-analyze feature should only have very little > impact (I would say at most 5 percent in the normal case). > > > I've tested out this theory and concluded that auto-analyze is 10 to > > 20 times faster when running on a simple table containing two INT > > column and one CLOB field. > > That's weird. According to my test case, excluding the LOB columns has > little effect (about 15% for 2 columns, see my patch and test case below). > > Please note that calculating the selectivity doesn't (shouldn't) load the > whole LOB column in memory, as it uses the hash value to calculate the > selectivity. > > Is this optimisation worth implementing? > > > > I guess yes, even if it's only about 15%. > > Patch: > > ### Eclipse Workspace Patch 1.0 > #P h2 > Index: src/main/org/h2/command/ddl/Analyze.java > =================================================================== > --- src/main/org/h2/command/ddl/Analyze.java (revision 3980) > +++ src/main/org/h2/command/ddl/Analyze.java (working copy) > @@ -16,6 +16,7 @@ > import org.h2.table.RegularTable; > import org.h2.table.Table; > import org.h2.util.StatementBuilder; > +import org.h2.value.Value; > > /** > * This class represents the statement > @@ -82,7 +83,14 @@ > Column[] columns = table.getColumns(); > for (Column col : columns) { > buff.appendExceptFirst(", "); > - buff.append("SELECTIVITY(").append(col.getSQL()).append(')'); > + int type = col.getType(); > + if (type == Value.BLOB || type == Value.CLOB) { > + // can not index LOB columns, so calculating > + // the selectivity is not required > + buff.append("100"); > + } else { > + > buff.append("SELECTIVITY(").append(col.getSQL()).append(')'); > + } > } > buff.append(" FROM ").append(table.getSQL()); > if (sample > 0) { > > Test case: > > package db; > > import java.sql.Connection; > import java.sql.DriverManager; > import java.sql.Statement; > import org.h2.tools.DeleteDbFiles; > import org.h2.util.Profiler; > > public class TestSimpleDb { > > public static void main(String... args) > throws Exception { > > Class.forName("org.h2.Driver"); > DeleteDbFiles.execute("~/temp", null, true); > > final String url = "jdbc:h2:~/temp/test"; > Connection conn; > conn = DriverManager.getConnection(url); > Statement stat = conn.createStatement(); > stat.execute( > "create table test(id int primary key, data clob) " + > "as select x, space(100000) " + > "from system_range(1, 100)"); > Profiler prof = new Profiler(); > prof.interval = 1; > > // 3101 old > // 2688 LOB columns excluded > > // prof.startCollecting(); > long start = System.currentTimeMillis(); > for (int i = 0; i < 10000; i++) { > stat.execute("analyze"); > long t = System.currentTimeMillis() - start; > if (t > 1000) { > System.out.println("i: " + i); > break; > } > } > // System.out.println(prof.getTop(5)); > conn.close(); > > } > > } > > Regards, > Thomas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
