Framework Manager: Model Query Subject vs. Datasource Query Subject 1

[LearnByDoing]

Can anybody list the pros and cons of using a Model Query Subject over a Datasource Query Subject? Which one would yield better performance?

 

[dmunson]

Generally, datasource query subjects are used at the database layer of your model, closely resembling the names and structure of the underlying database. While Model Query subjects are used for a business layer and/or presentation layer. These layers contain Model Query Subjects created by merging multiple columns and calcs from other query subjects (model or data).

The performance part is a bigger question. Your queries will be built using derived tables. In my experience DBA's are very leery (to put it mildly) of these as they look similar to sub-selects. They are not sub-selects, they are similar to writing an inline view and as such are able to utilize all of the indexing and statistics available to the query engine. You will see a number of select statements but the database optimizer will see these and build the appropriate query plan

2 things to take note of in Model Query Subjects

1. Use dimensionality if your users will be summarizing your data.
By setting dimensionality and giving framework manager unique keys at different grouping levels, the query engine can stitch derived tables back together after they are summarized with a minimum number of columns. Without defining unique columns it will routinely use ALL matching, not aggregate columns for the join. Also, if you don't add dimensionality the "Generate SQL" option is always "As View" (see #2).

2. Set the Generate SQL option to “Minimized” if possible.
Double click on a query subject to open the definition window, then select the options button in the top left corner. In the options window, select the "SQL Settings tab. Set the Generate SQL Option to "Minimized".
Unless you queries require that all columns be selected from all tables in the Query Subject, set the Generate SQL option to "Minimized". Setting it to "As View" make the subject act as a view would, where the query is run against the entire SQL statement containing all rows.

For example, say I have a model query subject that looks like this
[tt]
Column Name | Source
---------------+--------------------------------
Customer name | [customers].[customer name]
Order number | [Orders].[Order Number]
Order Date | [Orders].[Order Date]
Product Line | [Products].[Product Line]
Product | [Products].[Product]
Price | [Order Details].[Price]
Qty | [Order Details].[Qty]
Ext Price | [Price] * [Qty]
[/tt]
Now say I want a list of product types and their totals for a specific time period. But I do not need any information about the customer.

If the Generate SQL Property is set to “As View”, the join to the customer table will still be in my query. By setting it to "Minimized" the customer table is removed from the generated sql resulting in a faster query.




In the end you will usually end up using both. And despite lot’s of objections to the contrary derived queries are not as slow as they look and will perform comparable to the queries you may have seen in impromptu.