Re: PERSISTANT PREPARE (another point of view)

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(dot)opa(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>