[Solved] Tracing SQL Commands

[dreamquartz]

Hi All,

I am looking for a way to trace what and how a SQL Query is running.
What does it do in what order et cetera.

I am trying to optimize the Queries, because some are so slow, even with a small Query.
Still running HSQLDB 1.8.

Thanks in advance.

Dream

Edit: topic went of track

[Sliderule]

I really do not know what you mean by "Tracing SQL commands".

It is important that you understand . . . you said you are using HSQL 1.8. as your database back-end, but, you did NOT say, if you are using it as an Embedded database or as an External database.

Short answer, the database back-end will determine the most efficient way to process a Query ( Select statement ). It, the database back-end, will use any indices ( indexes ) . . . if possible. If a query is taking too long ( I wish you could be more specific what you mean by that ) . . . perhaps you could add an Index, so, it will be able to optimize the query by an index.

Now, HSQL, whether Version 1.8 or, Version 2.3 both support the use of EXPLAIN PLAN FOR Select . . . that will show you exactly that information . . . BUT this command CANNOT ( is not supported ) be used within OpenOffice / LibreOffice Base. What I mean is . . . it can only be run external to OpenOffice / LibreOffice Base . . . because the output is NOT a result set.

Below is an example, run OUTSIDE of OpenOffice / LibreOffice Base, of the use of Explain Plan For . . . it shows the syntax of the command ( including the Select statement ) . . . and . . . the output generated by Explain Plan For :

Code: Select all

Explain Plan For Select * From bldtest, grptime Where grptime.group_time = bldtest.group_time and test_date between {D '1999-12-28'} and {D '2000-01-05'} Order By bldtest.test_day desc;

isDistinctSelect=[false]
isGrouped=[false]
isAggregated=[false]
columns=[  COLUMN: PUBLIC.BLDTEST.TEST_DATE not nullable
  COLUMN: PUBLIC.BLDTEST.TEST_TIME not nullable
  COLUMN: PUBLIC.BLDTEST.BLOOD_SUGAR not nullable
  COLUMN: PUBLIC.BLDTEST.GROUP_TIME not nullable
  COLUMN: PUBLIC.BLDTEST.TEST_DAY not nullable
  COLUMN: PUBLIC."GRPTIME".GROUP_TIME not nullable
  COLUMN: PUBLIC."GRPTIME".DESCRIPT not nullable
  COLUMN: PUBLIC."GRPTIME".DESCRIPT2 not nullable

]
[range variable 1
  join type=INNER
  table=BLDTEST
  cardinality=28864
  access=INDEX PRED
  join condition = [index=SYS_IDX_SYS_PK_10116_10119
    start conditions=[
    GREATER_EQUAL arg_left=[     COLUMN: PUBLIC.BLDTEST.TEST_DATE
] arg_right=[
     VALUE = DATE'1999-12-28', TYPE = DATE]]
    end condition=[
    SMALLER_EQUAL arg_left=[     COLUMN: PUBLIC.BLDTEST.TEST_DATE
] arg_right=[
     VALUE = DATE'2000-01-05', TYPE = DATE]]
    other condition=[
    SMALLER_EQUAL arg_left=[     COLUMN: PUBLIC.BLDTEST.TEST_DATE
] arg_right=[
     VALUE = DATE'2000-01-05', TYPE = DATE]]
  ]
  ][range variable 2
  join type=INNER
  table=GRPTIME
  cardinality=4
  access=INDEX PRED
  join condition = [index=SYS_IDX_SYS_PK_10098_10101
    start conditions=[
    EQUAL arg_left=[     COLUMN: PUBLIC."GRPTIME".GROUP_TIME
] arg_right=[     COLUMN: PUBLIC.BLDTEST.GROUP_TIME
]]
    end condition=[
    EQUAL arg_left=[     COLUMN: PUBLIC."GRPTIME".GROUP_TIME
] arg_right=[     COLUMN: PUBLIC.BLDTEST.GROUP_TIME
]]
  ]
  ]]
order by=[
COLUMN: PUBLIC.BLDTEST.TEST_DAY
DESC
uses index]
PARAMETERS=[]
SUBQUERIES[]

Fetched 54 rows.

You might also want to do a search, in this forum, for: explain plan for . . . and look in the HSQL documentation ( Version 1.8 ) for Explain Plan http://www.hsqldb.org/doc/1.8/guide/ch0 ... in-section .

I hope this helps, please be sure to let me /us know.

Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this issue has been resolved.

[dreamquartz]

Hi Sliderule,

I am running the embedded version.
Forgot about Indexes.
I will look into it.

Dream

[Sliderule]

Just as a guess . . . since you have given NO indication about how many records in your table(s) nor, what your Query look like . . . let me mention a thought.

In another ( prior ) post ( viewtopic.php?f=13&t=64954&p=288440 ) . . . we talked about using the function DATEDIFF ( to calculate the number of days, or, months, years, hours, minutes, etc ) and I mentioned that HSQL ( database backend ) Version 2.3 has a function DATEADD . It is important to say, that, in my experience, the use of DATEDIFF in HSQL Version 1.8 ( Embedded Database ) is very SLOW ( inefficient ). But, it ( DATEDIFF ) is much faster ( efficient ) with HSQL Version 2.3 . . . not to mention . . . that you could use DATEADD instead.

Of course, you could, if you have an index on your date column, make the search faster, by including in a WHERE clause, a date range . . . for example . . . .Where "MyDateColumn" >= '2013-07-01' . . . so . . . the DATEDIFF will only have to be calculated on a lot fewer records.

I am mentioning all of this, since, your Queries might run much faster, if you upgrade to HSQL 2.3 ( as of today, the date I am writing this . . . HSQL 2.3.2 ) and as an external database . . . it ( the database ) would me much safer and less likely to cause corruption ( *.odb ) problems / failure.

Let me give you REAL numbers . . . to compare the use of DATEDIFF and DATEADD with HSQL Version 1.8.0.10 ( embedded ) and HSQL Version 2.3.2 ( external ) with the following four queries. My table "bldtest" or "BLDTEST" contains about 28,864 records, "grptime" or "GRPTIME" contains 4 records.

1. HSQL 1.8.0.10 with DATEDIFF, Query took 179805 milliseconds and returned 71 rows
   

   Code: Select all

   Select * 
   From "bldtest", "grptime" 
   Where "grptime"."group_time" = "bldtest"."group_time" 
     and DATEDIFF('day', "bldtest"."test_date", CURRENT_DATE) <= 20;

2. HSQL 1.8.0.10 with DATEDIFF and narrow "test_date" rows to check, Query took 79 milliseconds and returned 71 rows
   

   Code: Select all

   Select * 
   From "bldtest", "grptime" 
   Where "grptime"."group_time" = "bldtest"."group_time" 
     and "bldtest"."test_date" >= '2014-01-01'
     and DATEDIFF('day', "bldtest"."test_date", CURRENT_DATE) <= 20;

3. HSQL 2.3.2 with DATEDIFF, Query took 281 milliseconds and returned 71 rows
   

   Code: Select all

   Select * 
   From "BLDTEST", "GRPTIME" 
   Where "GRPTIME"."GROUP_TIME" = "BLDTEST"."GROUP_TIME" 
     and DATEDIFF('day', "BLDTEST"."TEST_DATE", CURRENT_DATE) <= 20;

4. HSQL 2.3.2 with DATEADD, Query took 16 milliseconds and returned 71 rows
   

   Code: Select all

   Select * 
   From "BLDTEST", "GRPTIME" 
   Where "GRPTIME"."GROUP_TIME" = "BLDTEST"."GROUP_TIME" 
     and DATEADD('day', -20, CURRENT_DATE) <= "BLDTEST"."TEST_DATE";

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit buton top right) if this isue has ben resolved.

[dreamquartz]

Have been working on the issue for a while, but no luck.
Can not find a good and easy way to install hsqldb outside of LibreOffice.
Information available looks too complicated and time consuming.
Would love to be able to check the DB that way.

Dream

[Villeroy]

0) Make a backup copy of your embedded database.
1) Download the latest HSQLDB and extract it somewhere. Mine is in /usr/local/lib/ or C:\Program Files\ or something. No further installation required.
2) Unzip the database folder out of the odb and rename all files (sript, data, properties) adding some common name prefix (prefix.script, prefix.data, prefix.properties).
3) Download download/file.php?id=17008, do NOT extract but install it via Tools>Extensions...[Add...]. It dumps a Basic library FreeHSQLDB into your user profile.
4) Open your odb and call Tools>Macros>Organize>Basic>MyMacros>FreeHSQLDB>FreeHSQLDB>Main which pops up a dialog.
5) Point the tool to lib/hsqldb.jar extracted in step 1).
6) Point the tool to the *.script file extracted and renamed in 2).
7) Save your *.odb, notice the new connection indicated on the status bar and see if your database works as it used to when it was embedded.

