X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 018019DC81D for ; Sun, 12 Feb 2006 16:25:57 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 06748-07 for ; Sun, 12 Feb 2006 16:25:58 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 322819DC814 for ; Sun, 12 Feb 2006 16:25:55 -0400 (AST) Received: from hotmail.com (bay106-dav7.bay106.hotmail.com [65.54.161.79]) by svr4.postgresql.org (Postfix) with ESMTP id 3586E5AF854 for ; Sun, 12 Feb 2006 20:25:56 +0000 (GMT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Sun, 12 Feb 2006 12:25:54 -0800 Message-ID: Received: from 85.105.24.123 by BAY106-DAV7.phx.gbl with DAV; Sun, 12 Feb 2006 20:25:54 +0000 X-Originating-IP: [85.105.24.123] X-Originating-Email: [a_dursun@hotmail.com] X-Sender: a_dursun@hotmail.com From: "Adnan DURSUN" To: Subject: SQL Function Performance Date: Sun, 12 Feb 2006 22:25:28 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0006_01C63023.3A17E170" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-OriginalArrivalTime: 12 Feb 2006 20:25:54.0368 (UTC) FILETIME=[86149C00:01C63012] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=3.23 required=5 tests=[AWL=0.593, DNS_FROM_RFC_ABUSE=0.479, DNS_FROM_RFC_POST=1.44, HTML_MESSAGE=0.001, HTML_TAG_EXIST_TBODY=0.126, MSGID_FROM_MTA_HEADER=0, UPPERCASE_50_75=0.591] X-Spam-Score: 3.23 X-Spam-Level: *** X-Archive-Number: 200602/137 X-Sequence-Number: 17123 This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C63023.3A17E170 Content-Type: text/plain; charset="iso-8859-9" Content-Transfer-Encoding: quoted-printable =20 =20 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.. =20 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 =3D SD.FIRMA_NO AND I.HAT_NO =3D SD.HAT_NO AND I.SEFER_KOD =3D SD.SEFER_KOD AND I.PLAN_TARIHI =3D SD.PLAN_TARIHI AND I.BIN_YER_KOD =3D SD.BIN_YER_KOD AND I.KOLTUK_NO =3D SD.KOLTUK_NO AND I.KOD =3D SD.ISLEM_KOD AND SD.ISLEM =3D 'S' AND LP.FIRMA_NO =3D I.FIRMA_NO AND LP.HAT_NO =3D I.HAT_NO AND LP.SEFER_KOD =3D I.SEFER_KOD AND LP.PLAN_TARIHI =3D I.PLAN_TARIHI AND LP.YER_KOD =3D I.BIN_YER_KOD AND I.FIRMA_NO =3D $1 AND S.FIRMA_NO =3D LP.FIRMA_NO=20 AND S.HAT_NO =3D LP.HAT_NO AND S.KOD =3D LP.SEFER_KOD AND S.IPTAL =3D 'H' AND ((I.ISLEM_TARIHI =3D $2 AND $5 =3D 'I') OR = (LP.KALKIS_TARIHI =3D $2 AND $5 =3D 'K')) AND (((LP.LOKAL_KOD =3D $3 AND $4 =3D 'K')) OR = ((I.ypt_lcl_kod =3D $3 AND $4 =3D 'I')))=20 GROUP BY S.KALKIS_YERI,S.VARIS_YERI,S.SAAT; $BODY$ LANGUAGE 'sql' VOLATILE; Adnan DURSUN ASRIN Bili=FEim Ltd.=DEti Turkey =20 =20 =20 ------=_NextPart_000_0006_01C63023.3A17E170 Content-Type: text/html; charset="iso-8859-9" Content-Transfer-Encoding: quoted-printable
 
            Hi=20 all,
 
            My=20 database has an SQL function. The result comes in 30-40 = seconds=20 when i use the SQL function. On the other hand; The result = comes
        300-400 milliseconds = when=20 i run the SQL statement. Any idea ?? My database is = Postgresql=20 8.1.2..
       
            = Function is=20 below :
 
CREATE OR REPLACE FUNCTION = fn_online_seferler_satis("varchar", date,=20 int4, "varchar", "varchar")
  RETURNS SETOF record=20 AS
$BODY$
SELECT  (S.KALKIS_YERI||' '||S.VARIS_YERI||'=20 '||S.SAAT)::varchar AS SEFER_BILGI,
 sum((i.bilet_ucreti + = coalesce(i.police_ucreti,0)) - coalesce(i.int_artik_ucret,0)) as=20 top_satis,
 count(1)::int4 as top_koltuk
   = FROM=20 T_KOLTUK_ISLEM I,
 T_KOLTUK_SON_DURUM = SD,
 T_LOKAL_PLAN=20 LP,
 W_SEFERLER S
  WHERE I.FIRMA_NO =3D=20 SD.FIRMA_NO
    AND I.HAT_NO =3D=20 SD.HAT_NO
    AND I.SEFER_KOD =3D=20 SD.SEFER_KOD
    AND I.PLAN_TARIHI =3D=20 SD.PLAN_TARIHI
    AND I.BIN_YER_KOD =3D=20 SD.BIN_YER_KOD
    AND I.KOLTUK_NO =3D=20 SD.KOLTUK_NO
    AND I.KOD =3D=20 SD.ISLEM_KOD
    AND SD.ISLEM =3D=20 'S'
    AND LP.FIRMA_NO =3D=20 I.FIRMA_NO
    AND LP.HAT_NO =3D=20 I.HAT_NO
    AND LP.SEFER_KOD =3D=20 I.SEFER_KOD
    AND LP.PLAN_TARIHI =3D=20 I.PLAN_TARIHI
    AND LP.YER_KOD =3D=20 I.BIN_YER_KOD
    AND I.FIRMA_NO =3D=20 $1
    AND S.FIRMA_NO =3D LP.FIRMA_NO=20
    AND S.HAT_NO =3D = LP.HAT_NO
    AND=20 S.KOD =3D LP.SEFER_KOD
    AND S.IPTAL =3D=20 'H'
    AND ((I.ISLEM_TARIHI =3D  $2 AND $5 = =3D 'I') OR=20 (LP.KALKIS_TARIHI =3D $2 AND $5 =3D 'K'))
    = AND=20 (((LP.LOKAL_KOD =3D $3 AND $4 =3D 'K')) OR  ((I.ypt_lcl_kod = =3D $3 AND $4 =3D=20 'I'))) 
GROUP BY S.KALKIS_YERI,S.VARIS_YERI,S.SAAT;
$BODY$
  LANGUAGE 'sql' VOLATILE;
 
Adnan DURSUN
ASRIN Bili=FEim Ltd.=DEti
Turkey
 
= ------=_NextPart_000_0006_01C63023.3A17E170--