22 January 2016

How to restrict view datasource fields

A view is specified by a query and there are two ways to define the view query - add existing query or add datasources to Metadata node:

Both options deal with datasources. It is recommended to restrict datasource fields and return only the fields which are actually used. Unused fields generate useless traffic and decrease performance. Is such recommendation relevant for the view Metadata?

Hints
Analyse the view CREATE script in SQL Server Management Studio. You can also jump to the conclusion section.

Solution
1. Let's analyse CustOpenInvoices view
CustOpenInvoices view is based on CustOpenInvoices query. All query datasources have Dynamic property set to Yes, it means that all fields are returned from the corresponding tables:
In fact the view uses only several fields returned from the query:
Let's find the view in SQL Server Management Studio and open CREATE script:
Even though the query returns all fields from the tables, only the view fields (plus system fields DataAreaId, Partition, RecId) are selected from database:

2. Let's analyse CustSalesOpenLines view
The view has datasources structure and all datasources have Dynamic property set to Yes:
The view uses only several fields returned from datasources:
Again only the view fields are selected from database:

Conclusion
If a view is based on an existing query and the query is used exclusively for the view, then it is not required to restrict the fields returned by the query, because only the view fields are fetched from the database.
If a view is based on datasources, then it is not required to restrict the fields of datasources either, because only the view fields are fetched from the database.

No comments:

Post a Comment