H2 with multiple joins, am I doing something wrong?
16 views
Skip to first unread message
Abyric
Oct 12, 2008, 1:06:53 PM10/12/08
to H2 Database
Hi,
Currently I am using an embedded database with my Java application.
This database has been normalised into 9 tables with each about 3-5
columns except for 1 "main table". This main table holds 8 foreign
keys to the other tables. The primary keys of the other tables are
mostly of datatype "Varchar(3)"
What I wish to retrieve is all this data with 1 query (this would
indeed mean a resultset with about 45 columns).
However, writing this query will mean I have to join 8 tables. This is
where my question comes in. For testing purposes all tables have only
1 or 2 rows of data. When I execute the query it takes about 800ms to
execute, which seems very long.
I have checked the "explain" and the query isnt tablescanning. When I
remove 1 join, doesn't matter which join, time is reduced to 250-300
ms.. and when I remove another one its about 0-10ms.
I have also tried to make a View of the query and querying the View
(with all joins) reduces time to 0-10ms but sporadically (about every
3 times I execute the query in my browser) it's 1500ms. So im not
quite sure if this solves my problem. If im correct, a View executes
the same query but im not certain about that.
What I wish to know is, am I doing something wrong? is having 8 joins
too intensive? and what would be a good way to find a solution and
join all the data in 1 resultset? As it seems now, I would almost
think that normalising everything has only given me a performance
penalty.
Furthermore, if there's any data needed with my question I'll gladly
provide it.
Thank in advance
Currently I am using an embedded database with my Java application.
This database has been normalised into 9 tables with each about 3-5
columns except for 1 "main table". This main table holds 8 foreign
keys to the other tables. The primary keys of the other tables are
mostly of datatype "Varchar(3)"
What I wish to retrieve is all this data with 1 query (this would
indeed mean a resultset with about 45 columns).
However, writing this query will mean I have to join 8 tables. This is
where my question comes in. For testing purposes all tables have only
1 or 2 rows of data. When I execute the query it takes about 800ms to
execute, which seems very long.
I have checked the "explain" and the query isnt tablescanning. When I
remove 1 join, doesn't matter which join, time is reduced to 250-300
ms.. and when I remove another one its about 0-10ms.
I have also tried to make a View of the query and querying the View
(with all joins) reduces time to 0-10ms but sporadically (about every
3 times I execute the query in my browser) it's 1500ms. So im not
quite sure if this solves my problem. If im correct, a View executes
the same query but im not certain about that.
What I wish to know is, am I doing something wrong? is having 8 joins
too intensive? and what would be a good way to find a solution and
join all the data in 1 resultset? As it seems now, I would almost
think that normalising everything has only given me a performance
penalty.
Furthermore, if there's any data needed with my question I'll gladly
provide it.
Thank in advance
Abyric
Oct 15, 2008, 11:01:36 PM10/15/08
to H2 Database
After thouroughly reading trough many SQL optimisation sites I still
fail to see what I can to to increase performance. To keep it short,
my query joins 16 tables. When using explain, all the joins use
foreign/primary keys so they are indexed. I double checked that since
all the columns in the WHERE-clause of the explain have been index
(again, as primary or foreign key).
Why is it that executing the query and fetching 100 records, takes
1600 ms? Truth is that 5,000 records take 2734 ms
I don't understand why 100 recods take so long and 5,000 records are
relative fast...
Help?
fail to see what I can to to increase performance. To keep it short,
my query joins 16 tables. When using explain, all the joins use
foreign/primary keys so they are indexed. I double checked that since
all the columns in the WHERE-clause of the explain have been index
(again, as primary or foreign key).
Why is it that executing the query and fetching 100 records, takes
1600 ms? Truth is that 5,000 records take 2734 ms
I don't understand why 100 recods take so long and 5,000 records are
relative fast...
Help?
Thomas Mueller
Oct 16, 2008, 3:21:36 PM10/16/08
to h2-da...@googlegroups.com
Hi,
Could you post the data or a test case with random data?
Regards,
Thomas
Abyric
Oct 17, 2008, 2:34:22 AM10/17/08
to H2 Database
Thank you for your reply!
You can copy and paste the following in the H2 Console and it should
build the table, fill it and execute the query I currently am using.
Thank you for your time!
----------------------------------------------------
DROP TABLE IF EXISTS country ;
CREATE TABLE country
(
country_code VARCHAR(3) NOT NULL,
countryname VARCHAR(50) NOT NULL,
PRIMARY KEY (country_code),
UNIQUE (countryname)
) ;
DROP TABLE IF EXISTS city ;
CREATE TABLE city
(
city_code VARCHAR(3) NOT NULL,
cityname VARCHAR(50) NOT NULL,
country_code VARCHAR(3) NOT NULL,
PRIMARY KEY (city_code),
FOREIGN KEY (country_code) REFERENCES country (country_code)
ON DELETE CASCADE ON UPDATE CASCADE
) ;
DROP TABLE IF EXISTS naval ;
CREATE TABLE naval
(
naval_code VARCHAR(3) NOT NULL,
navalname VARCHAR(255) NOT NULL,
city_code VARCHAR(3) NOT NULL,
PRIMARY KEY (naval_code),
FOREIGN KEY (city_code) REFERENCES city (city_code)
ON DELETE CASCADE ON UPDATE CASCADE
) ;
DROP TABLE IF EXISTS captain ;
CREATE TABLE captain
(
captain_id INTEGER NOT NULL AUTO_INCREMENT,
captain_name VARCHAR(50) NOT NULL,
PRIMARY KEY (captain_id)
) ;
DROP TABLE IF EXISTS vessal ;
CREATE TABLE vessal
(
vessal_code VARCHAR(4) NOT NULL,
prefix VARCHAR(3) NOT NULL,
vessalname VARCHAR(50) NOT NULL,
captain INTEGER,
PRIMARY KEY (vessal_code),
FOREIGN KEY (captain) REFERENCES captain (captain_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ;
DROP TABLE IF EXISTS expeditor ;
CREATE TABLE expeditor
(
expeditor_code VARCHAR(3) NOT NULL,
expeditorname VARCHAR(50) NOT NULL,
PRIMARY KEY (expeditor_code)
) ;
DROP TABLE IF EXISTS weighttype ;
CREATE TABLE weighttype
(
weighttype TINYINT NOT NULL AUTO_INCREMENT,
weightname VARCHAR(2) NOT NULL,
PRIMARY KEY (weighttype),
UNIQUE (weightname)
) ;
DROP TABLE IF EXISTS fueltype ;
CREATE TABLE fueltype
(
fueltype_id TINYINT NOT NULL AUTO_INCREMENT,
fueltype_name VARCHAR(50) NOT NULL,
PRIMARY KEY (fueltype_id),
UNIQUE (fueltype_name)
) ;
DROP TABLE IF EXISTS fuels ;
CREATE TABLE fuels
(
fuel_id BIGINT NOT NULL AUTO_INCREMENT,
fueltype_id TINYINT NOT NULL,
naval_code VARCHAR(3) NOT NULL,
vessal_code VARCHAR(3) NOT NULL,
min DECIMAL(5,2) NOT NULL,
unit DECIMAL(5,2) NOT NULL,
weighttype TINYINT NOT NULL,
startingdate DATE NOT NULL,
endingdate DATE,
active BOOL NOT NULL,
PRIMARY KEY (fuel_id),
FOREIGN KEY (naval_code) REFERENCES naval (naval_code)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (vessal_code) REFERENCES vessal (vessal_code)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (fueltype_id) REFERENCES fueltype (fueltype_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (weighttype) REFERENCES weighttype (weighttype)
ON DELETE CASCADE ON UPDATE CASCADE
) ;
DROP TABLE IF EXISTS product ;
CREATE TABLE product
(
product_id SMALLINT NOT NULL AUTO_INCREMENT,
productname VARCHAR(50) NOT NULL,
productdesc TEXT,
PRIMARY KEY (product_id)
) ;
DROP TABLE IF EXISTS currency ;
CREATE TABLE currency
(
currency_id SMALLINT NOT NULL AUTO_INCREMENT,
currencyname VARCHAR(3) NOT NULL,
PRIMARY KEY (currency_id),
UNIQUE (currencyname)
) ;
DROP TABLE IF EXISTS rgd ;
CREATE TABLE rgd
(
rgd_id SMALLINT NOT NULL AUTO_INCREMENT,
rgdvalue VARCHAR(10) NOT NULL,
PRIMARY KEY (rgd_id),
UNIQUE (rgdvalue)
) ;
DROP TABLE IF EXISTS activejourneys ;
CREATE TABLE activejourneys
(
journey_id BIGINT NOT NULL AUTO_INCREMENT,
specialjourney BOOL NOT NULL,
originnavalCode VARCHAR(3) NOT NULL,
destinationnavalCode VARCHAR(3) NOT NULL,
vessalCode VARCHAR(4) NOT NULL,
expeditorCode VARCHAR(3) NOT NULL,
jMin DECIMAL(5,2),
jBasic DECIMAL(5,2),
jFlat DECIMAL(5,2),
jFortyFive DECIMAL(5,2),
jHundred DECIMAL(5,2),
jTwoFifty DECIMAL(5,2),
jThreeHundred DECIMAL(5,2),
jFiveHundred DECIMAL(5,2),
jThousand DECIMAL(5,2),
jTwoThousand DECIMAL(5,2),
kType VARCHAR(255),
kPweight DECIMAL(5,2),
kPjourney DECIMAL(5,2),
kFlat DECIMAL(5,2),
kOverP DECIMAL(5,2),
fuelPrim_id BIGINT,
fuelSec_id BIGINT,
product_id SMALLINT NOT NULL,
currency_id SMALLINT NOT NULL,
rgd_id SMALLINT NOT NULL,
pp BOOL NOT NULL,
cc BOOL NOT NULL,
fromdate DATE NOT NULL,
tilldate DATE NOT NULL,
notes TEXT,
PRIMARY KEY (journey_id),
FOREIGN KEY (originnavalCode) REFERENCES naval (naval_code)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (destinationnavalCode) REFERENCES naval (naval_code)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (vessalCode) REFERENCES vessal (vessal_code)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (currency_id) REFERENCES currency (currency_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (rgd_id) REFERENCES rgd (rgd_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (expeditorCode) REFERENCES expeditor (expeditor_code)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (product_id) REFERENCES product (product_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (fuelPrim_id) REFERENCES fuels (fuel_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (fuelSec_id) REFERENCES fuels (fuel_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ;
insert into country (country_code, countryname)
values('NL','Netherlands');
insert into country (country_code, countryname)
values('BE','Belgium');
insert into country (country_code, countryname) values('FR','France');
insert into city (city_code, cityname, country_code) values ('AMS',
'Amsterdam','NL');
insert into city (city_code, cityname, country_code) values ('BRU',
'Brussels', 'BE');
insert into city (city_code, cityname, country_code) values ('PAR',
'Paris', 'FR');
insert into naval (naval_code, navalname, city_code) values ('AMS',
'Amsterdam Port', 'AMS');
insert into naval (naval_code, navalname, city_code) values ('BRU',
'Brussels Port', 'BRU');
insert into naval (naval_code, navalname, city_code) values ('LHV',
'Le Havre du Paris', 'PAR');
insert into captain (captain_name) values ('Sparrow');
insert into vessal (vessal_code, prefix, vessalname, captain)
values('ME', '704', 'Mediterrenian One', 1);
insert into expeditor(expeditor_code, expeditorname) values ('AAA',
'AAA');
insert into fueltype(fueltype_name) values ('Fuel 1');
insert into fueltype(fueltype_name) values ('Fuel 2');
insert into weighttype(weightname) values ('AA');
insert into weighttype(weightname) values ('CA');
insert into fuels(fuel_id, fueltype_id, naval_code, vessal_code, min,
unit, weighttype, startingdate, endingdate, active)
values(1, 1, 'BRU', 'ME', '0.54', '0.60', '1', '2008-10-10', null,
true);
insert into fuels(fuel_id, fueltype_id, naval_code, vessal_code, min,
unit, weighttype, startingdate, endingdate, active)
values(2, 2, 'BRU', 'ME', '0.14', '0.16', '1', '2008-10-10',
'2008-10-20', true);
insert into fuels(fuel_id, fueltype_id, naval_code, vessal_code, min,
unit, weighttype, startingdate, endingdate, active)
values(3, 2, 'BRU', 'ME', '0.15', '0.17', '1', '2008-10-20', null,
true);
insert into fuels(fuel_id, fueltype_id, naval_code, vessal_code, min,
unit, weighttype, startingdate, endingdate, active)
values(4, 1, 'LHV', 'ME', '0.24', '0.62', '1', '2008-10-10',
'2008-10-20', true);
insert into fuels(fuel_id, fueltype_id, naval_code, vessal_code, min,
unit, weighttype, startingdate, endingdate, active)
values(5, 1, 'LHV', 'ME', '0.30', '0.40', '1', '2008-10-20', null,
true);
insert into fuels(fuel_id, fueltype_id, naval_code, vessal_code, min,
unit, weighttype, startingdate, endingdate, active)
values(6, 2, 'LHV', 'ME', '0.34', '0.61', '1', '2008-10-10', null,
true);
insert into fuels(fuel_id, fueltype_id, naval_code, vessal_code, min,
unit, weighttype, startingdate, endingdate, active)
values(7, 1, 'BRU', 'ME', '0.30', '0.40', '1', '2007-10-20',
'2007-10-30', true);
insert into fuels(fuel_id, fueltype_id, naval_code, vessal_code, min,
unit, weighttype, startingdate, endingdate, active)
values(8, 2, 'LHV', 'ME', '0.34', '0.61', '1', '2007-10-10',
'2007-10-20', true);
insert into product(productname, productdesc) values ('PRO 16:00',
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
insert into currency(currencyname) values ('EUR');
insert into rgd (rgdvalue) values ('no');
insert into rgd (rgdvalue) values ('XAP');
insert into rgd (rgdvalue) values ('COA');
insert into activejourneys (journey_id, specialjourney,
originnavalCode, destinationnavalCode, vessalCode, expeditorCode,
jMin, jBasic, jFlat, jFortyFive, jHundred, jTwoFifty, jThreehundred,
jFiveHundred, jThousand, jTwoThousand,kType, kPWeight, kPjourney,
kFlat, kOverP, fuelprim_id, fuelsec_id, product_id, currency_id,
rgd_id,
pp, cc, fromdate, tilldate, notes) values( 1, false, 'AMS', 'BRU',
'ME', 'AAA', 0.5, null, 0.6, 0.34, 0.44, 0.55, 0.35, 0.55, 0.66, 0.77,
null, null, null, null, null, 1, 2, 1, 1, 1, true, true, '2008-10-10',
'2008-10-20', 'Non special journey!');
insert into activejourneys (journey_id, specialjourney,
originnavalCode, destinationnavalCode, vessalCode, expeditorCode,
jMin, jBasic, jFlat, jFortyFive, jHundred, jTwoFifty, jThreehundred,
jFiveHundred, jThousand, jTwoThousand,kType, kPWeight, kPjourney,
kFlat, kOverP, fuelprim_id, fuelsec_id, product_id, currency_id,
rgd_id,
pp, cc, fromdate, tilldate, notes) values( 2, true, 'AMS', 'LHV',
'ME', 'AAA', 0.5, null, 0.6, 0.34, 0.44, 0.55, 0.35, 0.55, 0.66, 0.77,
null, null, null, null, null, 3, 4, 1, 1, 1, true, true, '2008-10-10',
'2008-10-20', 'Special journey!');
select (aj.specialjourney) as specialjourney, (navOri.naval_code)as
originnavalCode, (navOri.navalname) as originnavalName,
(citori.city_code) as originCityCode,
(citori.cityname) as originCityName, (couori.country_code) as
originCountryCode, (couori.countryname) as originCountryName,
(navDest.naval_code) as destinationnavalCode,
(navDest.navalname) as destinationnavalName,(citdest.city_code) as
destinationCityCode, (citdest.cityname) as destinationCityName,
(coudest.country_code) as destinationCountryCode,
(coudest.countryname) as destinationCountryName, (ves.vessal_code) as
vessalCode, (ves.vessalname) as vessalName, (ves.prefix) as
vessalPrefix, (cap.captain_name) as captainName,
(exp.expeditor_code) as expeditorCode, (exp.expeditorname) as
expeditorName, (aj.jmin) as jMin, (aj.jbasic) as jBasic, (aj.jflat) as
jFlat,(aj.jfortyfive) as jFortyFive,
(aj.jhundred) as jHundred, (aj.jtwofifty) as jTwoFifty,
(aj.jthreehundred) as jThreeHundred, (aj.jfivehundred) as jFiveHundred,
(aj.jthousand) as jThousand,
(aj.jtwothousand) as jTwoThousand, (aj.ktype) as kType, (aj.kpweight)
as kPWeight, (aj.kpjourney) as kPjourney, (aj.kflat) as kFlat,
(aj.koverp) as kOverP, (fuelPrim.min) as fuelPrimMin, (fuelPrim.unit)
as fuelPrimUnit, (fuelPrimwt.weightname) as fuelPrimWeightType,
(fuelSec.min) as fuelSecMin, (fuelSec.unit) as fuelSecUnit,
(fuelSecwt.weightname) as fuelSecWeightType, (pro.productname) as
productName, (cur.currencyname) as currencyName, (d.rgdvalue) as
rgdValue, (aj.pp) as PP,
(aj.cc) as CC, (aj.fromdate) as fromDate, (aj.tilldate) as tillDate,
(aj.notes) as notesfrom from activejourneys as aj
join naval as navOri on aj.originnavalcode = navOri.naval_code
join naval as navDest on aj.destinationnavalcode = navDest.naval_code
join city as citori on navOri.city_code=citori.city_code
join city as citdest on navDest.city_code=citdest.city_code
join country as couori on couori.country_code=citori.country_code
join country as coudest on coudest.country_code=citdest.country_code
join vessal as ves on aj.vessalcode = ves.vessal_code
join captain as cap on ves.captain=cap.captain_id
join expeditor as exp on aj.expeditorcode = exp.expeditor_code
join fuels as fuelPrim on aj.fuelPrim_id=fuelPrim.fuel_id
join weighttype as fuelPrimwt on fuelPrimwt.weighttype =
fuelPrim.fueltype_id
join fuels as fuelSec on aj.fuelSec_id=fuelSec.fuel_id
join weighttype as fuelSecwt on fuelSecwt.weighttype =
fuelSec.fueltype_id
join product as pro on aj.product_id=pro.product_id
join currency as cur on aj.currency_id = cur.currency_id
join rgd as d on aj.rgd_id = d.rgd_id
------------------------------------------------------------------------------------------------------------
On Oct 16, 9:21 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
> > Help?- Hide quoted text -
>
> - Show quoted text -
You can copy and paste the following in the H2 Console and it should
build the table, fill it and execute the query I currently am using.
Thank you for your time!
----------------------------------------------------
DROP TABLE IF EXISTS country ;
CREATE TABLE country
(
country_code VARCHAR(3) NOT NULL,
countryname VARCHAR(50) NOT NULL,
PRIMARY KEY (country_code),
UNIQUE (countryname)
) ;
DROP TABLE IF EXISTS city ;
CREATE TABLE city
(
city_code VARCHAR(3) NOT NULL,
cityname VARCHAR(50) NOT NULL,
country_code VARCHAR(3) NOT NULL,
PRIMARY KEY (city_code),
FOREIGN KEY (country_code) REFERENCES country (country_code)
ON DELETE CASCADE ON UPDATE CASCADE
) ;
DROP TABLE IF EXISTS naval ;
CREATE TABLE naval
(
naval_code VARCHAR(3) NOT NULL,
navalname VARCHAR(255) NOT NULL,
city_code VARCHAR(3) NOT NULL,
PRIMARY KEY (naval_code),
FOREIGN KEY (city_code) REFERENCES city (city_code)
ON DELETE CASCADE ON UPDATE CASCADE
) ;
DROP TABLE IF EXISTS captain ;
CREATE TABLE captain
(
captain_id INTEGER NOT NULL AUTO_INCREMENT,
captain_name VARCHAR(50) NOT NULL,
PRIMARY KEY (captain_id)
) ;
DROP TABLE IF EXISTS vessal ;
CREATE TABLE vessal
(
vessal_code VARCHAR(4) NOT NULL,
prefix VARCHAR(3) NOT NULL,
vessalname VARCHAR(50) NOT NULL,
captain INTEGER,
PRIMARY KEY (vessal_code),
FOREIGN KEY (captain) REFERENCES captain (captain_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ;
DROP TABLE IF EXISTS expeditor ;
CREATE TABLE expeditor
(
expeditor_code VARCHAR(3) NOT NULL,
expeditorname VARCHAR(50) NOT NULL,
PRIMARY KEY (expeditor_code)
) ;
DROP TABLE IF EXISTS weighttype ;
CREATE TABLE weighttype
(
weighttype TINYINT NOT NULL AUTO_INCREMENT,
weightname VARCHAR(2) NOT NULL,
PRIMARY KEY (weighttype),
UNIQUE (weightname)
) ;
DROP TABLE IF EXISTS fueltype ;
CREATE TABLE fueltype
(
fueltype_id TINYINT NOT NULL AUTO_INCREMENT,
fueltype_name VARCHAR(50) NOT NULL,
PRIMARY KEY (fueltype_id),
UNIQUE (fueltype_name)
) ;
DROP TABLE IF EXISTS fuels ;
CREATE TABLE fuels
(
fuel_id BIGINT NOT NULL AUTO_INCREMENT,
fueltype_id TINYINT NOT NULL,
naval_code VARCHAR(3) NOT NULL,
vessal_code VARCHAR(3) NOT NULL,
min DECIMAL(5,2) NOT NULL,
unit DECIMAL(5,2) NOT NULL,
weighttype TINYINT NOT NULL,
startingdate DATE NOT NULL,
endingdate DATE,
active BOOL NOT NULL,
PRIMARY KEY (fuel_id),
FOREIGN KEY (naval_code) REFERENCES naval (naval_code)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (vessal_code) REFERENCES vessal (vessal_code)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (fueltype_id) REFERENCES fueltype (fueltype_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (weighttype) REFERENCES weighttype (weighttype)
ON DELETE CASCADE ON UPDATE CASCADE
) ;
DROP TABLE IF EXISTS product ;
CREATE TABLE product
(
product_id SMALLINT NOT NULL AUTO_INCREMENT,
productname VARCHAR(50) NOT NULL,
productdesc TEXT,
PRIMARY KEY (product_id)
) ;
DROP TABLE IF EXISTS currency ;
CREATE TABLE currency
(
currency_id SMALLINT NOT NULL AUTO_INCREMENT,
currencyname VARCHAR(3) NOT NULL,
PRIMARY KEY (currency_id),
UNIQUE (currencyname)
) ;
DROP TABLE IF EXISTS rgd ;
CREATE TABLE rgd
(
rgd_id SMALLINT NOT NULL AUTO_INCREMENT,
rgdvalue VARCHAR(10) NOT NULL,
PRIMARY KEY (rgd_id),
UNIQUE (rgdvalue)
) ;
DROP TABLE IF EXISTS activejourneys ;
CREATE TABLE activejourneys
(
journey_id BIGINT NOT NULL AUTO_INCREMENT,
specialjourney BOOL NOT NULL,
originnavalCode VARCHAR(3) NOT NULL,
destinationnavalCode VARCHAR(3) NOT NULL,
vessalCode VARCHAR(4) NOT NULL,
expeditorCode VARCHAR(3) NOT NULL,
jMin DECIMAL(5,2),
jBasic DECIMAL(5,2),
jFlat DECIMAL(5,2),
jFortyFive DECIMAL(5,2),
jHundred DECIMAL(5,2),
jTwoFifty DECIMAL(5,2),
jThreeHundred DECIMAL(5,2),
jFiveHundred DECIMAL(5,2),
jThousand DECIMAL(5,2),
jTwoThousand DECIMAL(5,2),
kType VARCHAR(255),
kPweight DECIMAL(5,2),
kPjourney DECIMAL(5,2),
kFlat DECIMAL(5,2),
kOverP DECIMAL(5,2),
fuelPrim_id BIGINT,
fuelSec_id BIGINT,
product_id SMALLINT NOT NULL,
currency_id SMALLINT NOT NULL,
rgd_id SMALLINT NOT NULL,
pp BOOL NOT NULL,
cc BOOL NOT NULL,
fromdate DATE NOT NULL,
tilldate DATE NOT NULL,
notes TEXT,
PRIMARY KEY (journey_id),
FOREIGN KEY (originnavalCode) REFERENCES naval (naval_code)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (destinationnavalCode) REFERENCES naval (naval_code)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (vessalCode) REFERENCES vessal (vessal_code)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (currency_id) REFERENCES currency (currency_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (rgd_id) REFERENCES rgd (rgd_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (expeditorCode) REFERENCES expeditor (expeditor_code)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (product_id) REFERENCES product (product_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (fuelPrim_id) REFERENCES fuels (fuel_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (fuelSec_id) REFERENCES fuels (fuel_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ;
insert into country (country_code, countryname)
values('NL','Netherlands');
insert into country (country_code, countryname)
values('BE','Belgium');
insert into country (country_code, countryname) values('FR','France');
insert into city (city_code, cityname, country_code) values ('AMS',
'Amsterdam','NL');
insert into city (city_code, cityname, country_code) values ('BRU',
'Brussels', 'BE');
insert into city (city_code, cityname, country_code) values ('PAR',
'Paris', 'FR');
insert into naval (naval_code, navalname, city_code) values ('AMS',
'Amsterdam Port', 'AMS');
insert into naval (naval_code, navalname, city_code) values ('BRU',
'Brussels Port', 'BRU');
insert into naval (naval_code, navalname, city_code) values ('LHV',
'Le Havre du Paris', 'PAR');
insert into captain (captain_name) values ('Sparrow');
insert into vessal (vessal_code, prefix, vessalname, captain)
values('ME', '704', 'Mediterrenian One', 1);
insert into expeditor(expeditor_code, expeditorname) values ('AAA',
'AAA');
insert into fueltype(fueltype_name) values ('Fuel 1');
insert into fueltype(fueltype_name) values ('Fuel 2');
insert into weighttype(weightname) values ('AA');
insert into weighttype(weightname) values ('CA');
insert into fuels(fuel_id, fueltype_id, naval_code, vessal_code, min,
unit, weighttype, startingdate, endingdate, active)
values(1, 1, 'BRU', 'ME', '0.54', '0.60', '1', '2008-10-10', null,
true);
insert into fuels(fuel_id, fueltype_id, naval_code, vessal_code, min,
unit, weighttype, startingdate, endingdate, active)
values(2, 2, 'BRU', 'ME', '0.14', '0.16', '1', '2008-10-10',
'2008-10-20', true);
insert into fuels(fuel_id, fueltype_id, naval_code, vessal_code, min,
unit, weighttype, startingdate, endingdate, active)
values(3, 2, 'BRU', 'ME', '0.15', '0.17', '1', '2008-10-20', null,
true);
insert into fuels(fuel_id, fueltype_id, naval_code, vessal_code, min,
unit, weighttype, startingdate, endingdate, active)
values(4, 1, 'LHV', 'ME', '0.24', '0.62', '1', '2008-10-10',
'2008-10-20', true);
insert into fuels(fuel_id, fueltype_id, naval_code, vessal_code, min,
unit, weighttype, startingdate, endingdate, active)
values(5, 1, 'LHV', 'ME', '0.30', '0.40', '1', '2008-10-20', null,
true);
insert into fuels(fuel_id, fueltype_id, naval_code, vessal_code, min,
unit, weighttype, startingdate, endingdate, active)
values(6, 2, 'LHV', 'ME', '0.34', '0.61', '1', '2008-10-10', null,
true);
insert into fuels(fuel_id, fueltype_id, naval_code, vessal_code, min,
unit, weighttype, startingdate, endingdate, active)
values(7, 1, 'BRU', 'ME', '0.30', '0.40', '1', '2007-10-20',
'2007-10-30', true);
insert into fuels(fuel_id, fueltype_id, naval_code, vessal_code, min,
unit, weighttype, startingdate, endingdate, active)
values(8, 2, 'LHV', 'ME', '0.34', '0.61', '1', '2007-10-10',
'2007-10-20', true);
insert into product(productname, productdesc) values ('PRO 16:00',
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
insert into currency(currencyname) values ('EUR');
insert into rgd (rgdvalue) values ('no');
insert into rgd (rgdvalue) values ('XAP');
insert into rgd (rgdvalue) values ('COA');
insert into activejourneys (journey_id, specialjourney,
originnavalCode, destinationnavalCode, vessalCode, expeditorCode,
jMin, jBasic, jFlat, jFortyFive, jHundred, jTwoFifty, jThreehundred,
jFiveHundred, jThousand, jTwoThousand,kType, kPWeight, kPjourney,
kFlat, kOverP, fuelprim_id, fuelsec_id, product_id, currency_id,
rgd_id,
pp, cc, fromdate, tilldate, notes) values( 1, false, 'AMS', 'BRU',
'ME', 'AAA', 0.5, null, 0.6, 0.34, 0.44, 0.55, 0.35, 0.55, 0.66, 0.77,
null, null, null, null, null, 1, 2, 1, 1, 1, true, true, '2008-10-10',
'2008-10-20', 'Non special journey!');
insert into activejourneys (journey_id, specialjourney,
originnavalCode, destinationnavalCode, vessalCode, expeditorCode,
jMin, jBasic, jFlat, jFortyFive, jHundred, jTwoFifty, jThreehundred,
jFiveHundred, jThousand, jTwoThousand,kType, kPWeight, kPjourney,
kFlat, kOverP, fuelprim_id, fuelsec_id, product_id, currency_id,
rgd_id,
pp, cc, fromdate, tilldate, notes) values( 2, true, 'AMS', 'LHV',
'ME', 'AAA', 0.5, null, 0.6, 0.34, 0.44, 0.55, 0.35, 0.55, 0.66, 0.77,
null, null, null, null, null, 3, 4, 1, 1, 1, true, true, '2008-10-10',
'2008-10-20', 'Special journey!');
select (aj.specialjourney) as specialjourney, (navOri.naval_code)as
originnavalCode, (navOri.navalname) as originnavalName,
(citori.city_code) as originCityCode,
(citori.cityname) as originCityName, (couori.country_code) as
originCountryCode, (couori.countryname) as originCountryName,
(navDest.naval_code) as destinationnavalCode,
(navDest.navalname) as destinationnavalName,(citdest.city_code) as
destinationCityCode, (citdest.cityname) as destinationCityName,
(coudest.country_code) as destinationCountryCode,
(coudest.countryname) as destinationCountryName, (ves.vessal_code) as
vessalCode, (ves.vessalname) as vessalName, (ves.prefix) as
vessalPrefix, (cap.captain_name) as captainName,
(exp.expeditor_code) as expeditorCode, (exp.expeditorname) as
expeditorName, (aj.jmin) as jMin, (aj.jbasic) as jBasic, (aj.jflat) as
jFlat,(aj.jfortyfive) as jFortyFive,
(aj.jhundred) as jHundred, (aj.jtwofifty) as jTwoFifty,
(aj.jthreehundred) as jThreeHundred, (aj.jfivehundred) as jFiveHundred,
(aj.jthousand) as jThousand,
(aj.jtwothousand) as jTwoThousand, (aj.ktype) as kType, (aj.kpweight)
as kPWeight, (aj.kpjourney) as kPjourney, (aj.kflat) as kFlat,
(aj.koverp) as kOverP, (fuelPrim.min) as fuelPrimMin, (fuelPrim.unit)
as fuelPrimUnit, (fuelPrimwt.weightname) as fuelPrimWeightType,
(fuelSec.min) as fuelSecMin, (fuelSec.unit) as fuelSecUnit,
(fuelSecwt.weightname) as fuelSecWeightType, (pro.productname) as
productName, (cur.currencyname) as currencyName, (d.rgdvalue) as
rgdValue, (aj.pp) as PP,
(aj.cc) as CC, (aj.fromdate) as fromDate, (aj.tilldate) as tillDate,
(aj.notes) as notesfrom from activejourneys as aj
join naval as navOri on aj.originnavalcode = navOri.naval_code
join naval as navDest on aj.destinationnavalcode = navDest.naval_code
join city as citori on navOri.city_code=citori.city_code
join city as citdest on navDest.city_code=citdest.city_code
join country as couori on couori.country_code=citori.country_code
join country as coudest on coudest.country_code=citdest.country_code
join vessal as ves on aj.vessalcode = ves.vessal_code
join captain as cap on ves.captain=cap.captain_id
join expeditor as exp on aj.expeditorcode = exp.expeditor_code
join fuels as fuelPrim on aj.fuelPrim_id=fuelPrim.fuel_id
join weighttype as fuelPrimwt on fuelPrimwt.weighttype =
fuelPrim.fueltype_id
join fuels as fuelSec on aj.fuelSec_id=fuelSec.fuel_id
join weighttype as fuelSecwt on fuelSecwt.weighttype =
fuelSec.fueltype_id
join product as pro on aj.product_id=pro.product_id
join currency as cur on aj.currency_id = cur.currency_id
join rgd as d on aj.rgd_id = d.rgd_id
------------------------------------------------------------------------------------------------------------
On Oct 16, 9:21 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
>
> - Show quoted text -
Thomas Mueller
Oct 17, 2008, 4:50:45 AM10/17/08
to h2-da...@googlegroups.com
Hi,
Thanks for the test case! The optimizer took most of the time, it was
calculating the cost of too many query plans. I have now fixed this
problem, now the query takes less than 100 ms for me (the first time
is a bit slower).
Regards,
Thomas
Abyric
Oct 17, 2008, 10:23:01 PM10/17/08
to H2 Database
Danke schon!
I knew H2 was the better choice!
It absolutely works like a charm now!
again, Thanks!
On Oct 17, 10:50 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
I knew H2 was the better choice!
It absolutely works like a charm now!
again, Thanks!
On Oct 17, 10:50 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
Reply all
Reply to author
Forward
0 new messages