Re: PERSISTANT PREPARE (another point of view) - Mailing list pgsql-sql

Hello

this is near my implemenation of stored procedures - it's not far from
your view on prepared statements. There  result of any unbound select
is directly forwarded to client - there is little bit different
syntax, but idea is same


create procedure foo(params)
as $$
begin select a, b, c from tab1 -- unbound select select ....
end;

and you can call with CALL statement. Curent implementation of SRF
function in plpgsql sn't eficient for big results - it needs
materialised table in memory. Pipeline Oracle's functions are better,
but we are limited one thread architecture.

regards
Pavel Stehule

2008/7/25 Milan Oparnica <milan.opa@gmail.com>:
> Jeff Williams wrote:
>
>> You can do this with cursors, but I'm not sure if you still get the
>> query caching?
>>
>
> I can do it with cursors, I don't get query cache but that is for now not an
> issue.
>
> Issue is performance.
>
> Although using cursors show better performance over using SET OF user
> defined data types, it still swallows huge amount of memory.
>
> Here are the results of a query (I post the query at the end just for sake
> of complexity example) running on database of 1.6 GB of data and around 5
> million of records:
>
>                        Time    Approx RAM usage        Swaping
> PREPARE Statement       5 min.  300 MB                  None
> SP with SET OF          16 min. 800 MB                  Heavy
> SP with cursor          9 min.  800 MB                  Medium
>
> Environment:
>
> Run on Query Tool of pgAdmin III. It does no data modification.
> Number of clients: 1
> PostgreSQL version 8.3
> Processor P4 2.66 GHZ
> RAM memory  1 GB.
>
> There where no other active programs or demanding services running in the
> background.
>
>
> I presume adding more RAM would increase performance but we usually have 10
> to 20 clients working at a time. I guess that would put some load.
>
> Besides, fetching records from cursor type SP requires transactional
> approach in calling convention (BEGIN....statment...END). At this moment I
> don't know if this can be done in our main report designer tool - Business
> Objects (former Crystal Reports).
>
> Best regards,
>
> Milan Oparnica
>
>
> Here is the text of SQL statement:
>
> Note: All fields in join are indexed and every where field is also indexed
> except boolean fields.
>
> CREATE FUNCTION aaTestCurs (refcursor, insklid integer, indatdo timestamp
> without time zone) RETURNS refcursor AS $$
> BEGIN
>    OPEN $1 FOR
>    SELECT 10 AS
> Poredak,U.UlID,U.DatDoc,DT.FKomis,DT.FSCObrac,DT.FNivelTot,DT.FNivelParc,
>      U.DocTip,SU.SasUlID,SU.ElID,ROUND(CAST(SU.Kol AS NUMERIC),8) AS
> Kol,SU.NCena,ROUND(CAST(SU.TKol AS NUMERIC),8) AS TKol,
>      SU.SCena,SU.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena,
>      U.Link,SU.FCena,SU.Popust,SU.Marza,SU.MCena,SU.MPorez,SU.KKol,
>      SU.PorezU,SUN.NNKol,SUN.NPCena,SUN.NTotal,SUN.NTotPP,SUN.NTotPU,0 AS
> NivelEdited,
>      SUN.NNKol AS NivelOldKol,0 AS LastTKol,0 AS LastSCena,SK.RacTipSKL,
>      U.DTStamp AS Tabela,U.DopSklID,DT.FPrenos,SK.FRemote
>      FROM(((defDocTip AS DT INNER JOIN Ulaz AS U ON DT.DTID = U.DocTip)
> INNER JOIN SasUlaz AS SU ON U.UlID = SU.UlID) INNER JOIN Skladista AS SK ON
> U.SklID = SK.SklID) LEFT JOIN SasUlazNivel AS SUN ON SU.SasUlID =
> SUN.SasUlID
>      WHERE DT.FSCObrac AND NOT(SK.FSkipSCObr <> false) AND NOT(SK.FSkipNivel
> <> false) AND U.DatDoc <= InDatDo
>      UNION ALL
>      SELECT 20 AS
> Poredak,U.UlID,U.DatDoc,DT.FKomis,DT.FSCObrac,DT.FNivelTot,DT.FNivelParc,
>      U.DocTip,SU.SasUlID,SU.ElID,ROUND(CAST(SU.Kol AS
> NUMERIC),8),SU.NCena,ROUND(CAST(SU.TKol AS NUMERIC),8),
>      SU.SCena,SU.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena,U.Link,
>
> SU.FCena,SU.Popust,SU.Marza,SU.MCena,SU.MPorez,SU.KKol,SU.PorezU,SUN.NNKol,
>      SUN.NPCena,SUN.NTotal,SUN.NTotPP,SUN.NTotPU,0 AS NivelEdited,
>      SUN.NNKol AS NivelOldKol,0 AS LastTKol,0 AS
> LastSCena,SK.RacTipSKL,(U.DTStamp -(200000*case when DT.Rank > 50000 then -1
> else 0
> end)) AS Tabela,
>      U.DopSklID,DT.FPrenos,SK.FRemote
>      FROM(((defDocTip AS DT INNER JOIN Ulaz AS U ON   DT.DTID = U.DocTip)
> INNER JOIN SasUlaz AS SU ON U.UlID = SU.UlID) INNER JOIN Skladista AS SK ON
> U.SklID = SK.SklID)  LEFT JOIN SasUlazNivel AS SUN ON SU.SasUlID =
> SUN.SasUlID
>      WHERE NOT DT.FSCObrac AND NOT(SK.FSkipSCObr <> false) AND
> NOT(SK.FSkipNivel <> false) AND U.DocTip <> 31 AND U.DatDoc <= InDatDo
>      UNION ALL
>      SELECT 28 AS Poredak,U.UlID,U.DatDoc,False AS
> FKomis,DT.FSCObrac,DT.FNivelTot,
>      DT.FNivelParc,U.DocTip,SU.SasUlID,SU.ElID,ROUND(CAST(SU.Kol AS
> NUMERIC),8),SU.NCena,ROUND(CAST(SU.TKol AS NUMERIC),8),
>      SU.SCena,SU.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena,
>
> U.Link,SU.FCena,SU.Popust,SU.Marza,SU.MCena,SU.MPorez,SU.KKol,SU.PorezU,
>      SUN.NNKol,SU.RCena,SUN.NTotal,SUN.NTotPP,SUN.NTotPU,0 AS NivelEdited,
>      SUN.NNKol AS NivelOldKol,0 AS LastTKol,0 AS LastSCena,SK.RacTipSKL,
>      U.DTStamp AS Tabela,U.DopSklID,DT.FPrenos,SK.FRemote
>      FROM(((defDocTip AS DT INNER JOIN Ulaz AS U ON DT.DTID = U.DocTip)
> INNER JOIN SasUlaz AS SU ON U.UlID = SU.UlID) INNER JOIN Skladista AS SK ON
> U.SklID = SK.SklID)  LEFT JOIN SasUlazNivel AS SUN ON SU.SasUlID =
> SUN.SasUlID
>      WHERE U.DatDoc <= InDatDo AND NOT(SK.FSkipSCObr <> false) AND
> NOT(SK.FSkipNivel <> false) AND U.DocTip = 31 AND((SK.RacTipSKL = 3
> AND(DT.FMP <> false)) OR(SK.RacTipSKL <> 3 AND(DT.FVP <> false)))
>      UNION ALL SELECT 30 AS Poredak,0 AS UlID,O.DatDoc,False AS
> FKomis,False,False,False,2 AS DocTip,
>      0 AS SasUlID,SO.ElID,-SUM(SO.Kol) AS Kol,0 AS NCena,0 AS TKol,
>      0 AS SCena,SK.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena,0 AS Link,
>      0 AS FCena,0 AS Popust,0 AS Marza,0 AS MCena,0 AS MPorez,0 AS KKol,0 AS
> PorezU,
>      0 AS NNKol,0 AS NPCena,0 AS NTotal,0 AS NTotPP,0 AS NTotPU,0 AS
> NivelEdited,
>      0 AS NivelOldKol,0 AS LastTKol,0 AS LastSCena,SK.RacTipSKL,
>      200000 AS Tabela,0 AS DopSklID,FALSE AS FPrenos,
>      FALSE AS FRemote
>      FROM(Otpremnice AS O INNER JOIN SasOtp AS SO ON O.OtpID = SO.OtpID)
>  INNER JOIN Skladista AS SK ON SO.SklID = SK.SklID
>      WHERE O.DatDoc <= InDatDo AND SO.ElID < 1000000
>      GROUP BY O.DatDoc,SO.ElID,SK.SklID,SK.RacTipSKL
>      ORDER BY 3,Tabela,2;
>    RETURN $1;
> END
> $$ LANGUAGE plpgsql;
>
>
> -- Executing query:
> BEGIN;
> SELECT aaTestCurs('curs', 1,'31.12.2008');
> FETCH ALL FROM curs;
> END;
> Query result with 1 rows discarded.
> Query result with 1564318 rows discarded.
>
> Query returned successfully with no result in 531563 ms.
>
> I guess I didn't populate the cursor.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>