Debugging JavaDB Query Performance

— turn on RUNTIMESTATISTICS for connection:
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);

— Indicate that statistics information should be captured into
— database tables in the SomeSchema schema:
CALL SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA(‘SomeSchema’);

—execute  the queries:
SELECT * FROM <SomeTable> WHERE someName = ‘XYZ’ ;

–turn off runtime statistics:
VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
–Retrieve the text of statements which were captured, in order by the time when the statistics were captured:
select stmt_text, xplain_time from SomeSchema.sysxplain_statements    order by xplain_time;

–Retrieve the text of statements which were captured, showing the statements which took the longest time to execute first:
select s.stmt_text, st.execute_time from SomeSchema.sysxplain_statements s, SomeSchema.sysxplain_statement_timings st where s.timing_id = st.timing_id    order by st.execute_time desc

–Show the statements that were executed, together with the result sets that each statement required:
select st.stmt_text, rs.op_identifier from SomeSchema.sysxplain_statements st  join SomeSchema.sysxplain_resultsets rs  on st.stmt_id = rs.stmt_id

–Find statements which resulted in an external sort being performed:
select s.stmt_text, s.stmt_id, rs.op_identifier, srt.no_input_rows from SomeSchema.sysxplain_sort_props srt, SomeSchema.sysxplain_resultsets rs, SomeSchema.sysxplain_statements s where rs.stmt_id = s.stmt_id and rs.sort_rs_id = srt.sort_rs_id and srt.sort_type = ‘EX’

–Find statements which resulted in a tablescan:
select st.stmt_text, sp.no_visited_pages, sp.no_visited_rows from SomeSchema.sysxplain_scan_props sp, SomeSchema.sysxplain_resultsets rs, SomeSchema.sysxplain_statements st where st.stmt_id = rs.stmt_id and  rs.scan_rs_id = sp.scan_rs_id and  rs.op_identifier = ‘TABLESCAN’ and sp.scan_object_name = ‘<SomeTable>’

Advertisements

Tracing Derby SQL statements

I am working on a project which has a derby db alias apache derby alias javadb as one of the components which stores the configurations (javadb is suitable for in-memory operations for small no. of clients). The application which accesses the derby db has high response times.
To troubleshoot the high response times, I have enabled tracing of sql statements that are fired from the application.

  • Created derby.properties file and added a property “derby.language.logStatementText=true”
  • Restarted the application server including the derby database.

After that, all the sql statements will be logged into derby.log file.

Hope it helps

Note: tracing enables lots of logging of sql statements. Be careful while  dealing with the logs that are generated due to tracing

Connecting to remote Derby Database from Windows

One of my application uses Derby Database to store configuration information. I just wanted to see what tables are present in derby db. As I was using Derby for the first time, I did not know the basic commands to check the list of tables from the   linux system (where my derby db has been installed) sql prompt. I wanted to have a SQL Client which can connect to the remote db and then list the available tables and the data in it. This is when I started googling for SQL Client.  I found the suitable SQL client installer (squirrel-sql-3.7.1-standard.jar).

Installing and configuring the SQL Client:

  • Run the command “java -jar squirrel-sql-3.7.1-standard.jar” a gui would open and  click on next.
  • Download Apace Derby (db-derby-10.10.2.0-bin.zip)
  • Unzip the the derby zip  file to a folder.
  • Click on the “squirrel-sql.bat” file from the location where it is installed, it would open “SQuirreL SQL Client Version 3.7.1” GUI Editor.
  • Under Drivers tab on the top left of the editor, click on drivers and then double click  on Apache Derby Client.
  • Click on the “Extra Class Path” tab on the new window and add the derbyclient.jar from the folder where we have unzipped the derby package previously. Click OK. The driver would be registered.
  • Configure-Derby
  • Click on Alias, and then “add Alias” and input  the required details. TEST the connection and click on OK.
  • addalias

Hope it helps.