A few days ago, I got pointed to this question on LinkedIn: https://www.linkedin.com/posts/alexander-drogin-0635422b_hey-business-central-developers-im-looking-activity-7231695259485839362-n670?utm_source=share&utm_medium=member_desktop
Or in other words: Is it a good practice to first test of a set is empty, to then get the set of records?
I had a few thoughts:
First, I was like: duh. Stupid question. 2 SQL roundtrips in stead of 1 is always to be avoided. So just do only FindSet.
Second, I realized: You’re right! I have come across this as well, multiple times. People actually do this.
Then, I remembered the unexplained phenomenon on the JIT loading in terms of partial records, where JIT loading seems to consistently perform faster, than a full FindSet without SetLoadFields (so, no partial records).
Or in other words: 2 statements can be faster than 1.
Say what?
Ok, you don’t remember? Well, here, in my session on BCTechDays, I explained some more on how I test “Partial Records”:
I might not have specifically addressed the JIT Load during the session (I simply forgot 🙈), but look at this:
The JIT Load was faster than one individual partial FindSet on the same table.
The JIT Load basically look liked this:
You can see in the loop I’m using a field that was not loaded in SetLoadFields. You’ll see SQL Server performing a full SELECT (all columns, all joins to all companion tables) – and still – it performed faster.
I have no explanation. But in any case – that has been very consistent throughout the versions.
Like today, I tested the very same code on a 24.3 sandbox in the cloud
This picture not only proves the clear and obvious performance gain since v23 thanks to the merged companion tables – but also still the same phenomenon on the JIT Load.
Still don’t believe me?
From the same data, you’ll see that the reads are all the same:
And the JIT Load indeed causes extra SQL Statements:
OK .. but what does this have to do with the current topic?
Well – it made me think – may be people ARE doing the IsEmpty + FindSet for a good reason, and I’m wrong. May be IsEmpty + FindSet is better than *just* FindSet, like it seems to be with JIT Loading.
So – I wanted to check…
The Testcase
I must say – it was easier said than done to come up with meaningful durations. Most of my cases had 1 thru 4 milliseconds – even for sets of 500k records. So, I just decided to read multiple tables, to amplify the impact a bit.
The first function, I pretty much read 5 tables, first with IsEmpty, second the FindSet, with a weird filter: No magic at all:
In the second function, I took the “IsEmpty” out:
In a third, I would only execute the IsEmpty .. just because I could:
And I must say – the results were not that surprising this time :-). Completely expected behavior.
As expected, the extra IsEmpty causes more SQL Statements:
And it takes longer (also as expected):
Conclusion
If you’re an IsEmpty-before-FindSet-kind-of-guy/girl: please stop it, or step away from your keyboard 😜.
Loading ...
5 comments
Skip to comment form
Interesting…
Note that IsEmpty also forces a table lock.
Therefore, use it together with ReadIsolation.
IsEmpty is still good to use before a DeleteAll call. More SQL calls but an empty delete all has a very negative performance impact.
Just one thing came to mind – your results are only valid if you have data in the table every time. If say 50% of the time IsEmpty returned true then you could skip the FindSet 50% of the time which would mean that the IsEmpty + FindSet combination would be better than just FindSet overall
Author
I don’t agree, sorry.
first of all: What does it mean “50% the table is empty”? How can a table be empty half of the time? What kind of table is that?
Second: I’m talking guidelines here. I’d rather gamble for the “other” 50% to not go to the “big” table twice, than the incredible limited time that is gained by doing an IsEmpty first.. 🤷♂️.
Just my 2 cents..
I should have said “50% of the time the result set is empty” – for example you may be applying a filter on the “Reservation Entry” table and this would usually be with specific filters. Now although the table is rarely ever empty, the result set after filtering for a specific related record may quite often be empty (e.g. you want to loop through all Reservation Entry records for each Sales Line on a Sales Order). If only 5 out of 10 of the sales lines on an order typically have reservations then 50% of the time the result set is empty.
I agree there is no hard and fast rule, I was just saying that there may be cases where you don’t expect to have a non-empty result set most of the time and if this is the case then having an empty before a findset may be beneficial.
If you do normally expect a non-empty result set then I agree there is no point having an IsEmpty before the FindSet
P.S. my original post may have come across a bit harsh, I didn’t mean that your blog/result were incorrect, just that it really depend on whether you normally expect a (non-empty) result set or not