pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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
     
             
     

view thread (16+ messages)  latest in thread

Message-ID: <BAY106-DAV73D3202A09EC68044469AFA040@phx.gbl>
Permalink:  ../BAY106-DAV73D3202A09EC68044469AFA040@phx.gbl/
Also on:    postgresql.org/message-id/BAY106-DAV73D3202A09EC68044469AFA040@phx.gbl

 · 

reply

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