Subject Re: [firebird-support] Firebird 3.0 RC1 - SQL optimization help
Author Arno Brinkman
Post date 2016-01-20T23:37:40Z
Hello,
 
 
You have no indices defined at all.
While i don’t understand your system i cannot really say what indices you should add, but these will at least help:
 
CREATE ASC INDEX I_TERMINAL_SAYIM ON TERMINAL_SAYIM (BELGENO, ADRESKODU, ISLEMTURU, BARKOD, OLCUBIRIMI, TEKPARCA);
 
Kind Regards,
Arno Brinkman
 
 
 
 
Sent: Wednesday, January 20, 2016 4:57 PM
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,Cup
from (
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.Cup
FROM            
(SELECT        AdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi 
FROM            BAZLISTE
where BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'
UNION
SELECT        AdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi
FROM            TERMINAL_SAYIM
where BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'
) as    dbovwUNB
LEFT OUTER JOIN
URUN ON dbovwUNB.olcubirimi = URUN.OlcuBirimi AND dbovwUNB.barkod = URUN.Barkod  
LEFT OUTER JOIN
(SELECT        AdresKodu, IslemTuru, BelgeNo, Barkod, Miktar, OlcuBirimi
FROM            BAZLISTE
where 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 Miktar
FROM            TERMINAL_SAYIM
where 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,Cup
from (
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.Cup
FROM            
(SELECT        AdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi 
FROM            BAZLISTE
where BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'
UNION
SELECT        AdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi
FROM            TERMINAL_SAYIM
where BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'
) as    dbovwUNB
LEFT OUTER JOIN
URUN ON dbovwUNB.olcubirimi = URUN.OlcuBirimi AND dbovwUNB.barkod = URUN.Barkod  
LEFT OUTER JOIN
(SELECT        AdresKodu, IslemTuru, BelgeNo, Barkod, Miktar, OlcuBirimi
FROM            BAZLISTE
where 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 Miktar
FROM            TERMINAL_SAYIM
where 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:BIGINT
Field #07: . Alias:Baz Miktar Type:BIGINT
Field #08: .SUBTRACT Alias:FARK Type:BIGINT
Field #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,Cup
from (

(Message over 64 KB, truncated)