Performance issues opening specific pages

I’ve been thinking about starting a blog for a while now, but I’ve just kept postponing it. Until now. Last week we had some performance problems connected to opening specific list pages. After finding the cause of the problem, I think there is a high possibility that others also have similar problems but aren’t aware of them. This actually seemed like a great introductory topic for my blog.

Edit

Microsoft introduced a fix for this problem in BC 19.4.

The problem

It all started when we noticed in Telemetrics that some of our customers have performance issues. Since the product facing this problem is POS Solution, where everything needs to be very fast, we cannot tolerate issues like this one. Besides that, how do you explain to a customer that a new shiny ERP solution does not work as fast as it should?

In the last 10 days, Telemetrics showed more than 200 results, where every one of those pointed to the same line of the same function. In the image below you can see 10 of these 200+ most long-running queries together with an SQL statement.

If you take a look at the SQL statement, you would expect that the line of the code pointing from Telemetrics attempts to open the Item list page or something like that. But that is not the case here. As I said, all of the 200+ long-running queries are pointing to the same line of the code, which is line 437 shown in the image below.

Yes, I was feeling the same as you are now while reading this – very confused. A lot of questions were going through my mind. Why would we have a long-running query selecting from the Item table when we obviously opened the Item Variants page? This motivated me to keep digging and find a logical explanation.

Investigation

We had the same issues on the test database so I started debugging just to confirm that this query was really executed after opening the Item Variants page. Guess what; if you put a breakpoint in line 438 and check the Last Executed SQL queries in VS code, you will find one query related to this. Let me paraphrase the famous line from Scarface; The Telemetrics, Chico, Telemetrics never lie. Opening the Item Variants page really caused the next query to be executed.

If you are curious and wish to know how the whole query looks, here it is:

So, basically, it selects all fields from the Item table including the ones from table extensions, resulting in joins, and filtering results by the “No.” field. Great, we confirmed that the query is really here, but the next questions were what caused this to happen and what we could do about that since these are standard Microsoft objects.

Then I got an idea; if the system does this with the standard objects, it does probably the same with the custom ones. Now try to create a custom Item Variants table and page – as a copy of standard ones, but only on your range – and try to open it with the same code as before. What do you think, will the same query be fired? Surprisingly, yes. Wow, really! This gave me a good starting point for narrowing down the cause of the problem. I started by removing the code piece by piece until I found the cause. The cause of the problem was the table relation to the Item table for the “Item No.” field. This was quite shocking for me, but on the other hand, gave me the possibility to continue investigating. It turns out that this type of query would be fired only for the fields which are part of the primary key.

Since I tested this on the custom table and still saw that query, this problem is getting even bigger. That means that opening every list page that has in the source table a primary key table related to another table will cost us additional loading time. Not only that, imagine that there is an Item Variant card page. Will the same query be fired? Unfortunately, the answer is yes.

If you are in a situation to create a new table and you are thinking about adding a table relation to the primary key, think twice. It could have a big performance impact on your system. The example above, about Item and Item Variants, is only one case, but who knows how many similar cases are hiding around the system that we are not aware of.

Workaround solution

We created a ticket for Microsoft, but while we were waiting we decided to create a workaround solution since this was causing us really big problems. As a part of the solution, we created a temporary buffer table and an appropriate list page. So before opening the custom list page of Item Variants we run a query object to select data and fill that data into the buffer table which shows the Variants on the custom page.