Exam DP-203 topic 2 question 90 discussion

B. Convert the avg_c column into a calculated column. D. Enable result set caching. Explanation: A calculated column is a column that uses an expression to calculate its value based on other columns in the same table. In this case, the udfFtoC function can be used to calculate the avg_c value based on the avg_temperature column, eliminating the need to call the UDF in the SELECT statement. Enabling result set caching can improve query performance by caching the result set of the query, so subsequent queries that use the same parameters can be retrieved from the cache instead of executing the query again. Creating an index on the avg_f column or the sensorid column is not useful because there are no join or filter conditions on these columns in the WHERE clause. Changing the table distribution to replicate is also not necessary because it does not affect the query performance in this scenario

No, Azure Synapse Analytics dedicated SQL pool does not support calculated (or computed) columns1. This means you cannot define columns in your table that automatically compute their values based on other columns in the same table. If you need to include calculated values, you can handle the calculations in your ETL (Extract, Transform, Load) process before loading the data into the dedicated SQL pool. Alternatively, you can perform the calculations in your queries when retrieving data from the table. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-overview#unsupported-table-features

Copilot: (DC) Converting the avg_c column into a calculated column (Option B) might not be as effective for improving query performance in this scenario. Calculated columns can be useful for simplifying queries and ensuring consistent calculations, but they don’t necessarily improve performance, especially if the calculation is complex or if the column is frequently queried. In contrast, enabling result set caching (Option D) and creating an index on the sensorid column (Option C) directly target performance improvements by reducing query execution time and speeding up data retrieval

I don't think D is a solution , "What's not cached .... Queries using user defined functions" from https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/performance-tuning-result-set-caching

chatgpt To minimize the time it takes for the query to return results, you should consider the following actions: A. Create an index on the avg_f column: Creating an index on the avg_f column can improve the query performance, especially if there are frequent searches or filtering based on this column. C. Create an index on the sensorid column: If the sensorid column is frequently used in filtering or joins, creating an index on this column can improve the query performance.

First the wording of the question is ridiculous. "Query is executed once every 15 minutes". So what is it, "Once" or "every 15 minutes"? Either way, they are asking what to do to speed up the query. D Setting result caching E Replicated distribution

correct

correct

Calculated columns exist in Power BI, not dedicated SQL pools. Computed columns are not supported in dedicated SQL pools. Ref: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-overview

correct

With that point by erhard being made (caching does work with queries using UDF), the most commonly voted D is wrong, so B and what now? Replicated cannot be right because it received date everyday and has aggregations so not a dim table and we have no clue about its size. by elimination that leaves us A and C Indexing is less useful with no joins but it does improve some performance being on where clause target. so I'd go with A and B.

need to first chage UDF to a calculated column and then enable result set caching. agreed with the answer

Queries using user defined functions are not cached. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/performance-tuning-result-set-caching

A,C not right since index don't help if join are not involved. D for sure help query performance. I don't get why B: "A computed column is a virtual column whose value is calculated from other values in the table. By default, the expression’s outputted value is not physically stored. Instead, SQL Server runs the expression when the column is queried and returns the value as part of the result set ... In many cases, non-persistent computed columns put too much burden on the processor, resulting in SLOWER QUERIES and unresponsive applications" Since the only requirements is faster execution times for queries, i don't think calculated columns will improve performance. Si second option for me would be D (replicate). Although it will cause more effort writing, because updates should be written to every partition, optimized writes aren't a requirement in the question.

pool ingest data once per 24 hrs, while query happens every 15mins, caching result can definitely avoid the some duplicate calculation, I'll go with BD.

I think should be DE. since "the query is executed once every 15 minutes and the @parameter value is set to the current date", and the it receives new data once every 24 hours, it means the query result isn't change in one day even you run it every 15 mins. The data is static within a day. Replication could help the performance.

Answer is Correct !