date:20160120

Re: [firebird-support] Firebird 3.0 RC1 - SQL optimization help

2016-01-20 Thread liviusliv...@poczta.onet.pl [firebird-support]

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: 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,Cupfrom (SELECTdbovwUNB.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(SELECTAdresKodu, IslemTuru, 
BelgeNo, barkod, olcubirimi FROMBAZLISTEwhere BelgeNo='REYSAS' AND 
AdresKodu='SAYIM' AND IslemTuru='SA'UNIONSELECTAdresKodu, IslemTuru, 
BelgeNo, barkod, olcubirimiFROMTERMINAL_SAYIMwhere BelgeNo='REYSAS' 
AND AdresKodu='SAYIM' AND IslemTuru='SA') asdbovwUNBLEFT OUTER JOINURUN ON 
dbovwUNB.olcubirimi = URUN.OlcuBirimi AND dbovwUNB.barkod = URUN.Barkod  LEFT 
OUTER JOIN(SELECTAdresKodu, IslemTuru, BelgeNo, Barkod, Miktar, 
OlcuBirimiFROMBAZLISTEwhere 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(SELECTAdresKodu , 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 (SELECTdbovwUNB.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(SELECTAdresKodu, IslemTuru, 
BelgeNo, barkod, olcubirimi FROMBAZLISTEwhere BelgeNo='REYSAS' AND 
AdresKodu='SAYIM' AND IslemTuru='SA'UNIONSELECTAdresKodu, IslemTuru,

Re: [firebird-support] Firebird 3.0 RC1 - SQL optimization help

2016-01-20 Thread Ertan Küçükoglu ertan.kucuko...@1nar.com.tr [firebird-support]

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, liviusliv...@poczta.onet.pl [firebird-support] 
>  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:firebird-support@yahoogroups.com
> Sent: Wednesday, January 20, 2016 4:57 PM
> To: 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 (
> SELECTdbovwUNB.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
> (SELECTAdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi 
> FROMBAZLISTE
> where BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'
> UNION
> SELECTAdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi
> FROMTERMINAL_SAYIM
> where BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'
> ) asdbovwUNB
> LEFT OUTER JOIN
> URUN ON dbovwUNB.olcubirimi = URUN.OlcuBirimi AND dbovwUNB.barkod = 
> URUN.Barkod  
> LEFT OUTER JOIN
> (SELECTAdresKodu, IslemTuru, BelgeNo, Barkod, Miktar, OlcuBirimi
> FROMBAZLISTE
> 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
> (SELECTAdresKodu , IslemTuru, BelgeNo, Barkod, OlcuBirimi , 
> SUM(Miktar) AS Miktar
> FROMTERMINAL_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

[firebird-support] Restore failed

2016-01-20 Thread Tim Ward t...@telensa.com [firebird-support]

gbak:adjusting an invalid decompression length from -33 to -19
gbak: ERROR:value exceeds the range for valid timestamps
gbak: ERROR:gds_$send failed
gbak:Exiting before completion due to errors

Anyone any idea what any of that means?

-- 
Tim Ward

[firebird-support] Firebird 3.0 RC1 - SQL optimization help

2016-01-20 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support]

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 (
SELECTdbovwUNB.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
(SELECTAdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi
FROMBAZLISTE
where BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'
UNION
SELECTAdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi
FROMTERMINAL_SAYIM
where BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'
) asdbovwUNB
LEFT OUTER JOIN
URUN ON dbovwUNB.olcubirimi = URUN.OlcuBirimi AND dbovwUNB.barkod =
URUN.Barkod
LEFT OUTER JOIN
(SELECTAdresKodu, IslemTuru, BelgeNo, Barkod, Miktar, OlcuBirimi
FROMBAZLISTE
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
(SELECTAdresKodu , IslemTuru, BelgeNo, Barkod, OlcuBirimi ,
SUM(Miktar) AS Miktar
FROMTERMINAL_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 (
SELECTdbovwUNB.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
(SELECTAdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi
FROMBAZLISTE
where BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'
UNION
SELECTAdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi
FROMTERMINAL_SAYIM
where BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'
) asdbovwUNB
LEFT OUTER JOIN
URUN ON dbovwUNB.olcubirimi = URUN.OlcuBirimi AND dbovwUNB.barkod =
URUN.Barkod
LEFT OUTER JOIN
(SELECTAdresKodu, IslemTuru, BelgeNo, Barkod, Miktar, OlcuBirimi
FROMBAZLISTE
where BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'
) as dbovwUBAZ

RE: [firebird-support] Firebird 3.0 RC1 - SQL optimization help

2016-01-20 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support]

 

 

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: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Thursday, January 21, 2016 12:35 AM
To: 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: liviusliv...@poczta.onet.pl   

Sent: Wednesday, January 20, 2016 11:17 PM

To: 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: 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 (
SELECTdbovwUNB.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
(SELECTAdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi 
FROMBAZLISTE
where BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'
UNION
SELECTAdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi
FROMTERMINAL_SAYIM
where BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'
) asdbovwUNB
LEFT OUTER JOIN
URUN ON dbovwUNB.olcubirimi = URUN.OlcuBirimi AND dbovwUNB.barkod = URUN.Barkod 
 
LEFT OUTER JOIN
(SELECTAdresKodu, IslemTuru, BelgeNo, Barkod, Miktar, OlcuBirimi
FROMBAZLISTE
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
(SELECTAdresKodu , IslemTuru, BelgeNo, Barkod, OlcuBirimi , SUM(Miktar) 
AS Miktar

Re: [firebird-support] Firebird 3.0 RC1 - SQL optimization help

2016-01-20 Thread 'Arno Brinkman' fbsupp...@abvisie.nl [firebird-support]

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





From: mailto:firebird-support@yahoogroups.com 
Sent: Wednesday, January 20, 2016 4:57 PM
To: 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,Cupfrom (SELECTdbovwUNB.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(SELECTAdresKodu, IslemTuru, 
BelgeNo, barkod, olcubirimi FROMBAZLISTEwhere BelgeNo='REYSAS' AND 
AdresKodu='SAYIM' AND IslemTuru='SA'UNIONSELECTAdresKodu, IslemTuru, 
BelgeNo, barkod, olcubirimiFROMTERMINAL_SAYIMwhere BelgeNo='REYSAS' 
AND AdresKodu='SAYIM' AND IslemTuru='SA') asdbovwUNBLEFT OUTER JOINURUN ON 
dbovwUNB.olcubirimi = URUN.OlcuBirimi AND dbovwUNB.barkod = URUN.Barkod  LEFT 
OUTER JOIN(SELECTAdresKodu, IslemTuru, BelgeNo, Barkod, Miktar, 
OlcuBirimiFROMBAZLISTEwhere 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(SELECTAdresKodu , 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 (SELECTdbovwUNB.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(SELECTAdresKodu, IslemTuru, 
BelgeNo, barkod, olcubirimi FROMBAZLISTEwhere BelgeNo='REYSAS' AND 
AdresKodu='SAYIM' AND

RE: [firebird-support] Firebird 3.0 RC1 - SQL optimization help

2016-01-20 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support]

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  

Sent: Wednesday, January 20, 2016 11:17 PM

To: 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 

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 ,

Re: [firebird-support] Firebird 3.0 RC1 - SQL optimization help

2016-01-20 Thread liviusliv...@poczta.onet.pl [firebird-support]

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: liviusliv...@poczta.onet.pl 
Sent: Wednesday, January 20, 2016 11:17 PM
To: 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: 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,Cupfrom (SELECTdbovwUNB.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(SELECTAdresKodu, IslemTuru, 
BelgeNo, barkod, olcubirimi FROMBAZLISTEwhere BelgeNo='REYSAS' AND 
AdresKodu='SAYIM' AND IslemTuru='SA'UNIONSELECTAdresKodu, IslemTuru, 
BelgeNo, barkod, olcubirimiFROMTERMINAL_SAYIMwhere BelgeNo='REYSAS' 
AND AdresKodu='SAYIM' AND IslemTuru='SA') asdbovwUNBLEFT OUTER JOINURUN ON 
dbovwUNB.olcubirimi = URUN.OlcuBirimi AND dbovwUNB.barkod = URUN.Barkod  LEFT 
OUTER JOIN(SELECTAdresKodu, IslemTuru, BelgeNo, Barkod, Miktar, 
OlcuBirimiFROMBAZLISTEwhere 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(SELECTAdresKodu , 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",

Re: [firebird-support] CPU Affinity

2016-01-20 Thread Hugo Eyng hugoe...@msn.com [firebird-support]


Hi Mark.

Thanks for your answer.

My reasoning was that more cores to process , more performance . However 
, in the light of your explanation I changed my way of thinking. Do you 
see any difference between using Classic Server and SuperClassic , 
except number of processes?




Em 19/01/2016 19:14, 'Mark Rotteveel' m...@lawinegevaar.nl 
[firebird-support] escreveu:


Setting the cpu affinity to target more than one cpu will usually be 
worse for performance, as SuperServer is essentially single threaded 
and allowing it to run on multiple cores will cause expensive 
rescheduling.


SuperServer may perform better than Classic because CS has a smaller 
page buffer by default, and the synchronisation between processes is 
more expensive than in-process. The scale however will usually tip in 
favor of classic when using a large number of connections, as 
SuperServer will eventually be limited by how much it can do in its 
single thread.


Mark


- Reply message -
Van: "Hugo Eyng hugoe...@msn.com [firebird-support]" 


Aan: 
Onderwerp: [firebird-support] CPU Affinity
Datum: di, jan. 19, 2016 21:01

Hi Thomas.

Thanks for your answer.

I use SuperServer.

Why? Performance. It seems to run faster.

Hugo


Em 19/01/2016 12:21, 'Thomas Steinmaurer' t...@iblogmanager.com 
[firebird-support] escreveu:


Hi!

> Dear Friends.
>
> FirebirdSQL : 2.5.5 64bits
> Operating System: Windows 2008 R2 Enterprise 64bits
> Server : Dell Poweredge R620
> 32GB
> 2 processors E5-2609 2.40 GHz
> 8 Cores
>
> To define CPU Affinity in firebird.conf I set CPUAffinity to 255, in a
> way to use all the 8 cores.

Which Firebird server architecture are you using? CPU affinity is 
only for SuperServer and the question is why do you bother with 
SuperServer on a multi-core machine when there is Classic or 
SuperClassic available?


--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

> Does it make sense or could be better to use less cores, like core
> 5,6,7,8 or 3,4,5,6?
>
> --
>
>
> Atenciosamente,
>
> Hugo Eyng
>
>
>
> 
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu. Try FAQ and other links from the left-side 
menu there.

>
> Also search the knowledgebases at 
http://www.ibphoenix.com/resources/documents/

>
>
> ++
> 
>
> Yahoo Groups Links
>
>
>



--


Atenciosamente,

Hugo Eyng



--


Atenciosamente,

Hugo Eyng

Re: [firebird-support] Performance Problem after Migration fb 2.1 cs to fb 2.5 cs both X86

2016-01-20 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support]

It is probably not relevant to your particular query, but you do have an
unusual way to write your joins and I believe (though I'm only 80% certain)
your 'nesting' style reduces the options for the optimizer (I think it can
only choose between TBESTELLUNGEN and TBESTPOS as the first table, though
since you probably want TBESTPOS anyway, it is not important unless you
decide to add another table). Sometimes (as part of optimizing a query),
that can be desirable, but only after finding the optimizer to come up with
a bad plan. Hence, I'd recommend that you change your query to (from the
FROM part):

FROM TKUNDEN
INNER JOIN TBESTELLUNGEN ON TKUNDEN.KUNDENNR = TBESTELLUNGEN.LIEFERANTNR
INNER JOIN TBESTPOS ON TBESTPOS.NR = TBESTELLUNGEN.NR AND TBESTPOS.JAHR =
TBESTELLUNGEN.JAHR
INNER JOIN TTEILE ON TTEILE.TEILENR = TBESTPOS.TEILENR
WHERE TBESTPOS.ABGESCHLOSSEN=-1
   AND TBESTPOS.TATSLT BETWEEN [forms]![fbestelleingang].[sa] AND
[forms]![fbestelleingang].[sb]
   AND UCase([tteile].[teilenr]) Like
nz(UCase([Formulare]![fbestelleingang]![st]) & "*","*")
   AND TKUNDEN.KUNDENNR Like nz([Formulare]![fbestelleingang]![sl],"*")
ORDER BY TBESTPOS.LIEFERTERMIN DESC , TBESTPOS.LIEFERZEIT DESC;

The reason I've removed lots of parenthesis, is simply because their main
purpose in a query containing only ANDs, is to confuse the reader of the
query. I don't understand nz([Formulare]![fbestelleingang]![sl],"*") and
similar parts of the query, I take it that it is Access transforming this
into some kind of parameter or constant. It can also be desireable to
change LIKE to STARTING if appropriate, since STARTING can use an index.

HTH,
Set

2016-01-20 7:07 GMT+01:00 'checkmail' check_m...@satron.de
[firebird-support] :

>
>
> Hello and good morning,
>
>
>
> the situation, firebird classic server 2.1 before, firebird classic server
> 2.5 (newest) after. The frontend is Microsoft Access, the tables odbc
> linked. Now, after the migration I open a Formular in Access, everything is
> fast, in less then one second the Data were displayed. But if I open the
> Formular the second time, I must wait 20 seconds. All Fields are indexed.
>
>
>
> The sql-code in Access
>
> SELECT TBESTELLUNGEN.JAHR, TBESTELLUNGEN.NR, TBESTPOS.LIEFERTERMIN,
> TBESTPOS.LIEFERZEIT, TBESTPOS.GELIEFERT, TTEILE.TEILENR,
> TTEILE.BEZEICHNUNG, TKUNDEN.FIRMA, TBESTELLUNGEN.GELIEFERT AS komplett,
> TBESTPOS.ANZAHL, TBESTPOS.GEWOGEN, TBESTPOS.BESTAETIGT,
> TBESTPOS.ABGESCHLOSSEN, TBESTPOS.TATSLT,
> IIf([einheitnr]=6,[tbestpos].[geliefert],[tbestpos].[geliefert]*[tteile].[gewicht])
> AS gewicht, UCase([tteile].[teilenr]) AS TNRG
>
> FROM TKUNDEN INNER JOIN (TTEILE INNER JOIN (TBESTPOS INNER JOIN
> TBESTELLUNGEN ON (TBESTPOS.NR = TBESTELLUNGEN.NR) AND (TBESTPOS.JAHR =
> TBESTELLUNGEN.JAHR)) ON TTEILE.TEILENR = TBESTPOS.TEILENR) ON
> TKUNDEN.KUNDENNR = TBESTELLUNGEN.LIEFERANTNR
>
> WHERE (((TBESTPOS.ABGESCHLOSSEN)=-1) AND
> ((TBESTPOS.TATSLT)>=[forms]![fbestelleingang].[sa] And
> (TBESTPOS.TATSLT)<=[forms]![fbestelleingang].[sb]) AND
> ((UCase([tteile].[teilenr])) Like
> nz(UCase([Formulare]![fbestelleingang]![st]) & "*","*")) AND
> ((TKUNDEN.KUNDENNR) Like nz([Formulare]![fbestelleingang]![sl],"*")))
>
> ORDER BY TBESTPOS.LIEFERTERMIN DESC , TBESTPOS.LIEFERZEIT DESC;
>
>
>
> It works fine with fb 2.1, but now.. What has been changed? The last odbc
> driver is installed
>
>
>
> Best regards
>
>
>
> Olaf
>
>
>
>
>
>
> 
>

Re: [firebird-support] Firebird 3.0 RC1 - SQL optimization help

Re: [firebird-support] Firebird 3.0 RC1 - SQL optimization help

[firebird-support] Restore failed

[firebird-support] Firebird 3.0 RC1 - SQL optimization help

RE: [firebird-support] Firebird 3.0 RC1 - SQL optimization help

Re: [firebird-support] Firebird 3.0 RC1 - SQL optimization help

RE: [firebird-support] Firebird 3.0 RC1 - SQL optimization help

Re: [firebird-support] Firebird 3.0 RC1 - SQL optimization help

Re: [firebird-support] CPU Affinity

Re: [firebird-support] Performance Problem after Migration fb 2.1 cs to fb 2.5 cs both X86

10 matches

Site Navigation

Mail list logo

Footer information