Re: [firebird-support] Installing Firebird 2.5 on Debian 9 Stretch

2019-01-21 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support] 
Hello,

Sent from my iPhone

> On 21 Jan 2019, at 11:48, Gabor Boros mlngl...@bgss.hu [firebird-support] 
>  wrote:
> 
> 2019. 01. 21. 9:37 keltezéssel, Ertan Küçükoğlu 
> ertan.kucuko...@1nar.com.tr [firebird-support] írta:
> > 1- Is there easier way to install firebird 2.5 on this Debian 9 Stretch
> > server?
> 
> Yes. I always use the official binary tar.gz and works like a charm 
> (with Debian 9 and 10, CentOS 7, etc.).
> 
I didn't think of that as a solution. That did work just fine for me as well.

Thanks.
> 
> Classic/SuperClassic:
> 
> https://github.com/FirebirdSQL/firebird/releases/download/R2_5_8/FirebirdCS-2.5.8.27089-0.amd64.tar.gz
> 
> SuperServer:
> 
> https://github.com/FirebirdSQL/firebird/releases/download/R2_5_8/FirebirdSS-2.5.8.27089-0.amd64.tar.gz
> 
> Gabor
> 
>

[firebird-support] Installing Firebird 2.5 on Debian 9 Stretch

2019-01-21 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support] 
Hello,

There was an old server running Debian 7. That server crashed and had to be
installed. Service provide did not have Debian 7 in install OS list and now
it runs Debian 9. Seemingly, Debian removed 2.5 packages from apt-get type
of install.

I went down the road to compile my own firebird 2.5 on AMD64 from sources.
Downloaded 2.5 sources from web site provided link. I did not know how to
fetch latest sources from git hub.

All was good until I receive below compile error:
make[4]: Entering directory
'/root/Firebird-2.5.8.27089-0/extern/icu/source/layout'
g++ -D_REENTRANT -I. -I../common -I../common -I./unicode -I./.. -D_REENTRANT
-DHAVE_CONFIG_H -DU_LAYOUT_IMPLEMENTATION -O  -c -DPIC -fPIC -o
IndicClassTables.o IndicClassTables.cpp
IndicClassTables.cpp:229:117: error: narrowing conversion of '2147483651u'
from 'unsigned int' to 'icu_3_0::IndicClassTable::ScriptFlags {aka int}'
inside { } [-Wnarrowing]
 luClassTable = {0x0C00, 0x0C6F, 3, TELU_SCRIPT_FLAGS, teluCharClasses,
teluSplitTable};
 
^
IndicClassTables.cpp:231:117: error: narrowing conversion of '2147483651u'
from 'unsigned int' to 'icu_3_0::IndicClassTable::ScriptFlags {aka int}'
inside { } [-Wnarrowing]
 daClassTable = {0x0C80, 0x0CEF, 4, KNDA_SCRIPT_FLAGS, kndaCharClasses,
kndaSplitTable};
 
^
.../config/mh-linux:43: recipe for target 'IndicClassTables.o' failed
make[4]: *** [IndicClassTables.o] Error 1
make[4]: Leaving directory
'/root/Firebird-2.5.8.27089-0/extern/icu/source/layout'
Makefile:98: recipe for target 'all-recursive' failed
make[3]: *** [all-recursive] Error 2
make[3]: Leaving directory '/root/Firebird-2.5.8.27089-0/extern/icu/source'
.../gen/Makefile.extern.icu:39: recipe for target 'libicu' failed
make[2]: *** [libicu] Error 2
make[2]: Leaving directory '/root/Firebird-2.5.8.27089-0/gen'
Makefile:193: recipe for target 'external_libraries' failed
make[1]: *** [external_libraries] Error 2
make[1]: Leaving directory '/root/Firebird-2.5.8.27089-0/gen'
Makefile:6: recipe for target 'firebird' failed
make: *** [firebird] Error 2


