IsEmpty + FindSet or just FindSet?

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 😜.