5.1. Performance

The performance of a system based on Data Vault is a key factor for ensuring fast and stable data access. Optimizing the model's operation includes efficient design, query tuning, load management, and the use of appropriate technologies.

Key Aspects of Performance Improvement

1. Data Structure Optimization

Why is it important?
Data Vault includes many tables (hubs, links, and satellites), which can slow down complex queries.

Solutions:

Using indexes:
Create indexes on frequently used keys (e.g., Surrogate Keys and Business Keys).
```
CREATE INDEX idx_hub_passengers_key 
ON Hub_Passengers (HubPassengerKey);
```

Denormalization when necessary:
For slow analytical queries, create temporary or materialized views.

CREATE MATERIALIZED VIEW mv_passenger_flight_stats AS 
SELECT 
    L.HubPassengerKey, 
    COUNT(L.HubFlightKey) AS TotalFlights
FROM 
    Link_PassengerFlights L
GROUP BY 
    L.HubPassengerKey;

2. Effective Data History Management

Why is it important?
Satellites store change history, leading to increased data volume.

Solutions:

Data partitioning by time intervals:
Use table partitioning by load date.

CREATE TABLE Sat_FlightDetails (
    HubFlightKey INT,
    LoadDate DATE,
    FlightStatus VARCHAR(50),
    PRIMARY KEY (HubFlightKey, LoadDate)
)
PARTITION BY RANGE (LoadDate) (
    PARTITION p2023 VALUES LESS THAN ('2024-01-01'),
    PARTITION p2024 VALUES LESS THAN ('2025-01-01')
);

Archiving old data:
Move outdated data to archival storage if it is rarely queried.

3. ETL/ELT Process Optimization

Why is it important?
Data loading stages in Data Vault can become a bottleneck, especially when processing large volumes of data.

Solutions:

Parallel data loading:
Split processing into multiple threads to work simultaneously with hubs, links, and satellites.

Incremental loading:
Load only new or modified data to reduce processing time. Example:
Using the LoadDate field to load only new records:

INSERT INTO Sat_FlightDetails (HubFlightKey, LoadDate, FlightStatus)
SELECT 
    HubFlightKey, LoadDate, FlightStatus
FROM 
    Staging_FlightDetails
WHERE 
    LoadDate > (SELECT MAX(LoadDate) FROM Sat_FlightDetails);

4. Query Tuning

Why is it important?
Suboptimal queries can slow down the analytical system.

Solutions:

Avoid redundant joins:
Ensure queries connect only the necessary tables.

Use analytical functions:
Analytical functions (e.g., ROW_NUMBER, RANK) are more efficient for some operations than subqueries.

SELECT 
    HubPassengerKey, 
    FlightStatus, 
    ROW_NUMBER() OVER (PARTITION BY HubPassengerKey ORDER BY LoadDate DESC) AS rn
FROM 
    Sat_FlightDetails
WHERE 
    rn = 1;

5. Monitoring and Profiling

Why is it important?
Understanding bottlenecks allows for targeted performance improvements.

Solutions:

Use database profilers (e.g., SQL Server Profiler or EXPLAIN in PostgreSQL) to analyze queries.
Set up automatic notifications for slow queries or overloads.

Example: Performance Analysis

Task: Determine why a query counting flights for a passenger is slow.

Original query:

SELECT 
    P.HubPassengerKey, 
    COUNT(F.HubFlightKey) AS TotalFlights
FROM 
    Hub_Passengers P
JOIN 
    Link_PassengerFlights L ON P.HubPassengerKey = L.HubPassengerKey
JOIN 
    Hub_Flights F ON L.HubFlightKey = F.HubFlightKey
GROUP BY 
    P.HubPassengerKey;

Analysis:

Three joins are used, increasing the load.
No indexes on keys.

Optimized query:

Added indexes on HubPassengerKey and HubFlightKey.
Reduced the number of tables in the query.
Used pre-aggregation.

CREATE INDEX idx_link_passenger ON Link_PassengerFlights (HubPassengerKey, HubFlightKey);

