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

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.KolAS 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.KolAS 
 
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,0AS 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,FalseAS 
 
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,0AS 
 
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,FalseAS 
 
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,0AS 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
ASFPrenos,      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
GROUPBY 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.