Done.
Although the merge with views executed on less time, performance is very identical...
Merge with views:
NOTE: DATA statement used (Total process time):
real time 16:24.78
user cpu time 41.00 seconds
system cpu time 32.28 seconds
Memory 22663k
Page Faults 185862
Page Reclaims 15291
Page Swaps 0
Voluntary Context Switches 9793
Involuntary Context Switches 6277
Block Input Operations 0
Block Output Operations 0
Merge with tables:
NOTE: DATA statement used (Total process time):
real time 16:29.87
user cpu time 37.82 seconds
system cpu time 29.61 seconds
Memory 21024k
Page Faults 185651
Page Reclaims 8430
Page Swaps 0
Voluntary Context Switches 9780
Involuntary Context Switches 8154
Block Input Operations 0
Block Output Operations 0
- Page faults are about the same (I/O) but page reclaims (done in memory) are much higher (almost double) for the merge/views, which may explain the 5 seconds difference.
- Memory was about the same in both techniques.
- cpu usage was a little less with merge/tables, which is reasonable, since views should have some processing overhead to map/resolve the logical definition to the physical data.
But your theory may be actually right (views by blocks vs tables by rows), the page reclaims difference may indicate that. Data read into memory with a larger block size (and retrieved from there) vs. data read into memory with a smaller block size, and much more I/O operations.
Anyway, as always for performance, it will be everytime a system dependent matter. Base on the above theory and for our system, although merge/views performed less I/O operations the total processing time remained pretty much the same.
Cheers from Portugal.
Daniel Santos @
www.cgd.pt.