I do not spend much time on this – maybe my conclusions are not good but also
you have not any index usage for this subquery
SELECT
AdresKodu,
IslemTuru,
BelgeNo,
barkod,
olcubirimi
FROM
TERMINAL_SAYIM
where
BelgeNo = 'REYSAS' AND AdresKodu = 'SAYIM' AND IslemTuru = 'SA'
what is the plan on mssql – is it hash join?
If yes then firebird 3 can use hash join only for inner join this is in plan to
support it for outer joins
regards,
Karol Bieniaszewski
From: [email protected]
Sent: Wednesday, January 20, 2016 11:17 PM
To: [email protected]
Subject: Re: [firebird-support] Firebird 3.0 RC1 - SQL optimization help
Hi,
i see that you have only partial key usage on 1/4
only BelgeNo is used because you do not use “BARKOD” – is this intentional?
FROM BAZLISTE XYZ
where
BelgeNo = 'REYSAS' AND AdresKodu = 'SAYIM' AND IslemTuru = 'SA'
regards,
Karol Bieniaszewski
From: mailto:[email protected]
Sent: Wednesday, January 20, 2016 4:57 PM
To: [email protected]
Subject: [firebird-support] Firebird 3.0 RC1 - SQL optimization help
Hi,
I have below tables.
CREATE TABLE BAZLISTE
(
ADRESKODU varchar(30) NOT NULL,
ISLEMTURU varchar(2) NOT NULL,
BELGENO varchar(30) NOT NULL,
BARKOD varchar(30) NOT NULL,
MIKTAR bigint,
OLCUBIRIMI varchar(10),
PRIMARY KEY (BELGENO,BARKOD,ADRESKODU,ISLEMTURU)
);
CREATE TABLE TERMINAL_SAYIM
(
LAPTOPID bigint generated by default as identity not null primary key,
DOSYAADI varchar(250) NOT NULL,
ELTERMINALIKAYITID bigint NOT NULL,
ADRESKODU varchar(30),
BELGENO varchar(30) NOT NULL,
BELGETARIHI date NOT NULL,
BELGEADI varchar(30),
LOKASYONKODU varchar(30) NOT NULL,
BOLGEKODU varchar(30) NOT NULL,
GOZKODU varchar(30) NOT NULL,
SSCC varchar(30),
SSCCKAPANDI char(1) CHARACTER SET ISO8859_1,
BARKOD varchar(30) NOT NULL,
MIKTAR integer NOT NULL,
OLCUBIRIMI varchar(10),
KONTROLSUZ varchar(1) NOT NULL,
TEKPARCA char(1) CHARACTER SET ISO8859_1 NOT NULL,
TEKPARCAINDEX smallint NOT NULL,
TERMINALID varchar(30),
KULLANICIKODU varchar(30),
OKUTMATARIHSAATI timestamp NOT NULL,
AKTARIMTARIHSAATI timestamp NOT NULL,
AKTARANKULLANICI varchar(30) NOT NULL,
SERINO varchar(25),
LOT varchar(25),
SKT varchar(10),
ISLEMTURU varchar(2),
ESLESTI varchar(1)
);
CREATE TABLE URUN
(
BARKOD varchar(30) NOT NULL primary key,
URUNKODU varchar(50),
URUNACIKLAMASI varchar(60),
URUNGRUBU varchar(30),
RENK varchar(20),
BEDEN varchar(20),
CUP varchar(20),
OLCUBIRIMI varchar(10),
SERINO varchar(1),
LOT varchar(1),
SKT varchar(1)
);
I am running below SQL which takes 7.5 minutes.
select AdresKodu as "Adres Kodu", IslemTuru as "İşlem Türü", BelgeNo as "Belge
No", Barkod, OlcuBirimi as "Ölçü Birimi",SayimMiktar as "Okutulan
Miktar",BazMiktar as "Baz Miktar",(SayimMiktar-BazMiktar) as Fark, UrunKodu as
"Ürün Kodu",UrunAciklamasi as "Ürün Açıklaması",UrunGrubu as "Ürün
Grubu",Renk,Beden,Cupfrom (SELECT dbovwUNB.AdresKodu,
dbovwUNB.IslemTuru, dbovwUNB.BelgeNo, dbovwUNB.barkod, dbovwUNB.olcubirimi,
coalesce(dbovwUT.Miktar,0) AS SayimMiktar, coalesce(dbovwUBAZ.Miktar, 0) AS
BazMiktar, URUN.UrunKodu, URUN.UrunAciklamasi, URUN.UrunGrubu, URUN.Renk,
URUN.Beden, URUN.CupFROM (SELECT AdresKodu, IslemTuru,
BelgeNo, barkod, olcubirimi FROM BAZLISTEwhere BelgeNo='REYSAS' AND
AdresKodu='SAYIM' AND IslemTuru='SA'UNIONSELECT AdresKodu, IslemTuru,
BelgeNo, barkod, olcubirimiFROM TERMINAL_SAYIMwhere BelgeNo='REYSAS'
AND AdresKodu='SAYIM' AND IslemTuru='SA') as dbovwUNBLEFT OUTER JOINURUN ON
dbovwUNB.olcubirimi = URUN.OlcuBirimi AND dbovwUNB.barkod = URUN.Barkod LEFT
OUTER JOIN(SELECT AdresKodu, IslemTuru, BelgeNo, Barkod, Miktar,
OlcuBirimiFROM BAZLISTEwhere BelgeNo='REYSAS' AND AdresKodu='SAYIM'
AND IslemTuru='SA') as dbovwUBAZ ON dbovwUNB.olcubirimi = dbovwUBAZ.OlcuBirimi
AND dbovwUNB.barkod = dbovwUBAZ.Barkod AND dbovwUNB.BelgeNo = dbovwUBAZ.BelgeNo
AND dbovwUNB.IslemTuru = dbovwUBAZ.IslemTuru AND dbovwUNB.AdresKodu =
dbovwUBAZ.AdresKodu LEFT OUTER JOIN(SELECT AdresKodu , IslemTuru,
BelgeNo, Barkod, OlcuBirimi , SUM(Miktar) AS MiktarFROM
TERMINAL_SAYIMwhere BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'
and TekParca='H'GROUP BY BelgeNo, Barkod, OlcuBirimi, IslemTuru, AdresKodu,
BelgeNo) as dbovwUT ON dbovwUNB.olcubirimi = dbovwUT.OlcuBirimi AND
dbovwUNB.barkod = dbovwUT.Barkod AND dbovwUNB.BelgeNo = dbovwUT.BelgeNo AND
dbovwUNB.IslemTuru = dbovwUT.IslemTuru AND dbovwUNB.AdresKodu =
dbovwUT.AdresKodu) as BazKarsilastirma
I read below PLAN generated before executing the SQL in Flamerobin:
Preparing statement: select AdresKodu as "Adres Kodu", IslemTuru as "İşlem
Türü", BelgeNo as "Belge No", Barkod, OlcuBirimi as "Ölçü Birimi",SayimMiktar
as "Okutulan Miktar",BazMiktar as "Baz Miktar",(SayimMiktar-BazMiktar) as Fark,
UrunKodu as "Ürün Kodu",UrunAciklamasi as "Ürün Açıklaması",UrunGrubu as "Ürün
Grubu",Renk,Beden,Cupfrom (SELECT dbovwUNB.AdresKodu,
dbovwUNB.IslemTuru, dbovwUNB.BelgeNo, dbovwUNB.barkod, dbovwUNB.olcubirimi,
nullif(dbovwUT.Miktar,0) AS SayimMiktar, nullif(dbovwUBAZ.Miktar, 0) AS
BazMiktar, URUN.UrunKodu, URUN.UrunAciklamasi, URUN.UrunGrubu, URUN.Renk,
URUN.Beden, URUN.CupFROM (SELECT AdresKodu, IslemTuru,
BelgeNo, barkod, olcubirimi FROM BAZLISTEwhere BelgeNo='REYSAS' AND
AdresKodu='SAYIM' AND IslemTuru='SA'UNIONSELECT AdresKodu, IslemTuru,
BelgeNo, barkod, olcubirimiFROM TERMINAL_SAYIMwhere BelgeNo='REYSAS'
AND AdresKodu='SAYIM' AND IslemTuru='SA') as dbovwUNBLEFT OUTER JOINURUN ON
dbovwUNB.olcubirimi = URUN.OlcuBirimi AND dbovwUNB.barkod = URUN.Barkod LEFT
OUTER JOIN(SELECT AdresKodu, IslemTuru, BelgeNo, Barkod, Miktar,
OlcuBirimiFROM BAZLISTEwhere BelgeNo='REYSAS' AND AdresKodu='SAYIM'
AND IslemTuru='SA') as dbovwUBAZ ON dbovwUNB.olcubirimi = dbovwUBAZ.OlcuBirimi
AND dbovwUNB.barkod = dbovwUBAZ.Barkod AND dbovwUNB.BelgeNo = dbovwUBAZ.BelgeNo
AND dbovwUNB.IslemTuru = dbovwUBAZ.IslemTuru AND dbovwUNB.AdresKodu =
dbovwUBAZ.AdresKodu LEFT OUTER JOIN(SELECT AdresKodu , IslemTuru,
BelgeNo, Barkod, OlcuBirimi , SUM(Miktar) AS MiktarFROM
TERMINAL_SAYIMwhere BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'
and TekParca='H'GROUP BY BelgeNo, Barkod, OlcuBirimi, IslemTuru, AdresKodu,
BelgeNo) as dbovwUT ON dbovwUNB.olcubirimi = dbovwUT.OlcuBirimi AND
dbovwUNB.barkod = dbovwUT.Barkod AND dbovwUNB.BelgeNo = dbovwUT.BelgeNo AND
dbovwUNB.IslemTuru = dbovwUT.IslemTuru AND dbovwUNB.AdresKodu =
dbovwUT.AdresKodu) as BazKarsilastirma Statement prepared (elapsed time:
0.000s).Field #01: . Alias:Adres Kodu Type:STRING(30)Field #02: . Alias:İşlem
Türü Type:STRING(2)Field #03: . Alias:Belge No Type:STRING(30)Field #04: .
Alias:BARKOD Type:STRING(30)Field #05: . Alias:Ölçü Birimi Type:STRING(10)Field
#06: . Alias:Okutulan Miktar Type:BIGINTField #07: . Alias:Baz Miktar
Type:BIGINTField #08: .SUBTRACT Alias:FARK Type:BIGINTField #09: URUN.URUNKODU
Alias:Ürün Kodu Type:STRING(50)Field #10: URUN.URUNACIKLAMASI Alias:Ürün
Açıklaması Type:STRING(60)Field #11: URUN.URUNGRUBU Alias:Ürün Grubu
Type:STRING(30)Field #12: URUN.RENK Alias:RENK Type:STRING(20)Field #13:
URUN.BEDEN Alias:BEDEN Type:STRING(20)Field #14: URUN.CUP Alias:CUP
Type:STRING(20)PLAN JOIN (JOIN (JOIN (SORT (BAZKARSILASTIRMA DBOVWUNB BAZLISTE
INDEX (RDB$PRIMARY3), BAZKARSILASTIRMA DBOVWUNB TERMINAL_SAYIM NATURAL),
BAZKARSILASTIRMA URUN INDEX (RDB$PRIMARY2)), BAZKARSILASTIRMA DBOVWUBAZ
BAZLISTE INDEX (RDB$PRIMARY3)), SORT (BAZKARSILASTIRMA DBOVWUT TERMINAL_SAYIM
NATURAL))Script execution finished.Preparing statement: select AdresKodu as
"Adres Kodu", IslemTuru as "İşlem Türü", BelgeNo as "Belge No", Barkod,
OlcuBirimi as "Ölçü Birimi",SayimMiktar as "Okutulan Miktar",BazMiktar as "Baz
Miktar",(SayimMiktar-BazMiktar) as Fark, UrunKodu as "Ürün Kodu",UrunAciklamasi
as "Ürün Açıklaması",UrunGrubu as "Ürün Grubu",Renk,Beden,Cupfrom (SELECT
dbovwUNB.AdresKodu, dbovwUNB.IslemTuru, dbovwUNB.BelgeNo, dbovwUNB.barkod,
dbovwUNB.olcubirimi, nullif(dbovwUT.Miktar,0) AS SayimMiktar,
nullif(dbovwUBAZ.Miktar, 0) AS BazMiktar, URUN.UrunKodu, URUN.UrunAciklamasi,
URUN.UrunGrubu, URUN.Renk, URUN.Beden, URUN.CupFROM (SELECT
AdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi FROM BAZLISTEwhere
BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'UNIONSELECT
AdresKodu, IslemTuru, BelgeNo, barkod, olcubirimiFROM
TERMINAL_SAYIMwhere BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA')
as dbovwUNBLEFT OUTER JOINURUN ON dbovwUNB.olcubirimi = URUN.OlcuBirimi AND
dbovwUNB.barkod = URUN.Barkod LEFT OUTER JOIN(SELECT AdresKodu,
IslemTuru, BelgeNo, Barkod, Miktar, OlcuBirimiFROM BAZLISTEwhere
BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA') as dbovwUBAZ ON
dbovwUNB.olcubirimi = dbovwUBAZ.OlcuBirimi AND dbovwUNB.barkod =
dbovwUBAZ.Barkod AND dbovwUNB.BelgeNo = dbovwUBAZ.BelgeNo AND
dbovwUNB.IslemTuru = dbovwUBAZ.IslemTuru AND dbovwUNB.AdresKodu =
dbovwUBAZ.AdresKodu LEFT OUTER JOIN(SELECT AdresKodu , IslemTuru,
BelgeNo, Barkod, OlcuBirimi , SUM(Miktar) AS MiktarFROM
TERMINAL_SAYIMwhere BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'
and TekParca='H'GROUP BY BelgeNo, Barkod, OlcuBirimi, IslemTuru, AdresKodu,
BelgeNo) as dbovwUT ON dbovwUNB.olcubirimi = dbovwUT.OlcuBirimi AND
dbovwUNB.barkod = dbovwUT.Barkod AND dbovwUNB.BelgeNo = dbovwUT.BelgeNo AND
dbovwUNB.IslemTuru = dbovwUT.IslemTuru AND dbovwUNB.AdresKodu =
dbovwUT.AdresKodu) as BazKarsilastirma Statement prepared (elapsed time:
0.002s).Field #01: . Alias:Adres Kodu Type:STRING(30)Field #02: . Alias:İşlem
Türü Type:STRING(2)Field #03: . Alias:Belge No Type:STRING(30)Field #04: .
Alias:BARKOD Type:STRING(30)Field #05: . Alias:Ölçü Birimi Type:STRING(10)Field
#06: . Alias:Okutulan Miktar Type:BIGINTField #07: . Alias:Baz Miktar
Type:BIGINTField #08: .SUBTRACT Alias:FARK Type:BIGINTField #09: URUN.URUNKODU
Alias:Ürün Kodu Type:STRING(50)Field #10: URUN.URUNACIKLAMASI Alias:Ürün
Açıklaması Type:STRING(60)Field #11: URUN.URUNGRUBU Alias:Ürün Grubu
Type:STRING(30)Field #12: URUN.RENK Alias:RENK Type:STRING(20)Field #13:
URUN.BEDEN Alias:BEDEN Type:STRING(20)Field #14: URUN.CUP Alias:CUP
Type:STRING(20)PLAN JOIN (JOIN (JOIN (SORT (BAZKARSILASTIRMA DBOVWUNB BAZLISTE
INDEX (RDB$PRIMARY3), BAZKARSILASTIRMA DBOVWUNB TERMINAL_SAYIM NATURAL),
BAZKARSILASTIRMA URUN INDEX (RDB$PRIMARY2)), BAZKARSILASTIRMA DBOVWUBAZ
BAZLISTE INDEX (RDB$PRIMARY3)), SORT (BAZKARSILASTIRMA DBOVWUT TERMINAL_SAYIM
NATURAL)) Executing statement...Statement executed (elapsed time:
0.000s).352138680 fetches, 2 marks, 799742 reads, 2 writes.0 inserts, 0
updates, 0 deletes, 6385 index, 172642730 seq.Delta memory: 35627456
bytes.Total execution time: 0:07:25 (hh:mm:ss)Script execution finished.
You can download Firebird 3.0 Backup file (GBAK) from here:
https://mega.nz/#!RQxQiT6D!fNIAU3VBpHC-2vKKsez_x4tAF9M5B7ZqwProEqVskXA
You can download MSSQL Backup file from here:
https://mega.nz/#!hQhzlahL!5yGNB8rL_Y1KoFLNQNKYNy8ba1mtXaA6_dWQrHE0bY8
Same query runs on MSSQL server *on same* computer much more faster. Like
execution time displayed is '00:00:00' and result set displayed immediately.
I didn't prepare this SQL, just migrating a software to a new database.
However, 7.5 minutes is very long time. Is there anything that can be done with
it?
Purpose of the query is to compare computer inventory records and physical
inventory counting records.
