Does it mean that adding necessary indexes will save me time in the future and make me slower on new inserts now?
Or, does it take more than indexes for fine query performance even in large data tables? Sent from my iPhone > On 21 Jan 2016, at 19:08, liviusliv...@poczta.onet.pl [firebird-support] > <firebird-support@yahoogroups.com> wrote: > > Hi, > > as you can see in query plan – you have natural (full table scan). > And hash match you see in MSSQL “because” you have not optimized query. > Your query use a lot of RAM and CPU to return value. MSSQL built hash table > and join streams. > But this query will be slower and slower if your table wil be bigger and > bigger. > > Firebird have not implemented hash match for outer stream joins (if i > analyzed this ok). > And because your query is not optimized at all – you see very slow response > in Firebird. > > regards, > Karol Bieniaszewski > > From: mailto:firebird-support@yahoogroups.com > Sent: Thursday, January 21, 2016 5:02 PM > To: firebird-support@yahoogroups.com > Subject: RE: RE: [firebird-support] Firebird 3.0 RC1 - SQL optimization help > > > Hello, > > I will created indexes necessary. However, I think I cannot do anything about > Hash match. Is it making Firebird slower Hash match? I don't know what is > Hash match anyway. > > Thanks. > Ertan Küçükoğlu > > From: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] > Sent: Thursday, January 21, 2016 5:14 PM > To: firebird-support@yahoogroups.com > Subject: ODP: RE: [firebird-support] Firebird 3.0 RC1 - SQL optimization help > > hi, > > this is what i supposed. Hash match. And as Arno Brinkman say you have not > defined indexes on filtered columns > > regards, > Karol Bieniaszewski > > -------- Oryginalna wiadomość -------- > Od: "Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support]" > <firebird-support@yahoogroups.com> > Data: 21.01.2016 00:18 (GMT+01:00) > Do: firebird-support@yahoogroups.com > Temat: RE: [firebird-support] Firebird 3.0 RC1 - SQL optimization help > > Sorry, for my formatting. Here is a better one. I have uploaded image. Link: > http://s16.postimg.org/pz3iyujr9/Untitled.png > > From: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] > Sent: Thursday, January 21, 2016 1:13 AM > To: firebird-support@yahoogroups.com > Subject: RE: [firebird-support] Firebird 3.0 RC1 - SQL optimization help > > I’m all new to these things. Below is what I could find on MSSQL. I hope that > is what you are looking for. > > From: mailto:firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] > Sent: Thursday, January 21, 2016 12:35 AM > To: mailto:firebird-support@yahoogroups.com > Subject: Re: [firebird-support] Firebird 3.0 RC1 - SQL optimization help > > 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: mailto:liviusliv...@poczta.onet.pl <mailto:liviusliv...@poczta.onet.pl> > > Sent: Wednesday, January 20, 2016 11:17 PM > > To: mailto:firebird-support@yahoogroups.com > <mailto:firebird-support@yahoogroups.com> > > 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:firebird-support@yahoogroups.com > > Sent: Wednesday, January 20, 2016 4:57 PM > > To: mailto:firebird-support@yahoogroups.com > <mailto:firebird-support@yahoogroups.com> > > 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. > > [Non-text portions of this message have been removed] > > >
