Page History
...
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
Caption reference | ||||
---|---|---|---|---|
|
Caption label | ||||
---|---|---|---|---|
| ||||
Testing Performance of Queries on Snapshot Tables and Snapshot Views |
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 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.
...
Code Block | ||||
---|---|---|---|---|
| ||||
SET SQL_SAFE_UPDATES=0; update full_tableName tbl set flag=flag | 1 where tbl.effectiveTime=(select max(sub.effectiveTime) from copy_full_tableName sub where sub.id=tbl.id and sub.effectiveTime<='20190731'); update full_tableName tbl set flag=flag | 2 where tbl.effectiveTime=(select max(sub.effectiveTime) from copy_full_tableName sub where sub.id=tbl.id and sub.effectiveTime<='20190131'); update full_tableName tbl set flag=flag | 4 where tbl.effectiveTime=(select max(sub.effectiveTime) from copy_full_tableName sub where sub.id=tbl.id and sub.effectiveTime<='20180731'); update full_tableName tbl set flag=flag | 8 where tbl.effectiveTime=(select max(sub.effectiveTime) from copy_full_tableName sub where sub.id=tbl.id and sub.effectiveTime<='20180131'); SET SQL_SAFE_UPDATES=1; |
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
...
Code Block | ||||
---|---|---|---|---|
| ||||
-- Create temporary table for the supersededTime values CREATE TEMPORARY TABLE tmp (id CHAR(36) NOT NULL,effectiveTime DATETIME,supersededTime DATETIME, PRIMARY KEY (id,effectiveTime)); -- Compute the supersededTime values for each combination of id+effectiveTime and add these to the temporary file INSERT INTO tmp SELECT tbl.id, tbl.effectiveTime, (SELECT IFNULL(MIN(sub.effectiveTime),DATE "99991231") FROM full_tableName sub WHERE tbl.id=sub.id AND tbl.effectiveTime<sub.effectiveTime) supersededTime FROM full_tableName tbl; -- Apply the appropriate supersededTime values to each row in the full table UPDATE full_tableName tbl JOIN tmp SET tbl.supersededTime=tmp.supersededTime WHERE tmp.id=tbl.id AND tmp.effectiveTime=tbl.effectiveTime; |
Once the flags 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
...