My questions are:
1- Is there easier way to install firebird 2.5 on this Debian 9 Stretch
server?
2- Is there a way to quick fix that compile error? (I have little to no
knowledge of C++)

Any help is appreciated.

Thanks & regards,
Ertan Küçükoğlu







++

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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Re: Firebird performance vs PostgreSQL

2017-09-29 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support] 
Hello Dmitry,

Thank you for your time. I just provided a reproducible sample. I'm not trying 
to make fine tuning to FirebirdSQL or PostgreSQL.

Answer to your question is yes. KAYNAGAGORE and SEBEBEGORE are not indexed. 
Please note that they're not indexed on PostgreSQL, too.

Question remains as how PostgreSQL can cope with identical data only with 
primary key indexes faster than FirebirdSQL?

I'm not experienced with both database systems internals. I'm not very good at 
reading query plans be that FirebirdSQL or PostgreSQL. So, it maybe plain 
reading plan that puts FirebirdSQL slower as you indicated.

Thanks & regards,
Ertan Küçükoğlu


Sent from my iPhone

> On 29 Sep 2017, at 08:34, Dmitry Yemanov dim...@users.sourceforge.net 
> [firebird-support]  wrote:
> 
> 29.09.2017 01:08, Ertan Küçükoğlu wrote:
> 
> Answers can be found in the plans:
> 
> > PLAN JOIN (RAPOR_EK ORDER rapor_ek_pkey, SORT (DT M NATURAL))
> 
> This join plan is terrible for FB. Are KAYNAGAGORE and SEBEBEGORE 
> indexed? They seem not.
> 
> > Merge Left Join (cost=6600.19..6610.98 rows=560 width=340)
> 
> FB cannot perform merge- or hash-join for outer joins (yet). When 
> important indices are missing, this becomes a problem.
> 
> Dmitry
> 
>

RE: [firebird-support] Firebird performance vs PostgreSQL

2017-09-28 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support] 
> From: mailto:firebird-support@yahoogroups.com 
> [mailto:firebird-support@yahoogroups.com] 
> Sent: Thursday, September 28, 2017 7:35 PM
> To: mailto:firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] Firebird performance vs PostgreSQL
>
> 
> Hi,
>
> without any sample you talking about nothing..
> Show some sample which can be reproduced.
> Without this i can say same Firebird is faster then Oracle and what someone 
> can say about this – nothing 
> 
> regards,
> Karol Bieniaszewski

Hello again,

As forum is not allowing attachments. Necessary files can be found in below 
link:
https://paste.ee/p/1gBQl

RE: [firebird-support] Firebird performance vs PostgreSQL

2017-09-28 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support] 
> From: firebird-support@yahoogroups.com 
> [mailto:firebird-support@yahoogroups.com] 
> Sent: Thursday, September 28, 2017 7:35 PM
> To: firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] Firebird performance vs PostgreSQL
>
>  
> Hi,
>
> without any sample you talking about nothing..
> Show some sample which can be reproduced.
> Without this i can say same Firebird is faster then Oracle and what someone 
> can say about this – nothing 
> 
> regards,
> Karol Bieniaszewski

Hello,

I am only providing details for above question. Not trying to say this is 
better or worse or anything.

- SQL statements to create necessary database tables can be found in attached 
*.SQL files.
- Followig link includes "insert into" statements which can be used for loading 
necessary data (around 100.000 records) on both FirebirdSQL and PostgreSQL 
databases.
http://s5.dosya.tc/server5/3dard7/insert_sql_statements.rar.html
- FirebirdSQL database should be created using WIN1254 character set.
- PostgreSQL database should be created as follows
For Linux:
CREATE DATABASE pgtest ENCODING 'UTF-8' LC_COLLATE 'tr_TR.UTF-8' TEMPLATE 
template0;
For Windows: 
CREATE DATABASE pgtest ENCODING 'UTF-8' LC_COLLATE 'tr-TR.UTF-8' TEMPLATE 
template0;
or
CREATE DATABASE pgtest ENCODING 'UTF8' LC_COLLATE 'Turkish_Turkey.1254' 
LC_CTYPE = 'Turkish_Turkey.1254' TEMPLATE template0;

- Query used on both database systems is as follows:
SELECT 
  RAPOR_EK.KAYNAK, 
  RAPOR_EK.SEBEP, 
  COALESCE(DT.IAIK_OG, 0) AS IAIK_OG,
  COALESCE(DT.IAIK_AG, 0) AS IAIK_AG,
  COALESCE(DT.IAIK_TOPLAM, 0) AS IAIK_TOPLAM,
  COALESCE(DT.IADK_OG, 0) AS IADK_OG,
  COALESCE(DT.IADK_AG, 0) AS IADK_AG,
  COALESCE(DT.IADK_TOPLAM, 0) AS IADK_TOPLAM,
  COALESCE(DT.GNLTOPLAM, 0) AS GNLTOPLAM
FROM RAPOR_EK
LEFT JOIN (
  SELECT 
M.KAYNAGAGORE AS KAYNAK, 
M.SEBEBEGORE AS SEBEP, 
(sum(M.ES_IIOG)/(select sum(I.IIOG) from sabitler I where kaynak = 
'CİHANBEYLİ')*60) as IAIK_OG,
(sum(M.ES_IIAG)/(select sum(I.IIAG) from sabitler I where kaynak = 
'CİHANBEYLİ')*60) as IAIK_AG,
((sum(M.ES_IIOG) + sum(M.ES_IIAG)) / ((select sum(I.IIOG) from sabitler I 
where kaynak = 'CİHANBEYLİ')  + (select sum(I.IIAG) from sabitler I where 
kaynak = 'CİHANBEYLİ'))*60) AS IAIK_TOPLAM,
(sum(M.ES_IDOG)/(select sum(I.IDOG) from sabitler I where kaynak = 
'CİHANBEYLİ')*60) as IADK_OG,
(sum(M.ES_IDAG)/(select sum(I.IDAG) from sabitler I where kaynak = 
'CİHANBEYLİ')*60) as IADK_AG,
((sum(M.ES_IDOG) + sum(M.ES_IDAG)) / ((select sum(I.IDOG) from sabitler I 
where kaynak = 'CİHANBEYLİ')  + (select sum(I.IDAG) from sabitler I where 
kaynak = 'CİHANBEYLİ'))*60) AS IADK_TOPLAM,
((sum(M.ES_IIOG) + sum(M.ES_IIAG) + (sum(M.ES_IDOG) + sum(M.ES_IDAG))) / 
((select sum(I.toplam) from sabitler I where kaynak = 'CİHANBEYLİ')*60)) AS 
GNLTOPLAM 
  FROM veri M
  WHERE 
M.ILCE = 'CİHANBEYLİ'
AND M.BILDIRIMEGORE = 'Bildirimsiz'
AND M.SUREYEGORE = 'Uzun'
AND (M.baslangic >= '2015-01-01' AND bitis <= '2015-01-31 23:59:59.999')
  GROUP BY 
M.KAYNAGAGORE,
M.SEBEBEGORE
) DT USING (KAYNAK, SEBEP)
ORDER BY KAYNAK, SEBEP;

- Query plan for both databases can be found in attached *.TXT files.

- My configuration i7-4720HQ cpu, 8GB RAM, Windows 10 64bit, 512GB Sandisk 
Extreme Pro SSD 6GB capability on 6GB connection.
- PostgreSQL version 9.6.4 64bit (installed using Enterprise DB binaries)
- FirebirdSQL version 3.0.1.32609 64bit (official installation) 
- Both database servers have default configuration files. Nothing changed, or 
tweaked.
- I read following figures for above SQL statement execution on a freshly 
created database, freshly loaded data
=> FirebirdSQL using flamerobin.exe to execute (I failed to find a way to 
execute an SQL file with stats on using isql.exe): 1.505 seconds after computer 
reboot, 1.379 seconds for 2nd, 1.381 seconds for 3rd.
=> PostgreSQL using psql.exe to execute: 0.252 seconds after computer reboot, 
0.029 seconds on 2nd, 0.024 seconds on 3rd

You may have additional questions, I try my best to answer within my knowledge.

Regards,
Ertan Küçükoğlu


  --

Field #01: RAPOR_EK.KAYNAK Alias:KAYNAK Type:STRING(20)
Field #02: RAPOR_EK.SEBEP Alias:SEBEP Type:STRING(20)
Field #03: .COALESCE Alias:IAIK_OG Type:NUMERIC(18,2)
Field #04: .COALESCE Alias:IAIK_AG Type:NUMERIC(18,2)
Field #05: .COALESCE Alias:IAIK_TOPLAM Type:NUMERIC(18,2)
Field #06: .COALESCE Alias:IADK_OG Type:NUMERIC(18,2)
Field #07: .COALESCE Alias:IADK_AG Type:NUMERIC(18,2)
Field #08: .COALESCE Alias:IADK_TOPLAM Type:NUMERIC(18,2)
Field #09: .COALESCE Alias:GNLTOPLAM Type:NUMERIC(18,2)
PLAN (DT I INDEX (RDB$PRIMARY2))
PLAN (DT I INDEX (RDB$PRIMARY2))
PLAN (DT I INDEX (RDB$PRIMARY2))
PLAN (DT I INDEX (RDB$PRIMARY2))
PLAN (DT I INDEX (RDB$PRIMARY2))
PLAN (DT I INDEX (RDB$PRIMARY2))
PLAN (DT I INDEX (RDB$PRIMARY2))
PLAN (DT I INDEX (RDB$PRIMARY2))
PLAN (DT I INDEX (RDB$PRIMARY2))
PLAN JOIN (RAPOR_EK ORDER rapor_ek_pkey, SORT (DT M NATURAL))
  --

Merge

Re: [firebird-support] Rename table

2017-07-16 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support] 

Sent from my iPhone

> On 16 Jul 2017, at 11:58, Alain Bastien alainbast...@gmail.com 
> [firebird-support]  wrote:
> 
> Else "What's in a NAME?" 
> 
> 
> 
> 
> 
> 
> 
> 2017-07-16 12:47 GMT+04:00 Alain Bastien :
>> Hi,
>> 
>> Block data entrance ?

Hello,

We do not know how busy table is. There may be new data entered and data moving 
application is already working on an executed query data which doesn't include 
those new rows. 

Because of that possible data loose I would strong suggest one should stop new 
data entrance while doing such operation on a production database.

Regards,
Ertan


>> 
>> I have never tested a situation where data is entering a database/ table 
>> and I rename a single field , or column or table and even the Database 
>> itself 
>> 
>> Can you please guest or anticipate the respond of the server's or the 
>> application's error handling and/or exceptions ?
>> 
>> Would there be ?
>> 
>> regards
>> 
> 
>

Re: [firebird-support] Rename table

2017-07-16 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support] 

Sent from my iPhone

> On 16 Jul 2017, at 07:40, Alain Bastien alainbast...@gmail.com 
> [firebird-support]  wrote:
> 
> Create a new table with same structure from METADATA
> 
> Use "Batchmove" (In Delphi VCL) with Delete Source enabled
> 
> Connect the two tables, MOVE DATA
> 
> Drop the empty table.
> 
> COMMIT!!! 
> 
> This would take merely 15 minutes 
> 
Hi,

If table is a big one with millions of rows, 15min cannot be enough moreover 
down time might not be possible as you should block data entrance while doing 
that.

Regards,
Ertan
> 
> Alain Bastien 
> 34, Dr Ross Avenue
> ROSE HILL 72102
> MAURITIUS
> Skype: alainbastien
> Phone: 230 57 19 30 30
> Res :230 464 68 30
> 
> Sent from iPhone
> 
> 
>

[firebird-support] Finding a single row

2016-05-26 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support] 
Hi,

I have a table where I keep some part from the beginning of a discount card
number. Length of beginning is variable. User provides a complete card
number at runtime, I need to find a single row in my table which begins as
the complete card number, or I need to know no match exists in my table.

I might do it coding in my application, but that would be way too slow
compared to an SQL statement I assume.

Table is as follows.
CREATE TABLE FIYATKODLARI(
 Kodu   Varchar(30) NOT NULL PRIMARY KEY
);

It contains something like:
Kodu

SA
SB
SSC
SAA
SBDE
8694
125

Complete card number may be something like:
SB1
SA25
SA325
SB1054
SAA23
86941005
12504

Below didn't work for me because complete card number is longer, and I do
lack SQL knowledge:
select * from FIYATKODLARI where Kodu like 'SB1%'

Any help is appreciated.

Thanks.
Ertan Küçükoğlu









++

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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

RE: !!***SPAM***!! Re: [firebird-support] Firebird V3RC2 Primary Key question

2016-04-08 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support] 
Thanks for instructions. It does work.

 

Is there a way to use created case insensitive collation as database wide 
default? It would be easier for me not to specify collation for each and every 
index definition.

 

Thanks.

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Friday, April 8, 2016 10:36 AM
To: firebird-support@yahoogroups.com
Subject: !!***SPAM***!! Re: [firebird-support] Firebird V3RC2 Primary Key 
question

 

  

On 7-4-2016 23:01, Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr 
[firebird-support] wrote:
> Would you be so kind to provide an example SQL which shows how to do
> primary key with a case insensitive collation?

1. Register the WIN1254_UNICODE collation (it is defined in fbintl.conf, 
but not included by default in Firebird):

create collation WIN1254_UNICODE for win1254

2. Derive a case insensitive collation from WIN1254_UNICODE:

create collation win1254_ci for win1254 from win1254_unicode case 
insensitive

3. Create table

create table win1254test (
stringkey varchar(30) character set win1254 primary key collate 
win1254_ci
)

4. Test it:

INSERT INTO win1254test(stringkey) VALUES('EK'); -- succeeds
INSERT INTO win1254test(stringkey) VALUES('ek'); -- fails
INSERT INTO win1254test(stringkey) VALUES('Ek'); -- fails
INSERT INTO win1254test(stringkey) VALUES('eK'); -- fails

I hope this helps,
Mark
-- 
Mark Rotteveel





[Non-text portions of this message have been removed]

RE: Re: [firebird-support] Firebird V3RC2 Primary Key question

2016-04-07 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support] 
Would you be so kind to provide an example SQL which shows how to do primary 
key with a case insensitive collation?

 

Thanks.

 

Ertan

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Thursday, April 7, 2016 8:54 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Firebird V3RC2 Primary Key question

 

  

This is the default and expected behavior. If you want case insensitive, then 
you need to create the primary key column with a case insensitive collation.

 

Mark

 

 

- Bericht beantwoorden -
Van: "Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr 
  [firebird-support]" 
 >
Aan:  >
Onderwerp: [firebird-support] Firebird V3RC2 Primary Key question
Datum: do, apr. 7, 2016 19:46

 

Hi,

 

It’s been a while since I last used Firebird. I am testing new release 
candidate at the moment. Just found something I am not so sure.

 

I have a database created using “CharSet WIN1254” parameter.

 

Below is the SQL I used to create a table in that database:

CREATE TABLE KULLANICI

(

  KODU VARCHAR(30)  NOT NULL PRIMARY KEY,

  ADI VARCHAR(50),

  SIFRE VARCHAR(64),

  EMAIL VARCHAR(60),

);

 

