More about ISEMPTY

I had much second thought about whether I should even blog this one. You know, it so profoundly belongs in the Duh! category that I have actually created the Duh! category specifically for this post.

Now, fasten your seatbelt. I apologize for what you are about to see,  but apparently people do write code as the example below. And I sh*t you not, you can’t really make this crap up, but this is what people do:

IF NOT Cust.ISEMPTY THEN
  REPEAT
    DoSomethingStupid(Cust);
  UNTIL Cust.NEXT = 0;

Wat?

Let’s start with a disclaimer. As unbelievable as it may seem, code with along the lines of the “pattern” above did find its way into runtime, so I have to state this as directly as I possibly can: it is stupid. If you can’t see why it is stupid right away, then read about ISEMPTY first, and then read on.

As I said, this belongs to the Duh! category, and if you think so too, then there is no need to reed further, you won’t learn anything new.

I owe you a little background now. I would have never ever even thought of writing this post, not in my craziest dreams because I could never ever even have imagined that someone could do something as, well, sorry again, but something as stupid as this. But right after Waldo’s, Gary’s and my NAV TechDays 2016 session on Bad Habits of NAV Developers, a good friend of mine, who happens to be an escalation engineer with NAV support team (and those folks get to see more code than you and I will ever read, squared), approached me and told me: you should be careful with ISEMPTY!

“In what ways exactly should I be careful with ISEMPTY?” I said in disbelief.

And then she told me.

She told me that I couldn’t believe how many people complained to support about problems that arise from using ISEMPTY along the lines of the “pattern” from the beginning of this post. And she was dead serious.

So, this is why this post is here, and if you are still here with me, reading, in disbelief or otherwise, I hope you now agree with me that I simply had to write it.

And since I politely asked all the Duh! folks to not read further, if you are still here, it means that you are waiting for explanation why the example above was so incredibly stupid that it made me sleep an hour less just to be able to write this. So, here we go.

First of all, as you know now, ISEMPTY doesn’t really retrieve anything from the database. There is a universe-size difference between what Cust variable contains after this:

IF NOT Cust.ISEMPTY THEN

… and after this:

IF NOT Cust.FINDFIRST THEN

I hope you understand that. If not, then go back to my ISEMPTY post again and REPEAT reading UNTIL clear.

I believe that “I have heard that I should use ISEMPTY instead of FINDFIRST” postulate comes from “you should use ISEMPTY instead of FINDFIRST when you want to check if there are records there”. But people not always listen throughout, so the first part sticks, the second doesn’t.

Yes, people not only do IF COUNT = 0 to check if there are records there. They sometimes do IF FIND(‘-‘) instead (because they think that this still behaves as it used to in year <2005 and back). <sarcasm>Smarter</sarcasm> people do IF FINDFIRST instead of FIND(‘-‘) to check for presence of rows because they learned at some point during the past twelve years that FINDFIRST is around and that it performs faster in SQL because it retrieves only the first record. And then there is an even <sarcasm>smarter</sarcasm> group of people who know that ISEMPTY performs even faster than FINDFIRST.

That’s what happens when you are more zealous about performance than about learning the tools you use.

You see, the biggest problem in the IF NOT ISEMPTY then REPEAT UNTIL NEXT syntax is not that when encountering this NEXT the NST runtime inevitably goes like “Um, NEXT what?” but it’s something else. It’s the fact that there are legitimate situations when you not only need to check if there is data in a table, but if there is you need to do something with the first row, and only the first row that might be there.

In such situations, this is entirely legitimate:

IF NOT Cust.FINDFIRST THEN
  DoSomething(Cust);

 

… while this is entirely not:

IF NOT Cust.ISEMPTY THEN
  DoSomething(Cust);

 

Again, ISEMPTY is not a data retrieval operation, after ISEMPTY completes, your record variable context does not change in any conceivable way. If it’s your lucky day, then this is what you have:

// Cust is uninitialized at this point
IF NOT
Cust.ISEMPTY THEN
  DoSomething(Cust);

However, if you are a member of all-variables-must-be-global religion, then you are more than likely to be in this situation:

// Cust was retrieved by some random function
// three years ago, and it contains some context

IF NOT
Cust.ISEMPTY THEN
  DoSomething(Cust);

If you were unlucky that your Cust variable actually contained context (in other words, it was positioned on an existing record, or contained information about a possible new or existing record) then depending on what exactly DoSomething does, you may get very unexpected, and very, very undesired results.

Yesterday I had a nice short comment exchange in my COUNT=1 post about whether I was wrong stating that FIND(‘-‘) retrieves the subset of records at first and then the remainder of records after NEXT takes it beyond the last retrieved, or FINDSET does so.

I was not wrong about it, but thinking about why Microsoft changed the behavior in NAV 2013, after years and versions of having it the opposite way around, after having been confronted with this ISEMPTY-as-a-FIND axiom I came to believe that perhaps Microsoft switched it around to counter for bad performance that people who only wanted to check if there are rows in the table by still writing IF FIND(‘-‘) THEN as they happily did since 1996 or so, incur by writing IF NOT FIND(‘-‘) THEN EXIT.

Between us – I know that this is not why they changed the behavior – but after having been confronted now with the same ISEMPTY-as-a-FIND axiom yourself, you must agree with me that for all those who used to do IF FIND(‘-‘) to check for presence of data this was one big fat lucky coincidence.

Here endeth the lesson.

Vjeko

Vjeko has been writing code for living since 1995, and he has shared his knowledge and experience in presentations, articles, blogs, and elsewhere since 2002. Hopelessly curious, passionate about technology, avid language learner no matter human or computer.

This Post Has 7 Comments

  1. Christian Clausen

    Nice blog series, Vjeko! As a sequel, maybe you could blog about best (and worst) practices of updating large data sets while looping over them, and deleting records you loop over. These are quite common scenarios and can cause very bad performance.

    1. Vjeko

      Hi Christian! Good to have you here 🙂 Thanks for the comment. Now that I have got one topic off the list, you immediately put three more to it 🙂 Sure, I’ll queue it up and blog about it when I get time. Great suggestion!

  2. Thomas

    I often see code like IF NOT ISEMPTY THEN FINDSET or IF NOT ISEMPTY THEN DELETEALL. In the case that a table does not contain any records, the usage of ISEMPTY is supposed to be faster than FINDSET or DELETEALL, which sounds reasonable to me.

    But sometimes I also see code like IF NOT ISEMPTY THEN FINDFIRST. If I understand the way the SQL Server works correctly, there should hardly be a difference in performance between ISEMPTY and FINDFIRST. Is it really useful to use IF NOT ISEMPTY THEN FINDFIRST or should IF FINDFIRST be used instead?

  3. David Baldwin

    Hi. We recently stumbled on a strange behavior of ISEMPTY that we suspect has to do with how ISEMPTY works behind the scenes, but we can only speculate. To wit:

    Say ISEMPTY on a given recordset returns TRUE.
    Now put a Security Filter on one field in that record; don’t change any data.
    ISEMPTY now returns FALSE. FINDFIRST on the same security-filtered record will return no records.
    “MESSAGE(‘Count is: %1’, <recordset..COUNT) will give a count of zero if run immediately after the ISEMPTY test which returns false.

    Something is going on that we cannot see, yes? We changed the SecurityFilter property on the record variable to Ignore, and our code worked. Safe to do so in this instance.

    But, what is going on??

    1. Vjeko

      Hm… from how you describe it, I’d say it’s a bug. Did you try contacting Microsoft about this?

Leave a Reply