java.jdbc

Add some sort of explain option

Details

  • Type: Enhancement Enhancement
  • Status: Closed Closed
  • Priority: Major Major
  • Resolution: Completed
  • Affects Version/s: None
  • Fix Version/s: None
  • Component/s: None
  • Labels:
    None

Description

EXPLAIN is very useful for debugging SQL performance issues but it isn't very portable (EXPLAIN PLAN in Oracle, EXPLAIN EXTENDED in MySQL... SQL Server seems to only support command options before/after SQL statements?).

Is there some bare minimum we can provide, such that if an :explain? option is provided, we can persuade the underlying system to provide the execution plan (as well as potentially executing the query in question).

Current thinking:

  • :explain? - truthy enables explain processing, default is just "EXPLAIN " prepended to the SQL statement, but if :explain? is a string, that value would be prepended instead,
  • :explain-fn - post-processing for the explain data returned by the query (if any), the default would just be println.

Activity

Hide
Sean Corfield added a comment -

The above is basic and works, insofar as printing the execution plan to the console and not interrupting the flow of code. :explain-fn could be a logging function of some sort.

HSQLDB requires EXPLAIN PLAN FOR so those users would need to provide the string explicitly. The default string works for Postgres and MySQL (as well as H2 and SQLite, although the latter produces something that looks like assembler!). Apache Derby seems to ignore an EXPLAIN statement. MS SQL Server doesn't seem to have a SQL-level option for this (really? I tried EXPLAIN and DESCRIBE but neither worked).

I can imagine situations where being able to get the plan back as data might be useful for some databases but for now the printing / logging seems sufficient.

Show
Sean Corfield added a comment - The above is basic and works, insofar as printing the execution plan to the console and not interrupting the flow of code. :explain-fn could be a logging function of some sort. HSQLDB requires EXPLAIN PLAN FOR so those users would need to provide the string explicitly. The default string works for Postgres and MySQL (as well as H2 and SQLite, although the latter produces something that looks like assembler!). Apache Derby seems to ignore an EXPLAIN statement. MS SQL Server doesn't seem to have a SQL-level option for this (really? I tried EXPLAIN and DESCRIBE but neither worked). I can imagine situations where being able to get the plan back as data might be useful for some databases but for now the printing / logging seems sufficient.
Hide
Sean Corfield added a comment -

Will be in 0.6.2

Show
Sean Corfield added a comment - Will be in 0.6.2
Hide
Sean Corfield added a comment -

Releasing 0.6.2-alpha1 with this change.

Show
Sean Corfield added a comment - Releasing 0.6.2-alpha1 with this change.

People

Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: