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