Re: Replacing Cursors with Temporary Tables

I appreciate all the comments.

I will perform some benchmarking before doing the rewrite to be certain of
how it will impact performance. At the very least, I think can say for
near-certain now that the indexes are not going to help me given the
particular queries I am dealing with and limited number of records the temp
tables will have combined with the limited number of times I will re-use
them.

On Thu, Apr 22, 2010 at 10:42 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Thu, Apr 22, 2010 at 10:11 AM, Merlin Moncure <mmoncure(at)gmail(dot)com>
> wrote:
> > The timings are similar, but the array returning case:
> > *) runs in a single statement. If this is executed from the client
> > that means less round trips
> > *) can be passed around as a variable between functions. temp table
> > requires re-query
> > *) make some things easier/cheap such as counting the array -- you get
> > to call the basically free array_upper()
> > *) makes some things harder. specifically dealing with arrays on the
> > client is a pain UNLESS you expand the array w/unnest() or use
> > libpqtypes
> > *) can nest. you can trivially nest complicated sets w/arrays
> > *) does not require explicit transaction mgmt
>
>
> I neglected to mention perhaps the most important point about the array
> method:
> *) does not rely on any temporary resources.
>
> If you write a lot of plpsql, you will start to appreciate the
> difference in execution time between planned and unplanned functions.
> The first time you run a function in a database session, it has to be
> parsed and planned. The planning time in particular for large-ish
> functions that touch a lot of objects can exceed the execution time of
> the function. Depending on _any_ temporary resources causes plan mgmt
> issues because the database detects that a table in the old plan is
> gone ('on commit drop') and has to re-plan. If your functions are
> complex/long and you are counting milliseconds, then that alone should
> be enough to dump any approach that depends on temp tables.
>
> merlin
>

--
Eliot Gable

"We do not inherit the Earth from our ancestors: we borrow it from our
children." ~David Brower

"I decided the words were too conservative for me. We're not borrowing from
our children, we're stealing from them--and it's not even considered to be a
crime." ~David Brower

"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not
live to eat.) ~Marcus Tullius Cicero