Tuesday, October 27, 2015

When fields lists don't work as expected

Did you know that a field list in a single-record select-statement is not always beneficial in comparison to a select firstonly * from...  (or a static find-method)?

The thing is, when a where-clause matches a unique index (in AX 2009 – a primary index), the AOS may find the complete record in the cache and return it. And if this is the very first call, it will pull the whole record anyway, in order to be able to cache it. This may be proved by debugging.

So please use find-methods where possible, because they are easier to read and maintain. And remember: a while select with a nested find-method call may actually perform better than a join – thanks to caching. Therefore do measure performance to confirm that your select-statement is the most optimal one.

No comments:

Post a Comment