And, all below executes without error:

INSERT INTO KULLANICI(KODU) VALUES(‘EK’);

INSERT INTO KULLANICI(KODU) VALUES(‘ek’);

INSERT INTO KULLANICI(KODU) VALUES(‘Ek’);

INSERT INTO KULLANICI(KODU) VALUES(‘eK’);

 

I can insert “EK”, “ek”, “Ek”, and “eK” as new records in the KODU column of 
that table. Somehow primary key of that table is case-sensitive.

 

I couldn’t find anything about primary key defaults to case-sensitive in the 
“Firebird-3.0.0_RC2-ReleaseNotes.pdf”. I don’t remember if that was the case in 
earlier versions, too. Just want to be sure about it. Can anybody confirm if 
that is a feature?

 

On my part, I would like to avoid it if possible.

Thanks.

 

Regards,

Ertan Küçükoğlu

[firebird-support] Firebird V3RC2 Primary Key question

2016-04-07 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support] 
Hi,

 

It's been a while since I last used Firebird. I am testing new release
candidate at the moment. Just found something I am not so sure.

 

I have a database created using "CharSet WIN1254" parameter.

 

Below is the SQL I used to create a table in that database:

CREATE TABLE KULLANICI

(

  KODU VARCHAR(30)  NOT NULL PRIMARY KEY,

  ADI VARCHAR(50),

  SIFRE VARCHAR(64),

  EMAIL VARCHAR(60),

);

 

And, all below executes without error:

INSERT INTO KULLANICI(KODU) VALUES('EK');

INSERT INTO KULLANICI(KODU) VALUES('ek');

INSERT INTO KULLANICI(KODU) VALUES('Ek');

INSERT INTO KULLANICI(KODU) VALUES('eK');

 

I can insert "EK", "ek", "Ek", and "eK" as new records in the KODU column of
that table. Somehow primary key of that table is case-sensitive.

 

I couldn't find anything about primary key defaults to case-sensitive in the
"Firebird-3.0.0_RC2-ReleaseNotes.pdf". I don't remember if that was the case
in earlier versions, too. Just want to be sure about it. Can anybody confirm
if that is a feature?

 

On my part, I would like to avoid it if possible.

Thanks.

 

Regards,

Ertan Küçükoğlu

[firebird-support] Firebird 3.0 RC1 (fbembed.dll)

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

 

I have subject version installed. However, I could not find and fbembed.dll
file to test and play with. Is it intentionally missing? Or, there is a way
to get it?

 

Thanks.

 

Ertan Küçükoğlu

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

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

I needed some time to understand (at least try to understand) your post. 
Please, note that my SQL knowledge is very limited. I really don’t know 
internals of database any system. Though, I need to develop an application 
which can use Firebird, Interbase, MSSQL, MySQL, Oracle. Some of users 
databases may be Firebird, others MSSQL, and an Oracle maybe. User decides what 
to use.

