You may have created some custom views in your Sage X3 system but are finding them slow to execute.   The first step in diagnosing poorly performing SQL is often checking the SQL execution plan, but how can you do this with Sage X3 custom views?

First thing is to check the custom view SQL in Development, Data and parameters, Views   All we initially care about is the name of the view, but may also want to make note of the SQL that is being executed:

We now need to run a query against the custom view in SQL Server Management Studio (SSMS)   In the object explorer, under views, you will find the custom view

Run the slow performing SQL that uses the custom view.  In my case I'll keep it simple and just select everything.

set statistics time on
select *
from [SEED].[MZCUSTOM

In the "Messages" tab you can see the time taken to execute

Now we'll run the same SQL again, but this time select the “Include actual execution plan” option before we do so:

You will see the tab "Execution Plan"   Here it may (as in this case) suggest a missing index to speed up the query

You can right click on the missing index text and select “Missing index details”

It is not necessarily a good idea to implement any or all of any suggestions that SSMS provides, however it is a good starting point for you to identify the root cause of a poorly performing custom view and consider how you may be able to implement any changes without impacting any other areas.

So why did I just use the custom view for my SQL, rather than test with the underlying SQL the view is using?  Well it depends on how you are using the view.  The execution plans will possibly be different, although may end up with the same recomendation.

You can read more about SQL Server excution plans in the Microsoft documentation at https://docs.microsoft.com/en-us/sql/relational-databases/performance/execution-plans?view=sql-server-ver16

I hope this has given you some food for thought, and welcome any comments below.