load heavily hit tables into temp-tables

[Grant Holman]

A WebSpeed system I'm working on at the moment has agents that load certain tables that are hit heavily and constantly into temp-tables when they start up.

I can see certain benefits in this to an extent, but is there a point where the benefits become neglegable? After all a large temp-table will still need to reside on disk and therefore requires physical reads the same as the original source DB table.

N.B. These tables are, more or less, static in nature so the issue of them being updated in the DB but this not reflecting in the temp-tables is not an issue.

Any views would be most appreciated.

 

[ChrisPaulson]

I wouldn't bother doing it.

Only time I would bother is if you was in a C/S setup to cut down network traffic ie. like our app.

 

[Grant Holman]

thanks for the reply.

Its worth noting that this temp-table code was actually put in place by Progress Professional Services so I guess there must be some validity in it.

Does anyone have benchmarks stats etc to back up a yes/no opinion?

 

[ChrisPaulson]

The only time I could think of a benifit was if the data was manipulated before put in the temp tables.

Data is better accessed through a central cache ie the DB server. Otherwise each webspeed server will have a copy of the data which is wasteful of memory and disk access.


Progress is not a seal of quality - just look at smart (dumb) objects.