In SQL Server Reporting Services (SSRS), reports can be executed in different modes depending on specific requirements and scenarios. The three primary execution modes are: Live or On-Demand Execution, Cached Execution, and Snapshot Execution. Below, I will detail each mode and its use cases.
Live or On-Demand Execution
This is the most basic execution mode, where reports are generated instantly when a user requests them. Each time a user requests a report, the report server sends new queries to the database to retrieve the latest data and generates the report in real-time based on this data.
Applicable Scenarios:
- When it is necessary to ensure users view the latest data.
- When data changes frequently and users require real-time reflection of these changes.
Examples:
- A financial company needs to display real-time stock market data. As stock data changes every second, using Live or On-Demand Execution ensures users see the most accurate current data.
Cached Execution
In Cached Execution mode, report data is stored in the cache upon the first query. Subsequent report requests directly use this cached data instead of querying the database again. Cached data expires after a specified time, and the next request after expiration retrieves the latest data from the database and updates the cache.
Applicable Scenarios:
- When report data does not require real-time updates but report requests are frequent, Cached Execution reduces database load.
- When data updates infrequently but report views are frequent.
Examples:
- A retail company generates sales reports at the end of each day. Data updates only once daily, but reports may be accessed multiple times. Setting up caching avoids repeated database queries and improves report response time.
Snapshot Execution
In Snapshot Execution mode, reports are generated based on predefined time points. Report data is saved as snapshots and stored on the report server. When users request a report, they see the data at the time the snapshot was created, not the current data.
Applicable Scenarios:
- When maintaining consistency in report results is required, ensuring all users see the same data at any time, Snapshot Execution is appropriate.
- When conducting historical comparisons or reviews of data is needed.
Examples:
- Government agencies need to submit quarterly financial reports, which require capturing data at specific time points to ensure accuracy and consistency. Using Snapshot Execution, report snapshots can be generated at the end of each quarter for subsequent viewing and analysis.
By effectively leveraging these three execution modes, SSRS can flexibly address diverse business requirements and optimize report generation and access efficiency.