Cursor Performance / 100% CPU on empty cursor

tnx · Post by **tnx** » Mon 16 Mar 2009 17:58

Hello,

I want to do following operation:

select myfunction(:myparameter);
fetch all from cursor

But PgDac doesn't allow to do both in one query. I have to do roughly:

PgConnection1.StartTransaction;
(Execute first query with first line)
(Execute second query with second line - fetch statement)
PgConnection1.Commit;

Alternatively, I can use a second query and do something like
PgQuery2.Cursor := TPgCursorField(PgQuery1.Fields[0]).AsCursor;

But whatever I do, this operation requires 4 round-trips to the server. Through a slow connection this takes more than twice as long than what I had before with PostgresDAC which allowed multiple statements in a single query with parameters.

Taking twice as long is a serious issue for me. What's the solution to this? There must be a way of using stored procedures with cursors without loss of that much speed.

Thanks

Plash · Post by **Plash** » Tue 17 Mar 2009 13:31

There is the UnpreparedExecute option in TPgQuery. Set it to True, and you will be able to execute a query containing several SQL statements.

tnx · Post by **tnx** » Tue 17 Mar 2009 18:46

Thanks. This functionality is undocumented. What's the disadvantage of using this? Does it support parameters without limitations?

Plash · Post by **Plash** » Thu 19 Mar 2009 08:45

We'll add this property to the help.

Parameters are supported without limitations. But internal implementation is the following:
PgDAC replaces parameter names with their values when sending SQL to the server. So that SQL is changed each time when you change parameters values.

tnx · Post by **tnx** » Sun 22 Mar 2009 09:21

I just tried it out and your library ends up in an infinite loop. This very basic function returning an empty cursor

Code: Select all

CREATE OR REPLACE FUNCTION test(cur refcursor)
  RETURNS refcursor AS
$BODY$
BEGIN
  OPEN cur FOR SELECT 1 where 1 < 0;
  RETURN cur;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

when activating the described property and quering:

Code: Select all

select test('test');
fetch all from test;

Excuse me but this has got to be one of the most basic thing that needs to be supported. I basically have to kill my program taking 100% CPU in task manager after calling this command.

Plash · Post by **Plash** » Mon 23 Mar 2009 08:35

We have fixed this problem. The fix will be included in the next build of PgDAC.

tnx · Post by **tnx** » Tue 24 Mar 2009 13:53

Thank you. Any idea when that will be?

Plash · Post by **Plash** » Wed 25 Mar 2009 07:59

We are planning to release new PgDAC build on the next week.