Edit: If the tool complains about your database document not being a database document, shut down the office and try again. I can not find the cause of this weird error but restarting the office always works for me.

[dreamquartz]

Hi Villeroy,

The Windows version is indeed quite easy as you describe, but I was thinking about it under Ubuntu 12.04.
Trying to figure that one out is a little more complicated.

Dream

[Villeroy]

I'm using Linux and Window. There is no difference at all. This is all about Java and zip.

A shell script to do the extraction and renaming job:
June 2015: slightly better version of that shell script:

Code: Select all

#!/bin/sh
ODB=$1
DBNAME=`basename "${ODB%.*}"`
unzip $ODB database/*
cd database
for n in *; do mv $n $DBNAME.$n; done

It takes MyDBName.odb as argument, extracts the embedded database/ folder into the current directory and renames script,properties,data to MyDBName.script, MyDBName.data and MyDBName.properties.

[ewald]

I've worked through the posts regarding base and HSQL by you and DACH.

I have reached the status where I successfully split my original base database and was able to connect to it using the hsql.jar.

The problem I am having now is altering tables and creating new tables. Harry Hobbes from the LibreOffice forum tells me why:

The Base embedded version of HyperSQL is 1.8, and as five year old technology, a known deficiency in that version is the lack of auto-increment fields. The current production version of HyperSQL (2.3.2) handles the feature correctly. You can replace the 1.8 driver with the 2.3.2 driver to get current functions and features. However, be advised that once your back-end is opened with the version 2.3.2 driver, the database is NOT backward compatible with version 1.8 (embedded in Base)

.

What are my options now to make changes in my existing database?

[Greengiant224]

ewald:
The problem I am having now is altering tables and creating new tables. Harry Hobbes from the LibreOffice forum tells me why:

The Base embedded version of HyperSQL is 1.8, and as five year old technology, a known deficiency in that version is the lack of auto-increment fields. The current production version of HyperSQL (2.3.2) handles the feature correctly. You can replace the 1.8 driver with the 2.3.2 driver to get current functions and features. However, be advised that once your back-end is opened with the version 2.3.2 driver, the database is NOT backward compatible with version 1.8 (embedded in Base)

FWIW Not entirely correct.

Both versions of HSQL v1.8.10 and v2.3.* can both auto-increment a column.

example sql:
CREATE TABLE "your_tablename"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY)

Unless of course, you are describing something completely different.
Hope this helps


Greengiant224

[ewald]

You can create an autoincrement column using SQL code that's clear. But the reason I was going for base was because of the GUI approach. With that you CAN'T create a table with auto increment.

[Villeroy]

You can create an autoincrement column using SQL code that's clear. But the reason I was going for base was because of the GUI approach. With that you CAN'T create a table with auto increment.

Forget the GUI. It's broken. It's incomplete. It is a pain in the ass.

The GUI does not allow date/time defaults (DEFAULT CURRENT_TIMESTAMP).
The GUI does not know constraints (CHECK "Start"<="End").
The GUI does not know users, groups, permissions.
The GUI is extremely annoying when you want to modify existing tables and indices.
With the GUI you can not be sure that some error message is your fault or just because Base is not able to work properly.

Learn the simple DDL syntax (data definition language) which lets you create tables, indices and relations much easier and faster than with a broken GUI.

[ewald]

Thanks for the advice. Is there a good peace of literature you could recommend to learn the DDL? In what way does DDL differ from SQL?

[Villeroy]

Thanks for the advice. Is there a good peace of literature you could recommend to learn the DDL? In what way does DDL differ from SQL?

SQL can be divided in 3 parts:
1) Data definition: any statement starting with CREATE, DROP or ALTER creates, deletes or alters some table, constraint or index. This is what you do when you add, remove or edit the tables, indices, fields and relations in Base. This is about the structures to keep your record sets.
2) Data manipulation: any statement starting with INSERT, DELETE or UPDATE inserts a new record, deletes a record or updates (edits) a record. This is what you do with Base when using forms or the naked grid of an editable record set. This is about the record sets.
3) Data selection: this is the most sophisticated part where Base's query designer may save some typing effort but to overcome the tight limitations of this tool you will need some understanding of SQL SELECT statements with inner and outer JOINs and GROUPing. This is about turning abstract data into meaningful information.

SQL is standardized and the recent HSQL 2.3 is very conformant to the standard. Any tutorial, lecture or book on standard SQL can be followed by means of HSQL. HSQL is well documented in the doc directory of your downloaded HSQL package. The embedded HSQL is documented here: http://www.hsqldb.org/doc/1.8/guide/ch09.html

SQL-99Complete, Really by Peter Gulutzan & Trudy Pelzer is a very good compendium.
When using LInux, any of the commonly used text editors has some SQL mode with some syntax highlighting. Some text editors can be connected to databases via JDBC so you can have a more powerful SQL editor than the one in Base. There are also graphical tools to generate valid SQL for various types of databases but I never used one because I find it very easy to write down SQL code.

[ewald]

Thanks a lot Villeroy. Your suggestions will give me something to do during the summer break. Maybe I'll have to let go of the GUI philosopy I am so attached to.

[Villeroy]

The DDL syntax is very, very simple. With a little bit of practice you can write down a complete new database faster than you can click it together. A sheet of paper with a list of fields and their relations is more helpful than the Base GUI.

The following statement builds a new table with an auto-ID field, default date and references to two existing tables:

Code: Select all

CREATE TABLE SALES(
   D DATE DEFAULT CURRENT_DATE NOT NULL,
   PID INT NOT NULL,
   CID INT NOT NULL,
   VAL DECIMAL(6,2) NOT NULL,
   ID INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   FOREIGN KEY(CID) REFERENCES CATEGORIES(ID),
   FOREIGN KEY(PID) REFERENCES PERSONS(ID)
);

CREATE TABLE "Some Name" is followed by a pair of braces with a list of fields and indices in between the braces. The trailing semicolon marks the end of an SQL statement. White space has no meaning so you can use line feeds and tabs for better readability.
You can omit the "double-quotes" around object names if the names are upper case and do not conflict with any SQL keyword. SALES, D, PID, CID, VAL, ID are no SQL keywords. To be sure, you can wrap the field names and table names in double-quotes anyway.
Each field is defined by its name, then the type, then a default value (if any) and NOT NULL. By default new columns are nullable.
The names of foreign key fields are in braces because foreign keys may include more than one field, for example FOREIGN KEY(CID, XREF) REFERENCES CATEGORIES(ID, X) would link 2 fields of the new table to 2 fields in the categories table.
The string "ID INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY," is a stored text snippet in my SQL editor. I insert this line by keyboard shortcut whenever I need an auto-ID for a new HSQL table.

[Rhyssa6]

0) Make a backup copy of your embedded database.
1) Download the latest HSQLDB and extract it somewhere. Mine is in /usr/local/lib/ or C:\Program Files\ or something. No further installation required.
2) Unzip the database folder out of the odb and rename all files (sript, data, properties) adding some common name prefix (prefix.script, prefix.data, prefix.properties).
3) Download download/file.php?id=17008, do NOT extract but install it via Tools>Extensions...[Add...]. It dumps a Basic library FreeHSQLDB into your user profile.
4) Open your odb and call Tools>Macros>Organize>Basic>MyMacros>FreeHSQLDB>FreeHSQLDB>Main which pops up a dialog.
5) Point the tool to lib/hsqldb.jar extracted in step 1).
6) Point the tool to the *.script file extracted and renamed in 2).
5) Save your *.odb, notice the new connection indicated on the status bar and see if your database works as it used to when it was embedded.

Edit: If the tool complains about your database document not being a database document, shut down the office and try again. I can not find the cause of this weird error but restarting the office always works for me.

I've done all of this up to and including step 5 but I can't find where to do step 6. The box to point to the .jar file is the only box in that window in which I can type anything. When I click Set - hsqldb.jar - open, another window opens (sometimes) with a message which states 'Basic Runtime error. Object variable not set.' The problem line of code seems to be this one:
if len(s) > 0 then
gODB.DataSource.Settings.JavaDriverClassPath = s

Is this where I'm meant to point to the renamed script file and if so from which point in the line do I replace the text with my script file address?