[AccessD] Is a cursor the way to go

Besides the pertinent questions that Charlotte posed, I'd like to add this:

In over a decade's worth of SQL development, I have only once or twice
encountered an occasion when I couldn't think of a better approach than
using a cursor, and those were due to bad DB design rather than logical
necessity.

Most recently, I used a table variable to shrink the execution time of a
sequence of routines from 855 seconds to 95 seconds. This routine was
executed  12 times a day, so the difference matters significantly.

The single big difference between a table variable and a cursor is that the
latter is always written to tempdb, while the former lives in RAM, if there
is enough of said precious commodity. Of course that depends on the size of
the virtual table you need to create, but it's not your fault if the
hardware is insufficient (unless of course you were responsible for
specifying it). A few ground-rules here:

1. the DB is the life-blood of the enterprise.
2. After stability, performance is the important issue of the DB.
3. RAM is cheap.

A.

On Fri, May 25, 2012 at 10:44 AM, Charlotte Foust <charlotte.foust at gmail.com
> wrote:

> I see what you're trying to do, but why are you trying to do it?  Does
> "Allocated" mean paid or to be paid?  Or are you trying to figure out what
> should be paid and what left out?  What logic would be applied?  Would the
> largest  Amount receive it's allocation first?  Would they go in date
> order?  It feels like there is a piece missing in this logic.
>
> Charlotte Foust
>
> On Fri, May 25, 2012 at 5:35 AM, Paul Hartland <
> paul.hartland at googlemail.com
> > wrote:
>
> > To all,
> >
> > I will try and explain the problem as best as I possibly can, for this
> > purpose lets say I have two tables, PODetail which holds detail records
> for
> > a purchase order, and a table containing a value which we have currently
> > allocated to a single purchase order number POAllocation
> >
> > PODetail
> > PoNumber    Amount     Allocated    RowID
> > 123456          50.00             0              1
> > 123456          20.00             0              2
> > 123456          76.00             0              3
> >
> > POAllocation
> > PONumber    Allocated
> > 123456          141.00
> >
>