Page History
...
One of the points identified in the previous section is that snapshot queries and database views are less likely to perform less as well than as direct access to database tables. As show shown in on the SNOMED CT example database confirm that there is a substantial performance difference between these two approaches.
Snapshot Table | Snapshot Views
| Performance Ratio | |||||||||||
Read 1 million rows from relationship snapshot | 1.52 | 11.06 | 15% | ||||||||||
Read 1 million rows from description snapshot | 3.57 | 12.73 | 28% | ||||||||||
Read all rows from concept snapshot | 0.66 | 2.45 | 26% | ||||||||||
Total time for all operations above | 5.75 | 25.74 | 22% | ||||||||||
Advanced test reading 10,000 relationships and with joins to descriptions and language reference set for the fully specified names of source, type and target concept | 2.34 | 4.70 | 50% |
Based on these finding findings the most effective way to optimize access to a snapshot view, is to replace the use of database views with snapshot tables. Representing the a snapshot with tables, rather than using a database view, adds roughly 2.6 Gb to the storage requirements for the example database.
The current snapshot view is essential and is used for most interactions with the database. Therefore, the performance enhancements justify use of the additional disk space required to store the current snapshot in separate tables. If there are specific reasons for extensive access to one or two retrospective snapshots, it might also be worthwhile representing those snapshots in separate tables. However, it would not be worthwhile to apply the same approach to the full range of less frequently used retrospective snapshots. Therefore, if the snapshot views defined in
Link to heading | ||||||
---|---|---|---|---|---|---|
|
...
Snapshot Flags Optimization Method
Overview
A columns are column is added to each full release table. This column is used to represent flags that indicate which snapshot views view each row is included in.
Practical Example
...