Re: PERSISTANT PREPARE (another point of view)

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.