I need to process a
large table a few "chunks" at a time, commiting in between chunks so that
another process can pick up and start processing the data.
I am using a
pl/pgsql procedure with a "FOR rec in Select * from tab order by...."
statement. The chunksize is passed in to the procedure, and in the FOR loop I
iterate until I reach chunksize. The procedure then returns and the calling code
issues the commit, etc.
I know from the
documentation that the FOR implicitly opens a cursor, but I'm wondering if there
would be any performance advantages to explicitly declaring a cursor and moving
through it with FETCH commands?
I have to use the
ORDER BY, so I imagine I'm taking the hit of processing all the records in the
table anyway, regardless of how many I ultimately fetch. The nature of the data
is that chunksize doesn't necessarily match up one-for-one with rows, so I can't
use it as a LIMIT value.
The table in
question gets inserted pretty heavily, and my procedure processes rows then
deletes those it has processed. My main concern is to keep the processing fairly
smooth, i.e., not have it choke on a select when the table gets
huge.
Any suggestions
appreciated!
-
DAP
----------------------------------------------------------------------------------
David
Parker Tazz Networks (401)
709-5130
