From: Adnan DURSUN <a_dursun@hotmail.com>
To: pgsql-performance@postgresql.org
Subject: SQL Function Performance
Date: Sun, 12 Feb 2006 22:25:28 +0200
Message-ID: <BAY106-DAV73D3202A09EC68044469AFA040@phx.gbl> (raw)
Hi all,
My database has an SQL function. The result comes in 30-40 seconds when i use the SQL function. On the other hand; The result comes
300-400 milliseconds when i run the SQL statement. Any idea ?? My database is Postgresql 8.1.2..
Function is below :
CREATE OR REPLACE FUNCTION fn_online_seferler_satis("varchar", date, int4, "varchar", "varchar")
RETURNS SETOF record AS
$BODY$
SELECT (S.KALKIS_YERI||' '||S.VARIS_YERI||' '||S.SAAT)::varchar AS SEFER_BILGI,
sum((i.bilet_ucreti + coalesce(i.police_ucreti,0)) - coalesce(i.int_artik_ucret,0)) as top_satis,
count(1)::int4 as top_koltuk
FROM T_KOLTUK_ISLEM I,
T_KOLTUK_SON_DURUM SD,
T_LOKAL_PLAN LP,
W_SEFERLER S
WHERE I.FIRMA_NO = SD.FIRMA_NO
AND I.HAT_NO = SD.HAT_NO
AND I.SEFER_KOD = SD.SEFER_KOD
AND I.PLAN_TARIHI = SD.PLAN_TARIHI
AND I.BIN_YER_KOD = SD.BIN_YER_KOD
AND I.KOLTUK_NO = SD.KOLTUK_NO
AND I.KOD = SD.ISLEM_KOD
AND SD.ISLEM = 'S'
AND LP.FIRMA_NO = I.FIRMA_NO
AND LP.HAT_NO = I.HAT_NO
AND LP.SEFER_KOD = I.SEFER_KOD
AND LP.PLAN_TARIHI = I.PLAN_TARIHI
AND LP.YER_KOD = I.BIN_YER_KOD
AND I.FIRMA_NO = $1
AND S.FIRMA_NO = LP.FIRMA_NO
AND S.HAT_NO = LP.HAT_NO
AND S.KOD = LP.SEFER_KOD
AND S.IPTAL = 'H'
AND ((I.ISLEM_TARIHI = $2 AND $5 = 'I') OR (LP.KALKIS_TARIHI = $2 AND $5 = 'K'))
AND (((LP.LOKAL_KOD = $3 AND $4 = 'K')) OR ((I.ypt_lcl_kod = $3 AND $4 = 'I')))
GROUP BY S.KALKIS_YERI,S.VARIS_YERI,S.SAAT;
$BODY$
LANGUAGE 'sql' VOLATILE;
Adnan DURSUN
ASRIN Biliþim Ltd.Þti
Turkey
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: a_dursun@hotmail.com
Subject: Re: SQL Function Performance
In-Reply-To: <BAY106-DAV73D3202A09EC68044469AFA040@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox