Alternate or Optimization for with hold cursor

On May 12, 2016, at 4:57 AM, sangeetha <tune2sangee@gmail.com> wrote:

Currently , I am using "With hold" cursor. In our case , the With hold cursor
is used to fetch the next record of the given primary key .

Can you explain your use case. If i understand with hold correctly, it is typically used to preserve locks even after commit , so as to get a consistent view of data.

The performance
is very slow for large data set. Can you provide me some alternative ways
like having own copy of table , or optimization for With hold cursor?

Thanks and Regards,
S.Sangeetha

--
View this message in context: http://postgresql.nabble.com/Alternate-or-Optimization-for-with-hold-cursor-tp5903211.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Not sure what you trying to achieve, you could give a try for Materialized
Views[1], see would this help you or not.

1. http://www.postgresql.org/docs/9.3/static/rules-materializedviews.html
2. http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html

Regards,
Amul Sul

--
View this message in context: http://postgresql.nabble.com/Alternate-or-Optimization-for-with-hold-cursor-tp5903211p5903233.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

From: Sangeetha Sent: Thursday, May 12, 2016 1:58 AM

Currently , I am using "With hold" cursor. In our case , the With hold
cursor is used to fetch the next record of the given primary key . The
performance is very slow for large data set. Can you provide me some
alternative ways like having own copy of table , or optimization for With
hold cursor?

Thanks and Regards,
S.Sangeetha<
==================

Cursors are the last tool I would ever grab out of my sql toolbox (aka, I
never use one) - it converts the enormous power of a relational database
engine into "RBAR" (row by agonizing row). For a large dataset in
particular, you are killing the server since the entire resultset must be
retained in working memory for the duration of the query as it peels off one
row at a time from that resultset OR if it's larger than your ram, you'll be
paging to disk constantly. And since you're working on a single row at
time, it will take forever.

Convert the cursor into a normal query and you should see BIG (10-1000x)
gains in speed. A cursor can always be converted to normal
sql...always...it's not always easy but it's always worth the effort.

Mike Sofen

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general