We have custom on the advanced product search (form: MCRSalesQuickQuote). It runs a bit slower than the standard.
I came across the following link on how to determine if a view is missing an index:
https://kaya-consulting.com/view-slow-maybe-sql-missing-index-can-help-you/
One of the datasources in this form is a view called: MCRSalesQuickQuoteSumUnion.
So I tried the above link's suggestion to create a estimated execution plan on this view. It didn't indicate that it was missing an index. So that is good.
There are Hash Match with a cost % and then there are "Index seek (nonculstered)" with a cost %. InventDim had a cost % of 18% and InventSum had a cost % of 15%. I added some indexes on these 2 tables by hovering my mouse over the diagram for the specific table and it gave me the Seek Predicates. I added an index according to that. I got InventDim down to 8% and InventSum down to 7%. However the hash match cost % went up from 9% to 13%.
So, did I accomplish something or did I just shifted the cost from invetnSum and InventDim to the hash match. I'm not an SQL expert (my experience lies more with the Progress database).
I will really appreciate it if somebody can explain the difference between the hash match cost and the actual table cost for me so that I can figure out if it is really worth adding the 2 extra indexes on InventDim and InventSum
