>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