ADP: How to handle work tables?

[(Pete Cresswell)]

Seems like creating a #temp table is kind of useless because it cannot be bound
to a subform.

OTOH, seems like a waste of resources to populate work tables in the "real"
database - both because of server mips and the round-trip-for-nothing.

I'm thinking about a timesheet where data is stored in a three-table heirarchy:
TimeSheet==>TimeSheetLine==>HoursWorked. To present that to the user, the data
has to be pivoted into a temp or work table that shows a line for each project
and a column for each weekday. The user works on the pivoted presentation and
then, when it's time to save, the data is un-pivoted back into
Timesheet/TimeSheetLine/HoursWorked.

How about setting up a "tblTimeSheetPresentation" on the server but never
actually writing to it? Instead, the server table would just serve as a
marker/hook/tabledef and when the app "used" it, it would open up the table, cut
the connection, and then work with it locally - where I *guess* ADO
transparently maintains an XML dataset or arrays in memory or something... And
once the local data has been un-pivoted and saved, the disconnected recordset
would have all it's rows deleted..

Is this feasible? Seems like it would satisfy the ADP table-wise, but prevent
the wasted round-trips to the server...
--
PeteCresswell

[Steve Jorgensen]

On Mon, 05 Apr 2004 09:42:04 GMT, Chuck Grimsby
<c.*******@worldnet.att.net.invalid> wrote:

#temp tables are always kind of a hack in SQL, Pete. They are
"expensive" in terms of resources and somewhat slow. There are also
times when there's no other choice but to use them, however #temp
tables really should be considered a last resort. Rather like using
Make-Table queries in Access and then deleting the table when it's use
is over or it's time to create a new table.

It's far more efficient in both Access and SQL to use a permanent
"temp" table and limit the records the user deals with to just those
that the user "owns" via the WHERE clause, or just use queries (views
and Stored Procedures in SQL) to get to the data.
Well, that's not the whole story with SQL Server. There's another expens you
may want to avoid, and that's space in the transaction log that must be backed
up if you are doing incremental backups. Levels of temporary data activity
can swamp permanent activity in many applications, meaning that the space it
ends up taking in the backup dwarfs the space required for just backing up
changes to permanent data.

One alternative I've used with MDB front-ends is to actually use batches of
temporary data in permanent tables with batch IDs, but put those tables in a
separate database using the simple recovery model, so that the transaction log
will not grow. A view in the primary database references the table in the
other database, so the application doesn't have to keep track of the
implementation. Unfortunately, due to the way ADPs break encapsulation, I've
never been able to make this trick work in an ADP.
And, of course, it's never a good idea to access a table directly in
any database. Always go through a query, view or stored procedure.

Again, with ADPs, you can indicate that you are using the view or stored
procedure, but ADO and Access will try to go around your back. You still get
the conceptual abstraction, but not true encapsulation. Using a view that has
the WITH METADATA option, you can actually get a view to work as
encapsulation, but with some significant limitations. I was not able to use
that to solve the problem above, for instance. Also, a WITH METADAT view will
report no or incorrect key information if multiple tables are involved.
That's OK with an MDB where you can assign a virtual primary key to a link,
but you can't do that in an ADP.

[John Winterbottom]

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:p3********************************@4ax.com...

#temp tables are always kind of a hack in SQL, Pete. They are
"expensive" in terms of resources and somewhat slow. There are also
times when there's no other choice but to use them, however #temp
tables really should be considered a last resort. Rather like using
Make-Table queries in Access and then deleting the table when it's use
is over or it's time to create a new table.
Temporary tables have two advantages though;

a) since tempdb is minimally logged less information is written to disk so
writes are somewhat faster,
b) you can locate tempdb on a separate physical drive, which can have a
marked improvement on performance
It's far more efficient in both Access and SQL to use a permanent
"temp" table and limit the records the user deals with to just those
that the user "owns" via the WHERE clause, or just use queries (views
and Stored Procedures in SQL) to get to the data.
And, of course, it's never a good idea to access a table directly in
any database. Always go through a query, view or stored procedure.
I don't agree with this Chuck - I think global work tables are going to
incur more overhead. If you consider that they are fully logged, plus the
cost of having to constantly query for each users records, no, I think a
true tamp table will win out, both in disk I/O and processor cycles. To make
the temp table updateable though you have to roll your own as it were. In
this case I would prefer to just fetch the records once and process them on
the client.
For "pivoted" information, disconnected recordsets are indeed best,
with behind the form updates to whatever table(s) are worked with.

I've started using local jet databases with adp's for these situations and
I've had great results. It's a lot more work, granted, especially managing
batch updates and handling multi-user conflicts, but the benefit of having
true relational capability client-side more than makes up for it. With
Access XP, (and I think 2000 as well), you can bind an updateable form to a
dao recordset created from a local jet database, so you really have the best
of both worlds.

We have an application where users can view a couple of hundred columns of
results, in pages of 10 columns at a time. The old system using server temp
tables was horrible; changing pages took several seconds - once we changed
to a local jet database it ran like lightning..

[David W. Fenton]

"John Winterbottom" <as******@hotmail.com> wrote in
news:c4*************@ID-185006.news.uni-berlin.de:

I've started using local jet databases with adp's for these
situations and I've had great results. It's a lot more work,
granted, especially managing batch updates and handling multi-user
conflicts, but the benefit of having true relational capability
client-side more than makes up for it. With Access XP, (and I
think 2000 as well), you can bind an updateable form to a dao
recordset created from a local jet database, so you really have
the best of both worlds.

But, heavens!!!! That means your ADP is not longer JETLESS!!!!!

HORROR OF HORRORS!!!!!!!!!!!!!

THE WORLD WILL COME TO AN END!!!!!!!

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc