Many years ago, for the Italian localization, I developed a complex report that should read many times the Customer Ledger Entry table and combine some entries with some other ones with different criteria. In order to do that, I used the Mark function to select the entries to be combined with the others.
Well, some years later, one of my first work as a freelance developer, was to correct this report for a partner having performance problems with a customer having a lot of records in that table. I payed my original choice!
Today, I'd never use the Mark function, and I'd like to explain what's the point about this.
The instruction
First of all, as we all know, there's no way to apply an OR condition on two different fields of the same table in AL by using SETRANGE or SETFILTER instructions. I mean, suppose you want to convert this SQL statement into one statement in AL
select * from "Sales Invoice Line"
where
(field1 = value1) or (field2 = value2)Instruction Mark has been introduced for this reason. So the SQL statement above can be translated in this way:
Before the first loop you apply the first condition, then inside the loop you mark every record you find; after this, you reset the first filter (you cannot use Reset), apply the second condition, and inside the second loop you mark the records; finally, you remove the last filter, apply the MarkedOnly(true) instruction and then you loop over the union.
As you can see in the image, looping on records with MarkedOnly(true) means to make a third query in SQL with a condition applied on the primary key of all the records you read in the two loops.
Now, suppose to find 10.000 record in the first loop and 20.000 in the second loop. The third loop will be on 30.000 records (supposing not to have overlapping records) with a SQL instruction made of 30.000 "where" conditions.
Suppose finally that the primary key is made by two or more fields (like Sales Line table, for example) and you can imagine how this procedure is poor from the performance point of view.
Using the temporary table
So now that we know that using Mark instruction is not a good solution, we can think about an alternative by using temporary tables.
As you can see, the procedure is similar for the first two loops (between the two in this case you could apply a Reset), but the main difference is that the third loop is not converted into a normal SQL query, simply because you are looping in memory and not on a real table.
So with this solution you have two queries and not three.
You can test the difference between the two solutions by using the SQL Profiler. It's quite impressive!
Conclusions
After by bad experience in using Mark and MarkedOnly, I stopped to use them. I can summarize this in two points:
Never use Mark and MarkedOnly
Never make a performance test on Cronus company
Well, the last point seems stupid, but performance problems arise on large databases and when you have many records to read. For this reason some years ago I developed a Codeunit that creates and posts more than 500.000 sales invoices in a database, in order to have a big database on which to do my tests.
Comments