How to show query plan and runtime statistic in Derby

The official websie (http://db.apache.org/derby/) has straightford description. I just try to highlight some features:

• an open source, under the Apache License, Version 2.0 .
• relational database
• entirely in Java
• 2 megabytes for the base engine and embedded JDBC driver
• embedded or client/server mode

check out the Quick Start page if you want to know more.

I will give my experience on check the query plan, runtime statistic, which is very useful to tune the performance.

Query Plan

To show the query plan, first create file named “derby.properties ” under derby “lib” directory, and put following lines:

derby.language.logQueryPlan=true
derby.optimizer.noTimeout=true
You must manully create the file! Then start derby ij command line [1], after run the query, the query plan is recorded in the “derby.log” file.The log will show you the detail on how the query engine analysis the user's query and optimize the query. You can find the the final query sequence and whether index is used or not and which one.

Runtime Statictic

But you can not get the real runtime for the query just display the query plan. Here is some way to show it:

In ij command

1. MaximumDisplayWidth 9999; (if you query is simple, ignor it. If complex, must set. For my case, even set it still not big enough. Try next two methods)
   
2. CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
3. CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
4. Run your statement (like select * from table; )
5. VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

Directly run java command

java -cp .;derby.jar;derbytools.jar java org.apache.derby.tools.ij my.sql > myoutput.txt

This will run a sql file and dump the result out to a file. Here is a sample "my.sql" file:

connect 'jdbc:derby:db;user=uncc;password=uncc';
MaximumDisplayWidth 9999;
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
select s.id, s.name, t.id,  t.name  from element as s, element as t, qgram as qs, qgram as qt where s.id = qs.element_id and t.id = qt.element_id and qs.qgram = qt.qgram and s.schema_id <> t.schema_id and abs(qs.pos - qt.pos) < 3 and abs(LENGTH(s.name)-LENGTH(t.name)) <= 3 group by s.id,  t.id,  s.name, t.name having count(*) >= LENGTH(s.name)-7 and count(*) >= LENGTH(t.name)-7 and myED(s.name, t.name) < 3;
VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

Note: I call a usre-defined function named "myED" in the script.

Using Java

	public static void main(String[] args){
		DatabaseConnection connection = new DatabaseConnection(DatabaseConnection.DERBY, "", "db",	"uncc", "uncc");		
		try {
			Statement stmt = connection.getStatement();
			stmt.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
			stmt.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1)");
			ResultSet rs = stmt
					.executeQuery("select s.id, s.name, t.id,  t.name  from element as s, element as t, qgram as qs, qgram as qt where s.id = qs.element_id and t.id = qt.element_id and qs.qgram = qt.qgram and s.schema_id <> t.schema_id and abs(qs.pos - qt.pos) < 3 and abs(LENGTH(s.name)-LENGTH(t.name)) <= 3 group by s.id,  t.id,  s.name, t.name having count(*) >= LENGTH(s.name)-7 and count(*) >= LENGTH(t.name)-7");
			//while (rs.next())
			//	System.out.println(rs.getInt("id")+" , "+rs.getString("name"));
			
			// retrieve query plan and run-time statistics
			rs = stmt.executeQuery("VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
			rs.next();
			String str = rs.getString(1);
			System.out.println("Query Plan: " + str);
			stmt.close();
		} catch (SQLException e) {
			System.out.println("(E) Database:getSchemas: " + e.getMessage());
		}
		
		System.out.print("Done...");
	}

Reference:

[1]http://db.apache.org/derby/integrate/plugin_help/ij_toc.html

[2]http://db.apache.org/derby/docs/dev/tools/ttoolsij98878.html

[3]http://db.apache.org/derby/docs/dev/tools/ttoolsij98878.html

[4]https://www.ibm.com/developerworks/forums/thread.jspa?threadID=122245&tstart=0&messageID=13833605

[5]http://home.online.no/~olmsan/publications/pres/javaone07/JavaDbJavaOne07.pdf