SNOMED Documentation Search


 Other Documents
Skip to end of metadata
Go to start of metadata

Current Version - Under Revision

The first optimization approach is provide a simple way to optimize the current snapshot and can be extended to cover a limited number of additional snapshot views. A column is added to each component table to hold a boolean value that indicates whether or not a particular row is part of the current snapshot. In the following Description and example this added column is called `inSnapshot` and is referred to as a " snapshot view flag".

After importing or updating SNOMED CT content the snapshot view flag is updated using the results of a snapshot view query such as one illustrated in . The example uses an intermediate temporary table. In some relational database environments nested queries could be used to reduce the number of steps in the script. However, the longer form is used here as some environments do not work (or are unpredictable) when updating a table that is also referenced by a nested select query.

/* Clear the inSnapshot flag */

UPDATE `sct2_concept` SET `inSnapshot`=False;

/* Create temporary table to hold latest id+effectiveTime */

DROP TEMPORARY TABLE IF EXISTS `tmp_ids`;

CREATE TEMPORARY TABLE `tmp_ids` (`id` BIGINT,`effectiveTime` DATETIME, PRIMARY KEY (`id`));

/* replace the line above with the line below for Refsets as the Id is a UUID rather than SCTID */

/* CREATE TEMPORARY TABLE `tmp_ids` (`id` BINARY(16),`effectiveTime` DATETIME, PRIMARY KEY (`id`)); */

/* Populate the temporary table with id+effectiveTime for the latest view*/

INSERT INTO `tmp_ids` SELECT `id`,`effectiveTime` FROM `sct2_concept` AS `c`

WHERE `c`.`effectiveTime` = (SELECT MAX(`c2`.`effectiveTime`)

FROM `sct2_concept` `c2`

WHERE `c2`.`id` = `c`.`id`);

/* Use the temporary table to update the inSnapshot flag for relevant rows */

UPDATE `sct2_concept` AS `c`,`tmp_ids` AS `t`

SET

`inSnapshot` = True

WHERE `c`.`id`=`t`.`id` AND `c`.`effectiveTime` = `t`.`effectiveTime`;

/* Clean up by removing the temporary table */

DROP TEMPORARY TABLE `tmp_ids`;

Figure 26. Setting the latest snapshot view flag

The following query illustrates the simple query that can be used to return the current snapshot view using the snapshot view flag.

SELECT `c`.* FROM `sct2_concept` AS `c` WHERE `c`.`inSnapshot` = True;

Figure 27. Using a snapshot view flag to select components in a snapshot view

The same approach can be applied to each of the components by replacing `sct2_concept` with the relevant table name.

Additional snapshot view flags can be added, set and used in a similar way for a few other snapshot times that need to be optimized .

/* Clear the inSnapshotPrev flag */

UPDATE `sct2_concept` SET `inSnapshotPrev`=False;

/* Create temporary table to hold latest id+effectiveTime */

DROP TEMPORARY TABLE IF EXISTS `tmp_ids`;

CREATE TEMPORARY TABLE `tmp_ids` (`id` BIGINT,`effectiveTime` DATETIME, PRIMARY KEY (`id`));

/* replace the line above with the line below for Refsets as the Id is a UUID rather than SCTID */

/* CREATE TEMPORARY TABLE `tmp_ids` (`id` BINARY(16),`effectiveTime` DATETIME, PRIMARY KEY (`id`));

/* Populate the temporary table with id+effectiveTime for the specified view date time */

INSERT INTO `tmp_ids` SELECT `id`,`effectiveTime` FROM `sct2_concept` AS `c`

WHERE `c`.`effectiveTime` = (SELECT MAX(`c2`.`effectiveTime`)

FROM `sct2_concept` `c2`

WHERE `c2`.`id` = `c`.`id` AND `c2`.`effectiveTime` <= CAST('2010-01-31', DATETIME));

/* Use the temporary table to update the inSnapshotPrev flag for relevant rows */

UPDATE `sct2_concept` AS `c`,`tmp_ids` AS `t`

SET

`inSnapshotPrev` = True

WHERE `c`.`id`=`t`.`id` AND `c`.`effectiveTime` = `t`.`effectiveTime`;

/* Clean up by removing the temporary table */

DROP TEMPORARY TABLE `tmp_ids`;

Figure 28. Setting the snapshot view flag for a specified date

This approach provides a simple approach to optimization of a limited number of views. However, it is constrained by the need to allocate a column for each time for which an optimized snapshot view is required.


Feedback