Saying that;
- I wonder if it would be possible to make a view and decrease one SELECT depth 
in the SQL? This will make statement a little easier to read for sure. Or, that 
will be a problem in the long run? Some say views are not good. They are 
generated completely for all the data in TABLE not considering WHERE clauses. 
Not saved as TABLE data and generated at each run in a temp disk space/memory.
- Having some indexes added query now performs very nicely. Runs & fetches all 
records below one second. Cold run around 0.8 second, 2nd run right after cold 
is around 0.4 seconds.
- I read and re-read your comments on having separate indexes is better for 
SQL. However, I do need that “combined” primary index for my application, for 
sure. Though, I wonder if it will be of any help to define separate indexes for 
each field as you suggested? I don't know if that will be lots of indexes 
defined in the end. Especially considering other tables I need to define as 
application is still being developed and new TABLES are added. I could not 
understand what maybe the problem if a/some field(s) length in "combined" index 
is increased.
- Finally, I needed to test SQL statement that you re-write in other SQL 
databases running in Windows 10 x64bit In order to see if it is executing OK. 
All databases I use are Free to use versions. I don't do any test on Interbase 
since I don't have it installed on my development computer and assuming it will 
be very much same with Firebird. My observations:
Firebird: No problem.
MSSQL: No problem.
MySQL: ERROR near UBAZ (at very beginning)
Oracle: Error at Line: 7 Column: 17, ORA-00907: missing right parenthesis (line 
7: SUM(IIF(TekParca='H', Miktar, 0)) AS Miktar)
- One last thing to tell as an information. Oracle was able to run my first 
posted statement with only Primary Keys and no additional indexes defined less 
than one second. This is nothing to be said for comparing Firebird and Oracle. 
It is just I cannot understand how Oracle handles things. Moreover, it is less 
than a month that I ever installed and used Oracle as a database. I always 
preferred Firebird as my first line of database.

Thanks & regards.
-Ertan

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Saturday, January 23, 2016 1:42 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Firebird 3.0 RC1 - SQL optimization help

  
Hi, sorry for a bit late reply. I had to rewrite your query to actually 
understand it, I'm not used to SELECT FROM (SELECT FROM (SELECT...

This is what I ended up with (though I am tired, so it wouldn't surprise 
me if I made some mistakes).

WITH UBAZ as
(SELECT AdresKodu, IslemTuru, BelgeNo, Barkod, Miktar, OlcuBirimi
FROM BAZLISTE
WHERE BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'),
UT as
(SELECT AdresKodu , IslemTuru, BelgeNo, Barkod, OlcuBirimi, 
SUM(IIF(TekParca='H', Miktar, 0)) AS Miktar
FROM TERMINAL_SAYIM
WHERE BelgeNo='REYSAS' AND AdresKodu='SAYIM' AND IslemTuru='SA'
GROUP BY BelgeNo, Barkod, OlcuBirimi, IslemTuru, AdresKodu),
UNB as
(SELECT AdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi
FROM UBAZ
UNION
SELECT AdresKodu, IslemTuru, BelgeNo, barkod, olcubirimi
FROM UT)

SELECT UNB.AdresKodu "Adres Kodu", UNB.IslemTuru "İşlem Türü", 
UNB.BelgeNo "Belge No", UNB.barkod, UNB.olcubirimi "Ölçü Birimi",
coalesce(UT.Miktar,0) "Okutulan Miktar", coalesce(UBAZ.Miktar, 0) "Baz 
Miktar", coalesce(UT.Miktar, 0) - coalesce(UBAZ.Miktar, 0) Fark,
URUN.UrunKodu "Ürün Kodu", URUN.UrunAciklamasi "Ürün Açıklaması", 
URUN.UrunGrubu "Ürün Grubu", URUN.Renk, URUN.Beden, URUN.Cup
FROM UNB
LEFT JOIN URUN ON UNB.olcubirimi = URUN.OlcuBirimi AND UNB.barkod = 
URUN.Barkod
LEFT JOIN UBAZ ON UNB.olcubirimi = UBAZ.olcubirimi AND UNB.barkod = 
UBAZ.Barkod
LEFT JOIN UT ON UNB.olcubirimi = UT.olcubirimi AND UNB.barkod = 
UT.Barkod

Then I looked at your PLAN:

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))

and compared the indexes to your table definitions. The first thing I 
notice, is that
RDB$PRIMARY3 = PRIMARY KEY (BELGENO,BARKOD,ADRESKODU,ISLEMTURU)

I dislike this key for two reasons:
(a) It contains fields which have some meaning attached to it. This may 
be OK for now, but it is more difficult to modify fields later on if 
field definitions change (e.g. if ADRESKODU is increased to

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

2016-01-21 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support] 
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 SayimMik

[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 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 ,