Re: slow performance with cursor

[Ayub Khan <ayub.hp@xxxxxxxxx>]

slowness is on the database side as I see the CPU goes high for procedures returning the result using cursors. If the same query is executed as a prepared statement from Java client there is no slowness.

for example there are 84 rows returning all are text data from a query. If  the result is returned by cursor from the database, the cpu is high on the db. 

stored procedure A executes query Q and returns cursor1, this process has high cpu on the database.

code changed in Java client to execute the same query as the prepared statement and get back the resultset from the database, this does not create a high cpu on the database.

--Ayub

On Fri, Jun 25, 2021 at 7:09 PM Ayub Khan <ayub.hp@xxxxxxxxx> wrote:

I am using postgresql 12 and using cursors in a stored procedure, executing procedure which has cursor is slowing down the call. However if I do not use the cursor and just execute the queries using JDBC (Java client) it's fast.

Is there any setting which needs to be modified to improve the performance of cursors. Also facing slow response with reading blobs (images) from db. Not an ideal way for storing images in db but this is a legacy application and wanted to check if there a quick tweak which can improve the performance while reading blob data from db.

--Ayub

--

--------------------------------------------------------------------

Sun Certified Enterprise Architect 1.5
Sun Certified Java Programmer 1.4
Microsoft Certified Systems Engineer 2000
http://in.linkedin.com/pub/ayub-khan/a/811/b81

mobile:+966-502674604

----------------------------------------------------------------------
It is proved that Hard Work and kowledge will get you close but attitude will get you there. However, it's the Love
of God that will put you over the top!!