SELECT 
    HubPassengerKey, 
    COUNT(HubFlightKey) AS TotalFlights
FROM 
    Link_PassengerFlights
GROUP BY 
    HubPassengerKey;

Result: Query execution time reduced from 10 seconds to 2 seconds.

Conclusion

Data Vault performance directly depends on proper design, configuration, and data management. Regular monitoring, index application, ETL process optimization, and the use of efficient queries enable the creation of a system capable of handling large data volumes and ensuring fast execution of analytical tasks.

Supplement: Data Partitioning and Compression

Partitioning and compression are key approaches to improving performance when working with large data volumes in Data Vault. These techniques help manage data, reduce query execution time, and optimize storage usage.

Partitioning

Partitioning is the division of a table into logical parts, each processed independently.

Advantages of partitioning:

Faster query execution: SQL queries process only the necessary data partitions.
Loading optimization: New data loading affects only active partitions.
Simplified archiving: Old data can be archived by removing outdated partitions.

Types of partitioning:

By date range (Range):
Used to split data by time.

Example:
Partitioning a satellite table by LoadDate.

CREATE TABLE Sat_FlightDetails (
    HubFlightKey INT,
    LoadDate DATE,
    FlightStatus VARCHAR(50)
)
PARTITION BY RANGE (LoadDate) (
    PARTITION p2023 VALUES LESS THAN ('2024-01-01'),
    PARTITION p2024 VALUES LESS THAN ('2025-01-01')
);

Advantage: Queries related to a specific period are processed faster.

By hash (Hash):
Dividing into partitions based on key value.

Example:
Partitioning a link table by HubPassengerKey.
```
CREATE TABLE Link_PassengerFlights (
    HubPassengerKey INT,
    HubFlightKey INT,
    LoadDate DATE
)
PARTITION BY HASH (HubPassengerKey) PARTITIONS 4;
```
Advantage: Improved data distribution and uniform server load.

Using partitioning in queries:

When partitioning, the SQL server automatically determines which partitions need to be processed if the query contains key columns.

Example query with partitioning by LoadDate:

SELECT 
    HubFlightKey, FlightStatus
FROM 
    Sat_FlightDetails
WHERE 
    LoadDate BETWEEN '2024-01-01' AND '2024-06-30';

Data Compression

Data compression is the process of reducing data volume to lower storage costs and improve performance.

Advantages of compression:

Disk space savings: Compression allows storing large data volumes with lower costs.
Faster data reading: Compressed data requires fewer I/O operations when reading.
Reduced network load: Transmitting compressed data reduces traffic volume.

Types of compression:

Row Compression:
Reduces storage volume by eliminating unused space.

ALTER TABLE Sat_FlightDetails REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW);

Page Compression:
More aggressive compression, including row compression and removal of duplicate values.
```
ALTER TABLE Sat_FlightDetails REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);
```
Columnar Compression:
Used in storages like Azure Synapse and Snowflake, where data is stored in columnar format.

Example of compression in practice:

Before compression:

The Sat_FlightDetails table occupies 1 TB.

After compression:

Applying PAGE compression reduces the volume to 400 GB.

Combining Partitioning and Compression

These techniques work especially effectively in combination.

Example:

A satellite table is partitioned by load date:

CREATE TABLE Sat_PassengerDetails (
    HubPassengerKey INT,
    Name NVARCHAR(100),
    LoadDate DATE
)
PARTITION BY RANGE (LoadDate) (
    PARTITION p2023 VALUES LESS THAN ('2024-01-01'),
    PARTITION p2024 VALUES LESS THAN ('2025-01-01')
);

Each partition is compressed to save space:

ALTER TABLE Sat_PassengerDetails REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE);

Result:

Reduced data volume.
Faster queries for specific time ranges.

Conclusion

Data partitioning and compression are powerful tools for improving performance and saving resources in a Data Vault system. They help efficiently manage large data volumes, improve query execution speed, and reduce storage costs. The use of these approaches should be integrated into the Data Vault architecture to maximize the system's potential.