Yes, this is information I need. Is it better to have a full index? Sent from my iPhone
> On 21 Jan 2016, at 00:17, [email protected] [firebird-support] > <[email protected]> wrote: > > 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,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 ( > 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.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: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)) > > > 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. > >
