A View generally can be used just to join few tables and see the data.
But the versatility of the Views is not just limited to this.
It can be used in multiple places where performance is critical. Because a view offers the benefit that it is pre executed on DB Synchronization and not on run time which means though it has many table joins it is already loaded and ready to use.
Here are some of the critical places where a view must be used.
Form Level:
Lets say we have a form, standard or custom. We got a requirement to add some more fields just for display purpose.
The first idea we get here is to write some display methods. Because it is simpler to do. This simplicity kills performance.
On a better approach, a join can be made to the related tables with the form's datasource having a relation prior and then adding the field works here.
But after doing all the above exercise and keeping everything correct, may be with data issue where it has 1:n cardinality, our form may render with duplicate records eventually we end up in choosing a display method.
But here if we make use of a view, then all the duplicating issues will be resolved with increased performance as the view is loaded on synchronization.
Let's say we have A and B tables with cardinality as 1:n.
Generally when we use a display method, the thumb rule, though we have multiple records is to fetch single record right? So we use simply a select firstonly.....
To replicate same in a view, following approach can be followed.
A view should be build with the 2 tables A & B and doing a grouping on a field in B table and once AB view is built, then it should be joined with the A datasource in form to the A table in the View..
For grouping of a field in View,
We have a GroupBy Column in View like below.
Now lets say we got a new requirement upon this to fetch more fields from the added table. If a new field is simply added to the list, it gives us error saying
Msg 8120, Level 16, State 1, Line 2
Column 'TABLENAME.FIELD' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Also we can't simply add all the fields in a Group By which will cause data issue.
For this, in SQL we does this like below by using MAX keyword.
SELECT A.FIELD1, B.FIELD2, MAX(A.FIELD2), MAX(B.FIELD3)
FROM A
JOIN B
ON B.FIELD1 == A.FIELD1
GROUP BY A.FIELD1, B.FIELD2
We are going to replicate the 'MAX' keyword in the View.
The MAX keyword can be implemented in view by below.
- To add a View unmapped string
- To add a method like below and assign to previous string
- Drag and drop of same field from datasource. This dragged field will show us the expected value.
The computed method will inform the view metadata, that the field is included in a MAX keyword. So without grouping with that field, the fields can be added.
The same approach can be followed for any aggregate function like SUM.
Going to our older discussion on display method, the MAX and Groupings will give same functionality like the display method with added performance advantage. By this user will never face slowness in loading a form.
Code level:
We use while select for retrieval of data from a set of records. The while select in X++ is not that faster like a SELECT in SQL which works better if we have a group by clause as well too.
Lets say in a scenario where we need to create a CSV file where it involves some grouping. Using a while in X++, has the grouping keyword like in SQL, but it never informs the developer that the fields are to be added in Grouping or Aggregate columns. And also we cannot see what data is being exported until unless as a developer we go and run that job. Only an analysis in mind will be done. We can give the same while select query in SQL and check and then try in X++. But it gives weird results of data when compared to SQL.
Keeping our purpose first i.e., to see the data, we will create a SQL query. If the same query is used to build a view, then the data can be verified directly in SQL and the same is used with a simple while or may be a SysDa to loop. Now we can get an idea what is being exported before unit testing.
Important thing here is as the joins and groupings are not made on run time like in while loop, this will be more faster than a normal while select with all the groupings and joins offering us improved performance.
Ranges:
For better retrieval, ranges also can be provided to a view like we do in normal X++.
Lets say a view is created which will only pick records with barcode, then the range will be added like below.
To give a range to fetch records having Available Physical more than 0, then the range is provided like below.
​​​​​​​
Best thing here is, if we are able to make a view, then the same can be used to create a Data Entity because the base framework of linking tables for both View and Entity are same with only difference is Data entities can be imported or exported. So Base DMF can be used for CSV export too running this from custom X++ logic.
View Definition:
Now lets say we built a View and all of a sudden the data is not showing correctly and couldn't figure out why, because it consists of many tables.
The simplest way to understand the metadata of the View is to navigate to SQL and search like below.
EXEC sp_helptext 'View Name'
This gives us the View definition. Using that narrowing down the actual issue can become easier.
In this way many critical performance concerns can be resolved with a View.
Will be updating the same blog for any new findings on View.
Stay Tuned!
