Exam DP-203 topic 1 question 25 discussion

B is correct. Materialized view and result set caching These two features in dedicated SQL pool are used for query performance tuning. Result set caching is used for getting high concurrency and fast response from repetitive queries against static data. To use the cached result, the form of the cache requesting query must match with the query that produced the cache. In addition, the cached result must apply to the entire query. Materialized views allow data changes in the base tables. Data in materialized views can be applied to a piece of a query. This support allows the same materialized views to be used by different queries that share some computation for faster performance.

B is the correct answer. A materialized view is a database object that contains the results of a query. A materialized view is not simply a window on the base table. It is actually a separate object holding data in itself. So query data against a materialized view with different filters should be quick. Difference Between View and Materialized View: https://techdifferences.com/difference-between-view-and-materialized-view.html

B is incorrect. A materialized view MUST contains one of the following a select that contains an aggregate function and/or a Group by clause. See the below article https://learn.microsoft.com/en-us/sql/t-sql/statements/create-materialized-view-as-select-transact-sql?toc=%2Fazure%2Fsynapse-analytics%2Fsql-data-warehouse%2Ftoc.json&bc=%2Fazure%2Fsynapse-analytics%2Fsql-data-warehouse%2Fbreadcrumb%2Ftoc.json&view=azure-sqldw-latest&preserve-view=true

also why C is incorrect: "When cached results are used: There is an exact match between the new query and the previous query that generated the result set cache." but in our case we need additional WHERE clause https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/performance-tuning-result-set-caching#when-cached-results-are-used

Correct Answer: B

As materalized views stores data. we can write once and can read from it mutiple times

Got this question on my exam on january 17, B is correct

Materialized Views: Create materialized views that store the results of the complex SELECT queries. Materialized views are precomputed views stored as tables, and they can significantly reduce query times by avoiding the need to recompute the results every time the query is executed.

A. an ordered clustered columnstore index => this will impact the where clause so it is a valid option B. a materialized => can't be used since there is no aggregation C. result set caching => We don't know if the output query respects the limitation (10 gb ) so no D. a replicated table => sizes of lookups tables not mentioned so even if it is a possible solution it's not a suggested approach

Was on my exam today (31.10.2023).

Materialized view

There is no information that this query aggregates data. "SELECT list in the materialized view definition needs to meet at least one of these two criteria: The SELECT list contains an aggregate function. GROUP BY is used in the Materialized view definition and all columns in GROUP BY are included in the SELECT list. Up to 32 columns can be used in the GROUP BY clause." I'm not sure that B is correct answer. Unfortunately I cannot see better answer

B is correct

B is correct acc to: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/performance-tuning-materialized-views https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/performance-tuning-materialized-views

B materialized view

B is correct without a doubt

Why isn't the answer "A" when the query may have additional WHERE parameters depending on the report. That mean's the query isn't static and will change depending on the report. A clustered columstore index would provide a bettery query performance in case of a complex query where query isn't static.