Relationship between BLOB fields and SetLoadFields()

It was great to hear a lot of positive comments after I published my previous post so I hope I won’t disappoint you and that this post will be at least as useful as the previous one. At the moment, we are experiencing some performance issues because of the BLOB fields. Maybe some of you have similar problems and possibly are not even aware of them. This seems like a great continuation of my blog. Because of that, the structure of this post is similar to the structure of the first one. Let’s get started with the Problem part.

Edit

The problem with obsoleted BLOB fields is solved in BC 19.3.

The problem

We noticed this problem in, where other than, Telemetrics. As mentioned in the previous post, the product facing this is a POS solution, so we cannot tolerate any performance issues. Telemetrics has been shown as a great tool for detecting that type of issue. In the image below you can see the 10 longest-running queries related to this problem. Look closer at the image. You will see only a few fields selected from the “Item” table. Yes, you are right, SetLoadFields method is used to retrieve a partial record from the database. This way we can gain a great performance improvement and our queries are supposed to be very fast. But wait, why does it take around 3 seconds for our queries to be executed.

Something is weird here; we will need to take a look into the line of code which Telemetrics is pointing at – it is the line 33 in the next image.

Now it is confirmed. I only asked for the “No.” field from the Item table so I was a little confused when I saw DATALENGTH(“My BLOB Field”) also included in the statement. Just to be clear, the problem here is not about DATALENGTH, the problem is because the BLOB field is from the Item table extension, and because of that, it causes JOIN on SQL statement which costs a lot. I looked in the official documentation for the SetLoadFields but didn’t find anything about the BLOB fields. There was something mentioned only in the Remark section saying that some extra fields could be loaded. That must be it. I decided to raise an issue for Microsoft regarding this on their GitHub. You can find more about it on https://github.com/microsoft/AL/issues/6836. Unfortunately, the final answer from them was to raise a new idea for things like that.

“Solution”

I intentionally used quotation marks in the title and if you continue reading this post you will find why.

I am aware that the best practice is to avoid BLOB fields in general and use Media type instead. The reason why we didn’t solve this problem in the first place is that upgrading time took too long on some of our bigger clients so we’ve postponed this process. Until now.

We’ve created new Media fields, put the ObsoleteState to Removed on the BLOB ones, and created upgrade procedures to transfer data from BLOB to Media. Great, that was easy. The problem is now solved and everything should work.

Take a look at the next picture.

Wait, what? How is this even possible? BLOB fields are still included in the query creating a JOIN? Did I do everything right? Let’s see…

Example

To confirm statements from the sections above, I’ve created a small Demo App. The app consists of the two fields in the Item table extension as in the image below.

As you can see, the first one is the obsoleted BLOB field and the other one is the Media Field. I added a “Test” action on “Item List” page so I can test the SetLoadFields method in combination with obsoleted Blob field.

If you put a breakpoint on line 24 and check the long executed SQL queries you would be surprised with the result. Why? You would expect to see an SQL statement for FindFirst Item only with selecting “No.” and system fields. But wait, this is the actual SQL statement.

Note, we even obsoleted the BLOB field but still, it caused a JOIN to the extension Item table and that is the scariest part because we followed all of the best practices and still we ended up with this huge problem which we cannot avoid.

Conclusion

If you are in the Cloud, and you have only obsoleted BLOB fields somewhere in your app, then I have some bad news for you. SetLoadFields method will create additional JOIN every time you use it on the table where your BLOB fields were. If you are not in the cloud, then there are a few really ugly solutions but I hope this will be solved in some of the future releases so you will not need to use them.

In the end, the last response I got in the above-mentioned GitHub issue was to submit a new BCIdea for this kind of problem. Here it is https://experience.dynamics.com/ideas/idea/?ideaid=960c1c50-e65c-ec11-a3ee-0003ff45e08b. If you like it, vote for it! 🙂