slow performance with cursor
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
Re: slow performance with cursor
On Fri, Jun 25, 2021 at 07:09:31PM +0300, Ayub Khan 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 the query slower, or is it slow to transfer tuples ? I expect there would be a very high overhead if you read a large number of tuples one at a time. > 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. Is the slowness between the client-server or on the server side ? Provide some details ? -- Justin
Re: slow performance with cursor
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 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!!
Re: slow performance with cursor
Ayub Khan writes: > 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. Possibly twiddling cursor_tuple_fraction would help. The default setting tends to encourage fast-start plans, which might be counterproductive if you're always fetching the entire result in one go. regards, tom lane
