Icon View Incident Report

Serious Serious
Reported By: Nenad Steric
Reported On: 7/12/2013
For: Version 4.36 Build 1
# 3869 LEFT OUTER JOINs with Join Conditions Referencing More Than Two Tables Executing Slowly

I have the following query. Using the DB System Util - with 4.30b6 (our previous version) this takes around 50 secs, with 4.36b1 the query times out and after the "reconnect ?" etc. dialog it shows an "invalid or unknown request ..."error
after that i have no access to the remote db server (which is in fact a local 127.0.0.1)

another test - 4.35 seems to be ok (~50 secs)

select 
distinct protokollnr
from antrag a
inner join AntragTPos atp on atp.protokollnr=a.protokollnr
left join tarifpos tp on tp.tpos=atp.tpos and tp.kassa=a.kasse and atp.ZeitraumID=tp.KZeitraumId
where
a.verrechnungsmonat>='2013-04-01' and a.verrechnungsmonat<'2013-07-01'
and tp.tpos is null

/* SQL-92 Table Creation Script with DBISAM Extensions */

CREATE TABLE IF NOT EXISTS "ANTRAG"
(
   "ProtokollNr" INTEGER NOT NULL,
   "Datum" DATE,
   "EinsenderID" INTEGER NOT NULL,
   "StationID" INTEGER,
   "EinsenderProt_Nr" VARCHAR(40),
   "PatientID" INTEGER NOT NULL,
   "Material" INTEGER,
   "Anamnese" MEMO,
   "Bemerkung" MEMO,
   "Abrechnung_Art" VARCHAR(1) DEFAULT 'V',
   "Abr_Privat_Adresse" VARCHAR(150),
   "Abr_Privat_PLZ" VARCHAR(10),
   "Abr_Privat_Ort" VARCHAR(40),
   "Kasse" VARCHAR(50),
   "Versicherung" VARCHAR(50),
   "EntnahmeDatum" DATE,
   "AnPatientschicken" BOOLEAN DEFAULT False,
   "Einsender2ID" INTEGER,
   "Station2ID" INTEGER,
   "Einsender2Prot_Nr" VARCHAR(40),
   "Einsender3ID" INTEGER,
   "Station3ID" INTEGER,
   "Einsender4ID" INTEGER,
   "Station4ID" INTEGER,
   "Material_Bemerkung" MEMO,
   "Klin_Diagnose" MEMO,
   "MV" INTEGER,
   "InfoText" VARCHAR(20),
   "VerrechnungsMonat" DATE,
   "AbrechnungAdresseExtra" MEMO,
   "NurVerrechnen" BOOLEAN DEFAULT False,
PRIMARY KEY ("ProtokollNr") COMPRESS FULL
LOCALE CODE 1031
);

CREATE NOCASE INDEX IF NOT EXISTS "DatumIndx" ON "ANTRAG" ("Datum") COMPRESS FULL;
CREATE NOCASE INDEX IF NOT EXISTS "FremdIndx" ON "ANTRAG" ("EinsenderProt_Nr") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "PatientID" ON "ANTRAG" ("PatientID") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "EinsenderID" ON "ANTRAG" ("EinsenderID") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "VerrechnungsMonatIndx" ON "ANTRAG" ("VerrechnungsMonat");

CREATE TABLE IF NOT EXISTS "AntragTPos"
(
   "AntragTPosId" AUTOINC,
   "ProtokollNr" INTEGER NOT NULL,
   "TPos" VARCHAR(8) NOT NULL,
   "Count" INTEGER DEFAULT 1,
   "ZeitraumID" INTEGER,
PRIMARY KEY ("ProtokollNr","AntragTPosId") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
);


CREATE TABLE IF NOT EXISTS "TarifPos"
(
   "TarifPosID" AUTOINC,
   "Kassa" VARCHAR(10) NOT NULL,
   "TPos" VARCHAR(10) NOT NULL,
   "KZeitraumID" INTEGER NOT NULL DEFAULT 1,
   "Tarif" MONEY NOT NULL,
   "PosExtern" VARCHAR(20) NOT NULL,
PRIMARY KEY ("Kassa","TPos","KZeitraumID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
);



Resolution Resolution
Fixed Problem on 8/2/2013 in version 4.36 build 3


Products Affected Products Affected
DBISAM Additional Software and Utilities
DBISAM CLX Client-Server
DBISAM CLX Client-Server with Source
DBISAM CLX Standard
DBISAM CLX Standard with Source
DBISAM ODBC Client-Server
DBISAM ODBC Client-Server with Source
DBISAM ODBC Standard
DBISAM ODBC Standard with Source
DBISAM ODBC Trial
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source
DBISAM VCL Trial

Image