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" <[EMAIL PROTECTED]>
wrote:
> Hi,
>
> Could you post the data or a test case with random data?
>
> Regards,
> Thomas
>
>
>
> On Thu, Oct 16, 2008 at 5:01 AM, Abyric <[EMAIL PROTECTED]> wrote:
>
> > 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?- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
