As noted in Release Type Options, SNOMED CT data can be imported into database tables from snapshot release files, from full release files or from both these sets of release files. The objective of importing the data into the database is to provide effective access to useful views of that data. In practical terms this means facilitating access to some or all of the views summarized in Table 1 below.
Table 1: Summary of Versioned Views of SNOMED CT Components and Reference Set Members
Table 2 summarizes the way in which different release type options affect the ability to access particular snapshot and delta views of SNOMED CT data. Importing the snapshot release supports direct access to the current snapshot view and query access to the most recent delta view. The full release provides access to all snapshot and delta views but is likely to perform slightly less well with the current snapshot view. Importing both full and current snapshot releases offers all the advantages of importing the full release and also provides direct access to the current snapshot view. This combined option requires more storage capacity but may be worthwhile because the current snapshot is likely to be the most commonly used view.
Table 2: Summary of Versioned View Access Capabilities Depending on Release Types Imported
The following subsections explore specific mechanisms that can be used to deliver these views in a relational database.
Versioned Database Table Views
Optimizing Versioned Table Views
See also
Extracting the current snapshot from a full release table requires a query that can identify the rows with the most recent effectiveTime for each uniquely identified component or reference set member. The general form of a current snapshot view query is shown below.
General Snapshot Query - Replace full_tableName with Name of a Full Release Table
It may be tempting to write queries that add criteria specific to a particular query within the structure of a general snapshot query. However if this is done, it must be done with care because additional conditions may cause incorrect results.\
Additional criteria to be applied to snapshot view must be added to the outer query to deliver the expected results. Otherwise they may inadvertently exclude the most recent component from the snapshot and thus leading to a misleading result.
Queries for earlier snapshot views require an additional condition so that only versions with an effectiveTime that is equal to or earlier than (i.e. less than) the date of the snapshot. In this case, it is correct to include this condition in the nested query because the objective is to constrain the maximum effectiveTime to that the subquery returns. This ensures that the outer query does not return component versions added after the specified snapshot time.
Retrospective Snapshot Query for Snapshot as at 2019-01-31
The most recent delta view is usually considered to be all the rows in a table with an effectiveTime equal to the release date. The general form of a query for the most recent delta view is shown below. Note that this query can be applied either to full release tables or the current snapshot release tables.
Most Recent Delta View Query for 2019-07-31 release. Replace tableName with Name of a Full or Snapshot Release Table for 2019-07-31 Release
A more general purpose approach to delta views is to include all changes between two specified dates (or times). This can be applied to the period between two releases or to a defined period during which multiple changes may have occurred to the same component. The general form of a delta view query for a specified period is shown below.
General Most Recent Delta View Query - for Change After 2019-01-31 and On or Before 2019-07-31
It is also possible to create an enhanced delta view that not only shows which components have changed but allows the pre-change state of that component to be seen. From a practical perspective this simply combines a delta view for a range of dates with a retrospective snapshot view for the delta view start date.
Delta View with Details of the Component Prior to the Changes
The previous section described queries that can be applied to database tables to access snapshot and delta views of individual database tables. However, meaningful access to SNOMED CT requires querying of interrelated data in multiple database tables including concepts, descriptions, relationships and reference sets. In most cases, the data from each of those tables needs to be from a snapshot view for the same date. This implies these composite need to derive information from snapshot queries applied to the table rather than directly accessing the table. Consequently, what should be relatively simple queries rapidly become complex, making them hard to understand and prone to errors.
Fortunately, relational databases provide solutions that reduce this complexity by allowing a query to be used to define a database view. These defined views can then be queried in exactly the same way as a database table. The result of this is that if all the required snapshot and delta views are defined for every table in a SNOMED CT database, it is possible to write relatively simple queries that return useful combinations of data from snapshot views of different tables.
The examples below two snapshot database views of a full release table. The first is current snapshot view and the second a is snapshot for 2019-01-31.
Creating a Current Snapshot View from the Full Concept Table
Creating a Retrospective Snapshot View for 2019-01-31 from the Full Concept Table
Having created those two views it is then possible to write queries like the examples below to display data from these snapshot views.
Creating a Current Snapshot View from the Full Concept Table
Creating a Retrospective Snapshot View for 2019-01-31 from the Full Concept Table
Comparing the results of these two queries shows that in January 2019 both concepts changed in the 2019-07-31 release. The concept with id 3859001 was made inactive and the definitionStatusId of concept with id 3704008 was changed from 900000000000074008 (primitive) to 900000000000073002 (defined).
It is possible to create snapshot views of every release file for every SNOMED CT release of SNOMED CT. However, this would result in over 600 distinct snapshot table views, most of which would rarely be used. A more practical solution is to create views for the current snapshot and two or three retrospective views with a configurable snapshot date. The SNOMED CT example database includes two retrospective snapshot views with a snapshotTime value in an identified row in a specifying the snapshot date.
The definition of one of these views is shown here. The other view (snap2_concept) has the same definition except that it specifies `cfg`.`id` = 2 rather than `cfg`.`id` = 1 so it refers to a different row in the configuration table. Having two independently configurable snapshots, allows queries to be written than compare different snapshots. Because the same configurable views are provided for all full release tables, changes to the snapshotTime for a view apply simultaneously to all those components and reference set members.
Example of Configurable Retrospective View Definition
The SNOMED CT example database also includes two configurable delta views. These are created and configured in the same way as the configurable snapshot views. However in this case, deltaStartTime and deltaEndTime values in the identified configuration table row specify the delta period.
Example of Configurable Retrospective View Definition
One of the points identified in the previous section is that snapshot queries and database views are less likely to perform as well as direct access to database tables. As shown in Table 3 on the SNOMED CT example database confirm that there is a substantial performance difference between these two approaches.
Table 3: Testing Performance of Queries on Snapshot Tables and Snapshot Views
Based on these 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 do not perform sufficiently well, it is worth considering ways to optimize snapshot access.
Unlike the views described earlier, the optimization methods described below require additional columns to be added to each of the full release tables. After importing release files, the tables are processed to generate values for the additional columns and this data is used to simplify the snapshot view queries by avoiding the need for nested queries.
A column is added to each full release table. This column is used to represent flags that indicate which snapshot view each row is included in.
A single 64-bit integer column called flag is added to all full release tables with a default value of 0 (zero).
A distinct number which is a power of 2 between 20 and 263 is assigned to each required retrospective snapshot time.
The flag column in each row is set to the sum of the values of all the snapshots in which that row appears.
The example SQL below illustrates the process of flag setting. In practice, while this query works it is not very efficient for several reasons. A more efficient approach would be to use a stored procedure that computes the full set of flags applicable to each row. This approach would allow the flag column in each row to be updated once rather than requiring a separate update for each snapshot view.
Illustrative Example of a Flag Setting Query
Once the flags are set, a query such as the one below can be used to return component versions that are part of a particular snapshot view. The example query returns row in which the flag the second bit (value 2) is set. Based on the settings in the query above this means it would include rows that are part of the 2019-01-31 snapshot view of this table
Example of a Snapshot Query Using the Flag Method
Limited testing of this optimization approach indicates that it is between 2 and 3 times faster than the unoptimized snapshot views. Direct access to a snapshot table is still twice as fast as this optimized approach.
The full release files in the 2019-07-31 release contain a total of approximately 16 million rows. If flags are added each of these rows will require a further 8 bytes of storage. No additional indexes are required to support this optimization. As a result the overall increase in storage requirements to support this optimization is less than 150 Mb.
As described here the approach is limited to 64 snapshot times. This is probably more than sufficient for most practical requirements. However, it and could be extended by adding an another flag column or by changing the data type of the flag column to binary.
The process of setting the flags required for this approach adds significantly to the time taken to build the database.
Adding an additional column to every table means that queries using "SELECT * FROM ... " will return a flag column that is not part of the original SNOMED CT data.
The flag values are technically essential to the process but this may not be apparent to anyone exploring the database.
A significant improvement in retrospective snapshot performance compared with unoptimized tables.
Minimal impact on disk capacity (adds less that 5% to the size of the full release tables).
Provides a fallback option for the current snapshot view if storage capacity is limited.
An additional datetime column is added to each row. This column is used to represent the time at when a row was replaced by the next version of that component or reference set member.
A single datetime column called supersededTime is added to all full release tables with a default value of a long distant future date (e.g. 9999-12-31).
Each full table is queried to establish the sequence of versions of each component or reference set member in effectiveTime order.
The supersededTime of a component that have been updated is set to the effectiveTime of the immediately following version of that component
The example SQL below illustrates the process of flag setting. In practice, while this query works it is not very efficient for several reasons. A more efficient approach would be to use a stored procedure that computes the full set of flags applicable to each row. This approach would allow the flag column in each row to be updated once rather than requiring a separate update for each snapshot view.
Illustrative Example of Queries Setting the supersededTime Value
Once the superseded time values are set, a query such as the one below can be used to return component versions that are part of a particular snapshot view. The example query returns row in which the flag the second bit (value 2) is set. Based on the settings in the query above this means it would include rows that are part of the 2019-01-31 snapshot view of this table
Example of a Current Snapshot Query Using the supersededTime Method
Example of a Retrospective Snapshot Query Using the supersededTime Method
Past experience indicates that this approach is 2 times faster than the unoptimized snapshot views. However, this figure varies depending on the complexity of the queries it is used in. Direct access to a snapshot table is between 2 and 3 times as fast as this optimized approach.
The full release files in the 2019-07-31 release contain a total of approximately 16 million rows. If a datetime column is added, each of these rows will require a further 5 bytes of storage. Database designs using this additional column also included additional indexes including supersededTime. As a result, the storage to fully support this approach required an additional 750 Mb.
The process of setting the supersededTime required for this approach adds significantly to the time taken to build the database.
Adding an additional column to every table means that queries using "SELECT * FROM ... " will return the supersededTime column that is not part of the original SNOMED CT data.
The supersededTime values are technically essential to the process but this may not be apparent to anyone exploring the database.
An improvement in retrospective snapshot performance compared with unoptimized tables but is out-performed by the snapshot flagging method.
A fallback option for the current snapshot view if storage capacity is limited but requires more storage than the snapshot flagging method.
Supports an unlimited number of snapshot times.