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 ...
7 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
Then, only for joke, why Microsoft introduced IsEmpty instruction if it is so dummy? And only by example why they use it inside CU 80? If you are right, probably Microsoft need to fire someone on language developer team and some people on application team.
Author
Well – there are more usecases than “before FindSet”, no? 😉