Wednesday, April 28, 2010

Record caching principle

Which statement will perform faster?

select inventTable
    join inventItemGroup
    where inventItemGroup.ItemGroupId == inventTable.ItemGroupId
    && inventTable.ItemId == 'A';


select inventTable
    where inventTable.ItemId == 'A';

    select inventItemGroup
    where inventItemGroup.ItemGroupId == inventTable.ItemGroupId;

From the first point of view it seems obvious that the first one with join, since it will result in only one database call and the second will result in two. However, it is not always the case. For example, if records that are being looked up in the second example are cached on the server (or even on the client) there will be no database calls at all, and may be even no client\server calls! The code from the first example will never hit the cache since caching is not supported for the queries with joins.
Actually Dynamics AX caches records by their primary key value. So, if a query has no joins and has primary key in its where clause then it will hit the cache. Otherwise it won't.


  1. Thank you for some other fantastic article.
    Where else may just anybody get that type of info in
    such an ideal method of writing? I've a presentation next week, and I am on the search for such information.

    Have a look at my web blog ... waist to height ratio chart

  2. What's up, after reading this amazing paragraph i am also delighted to share my know-how here with friends.

    My site hip to waist ratio calculator


  3. Great and useful article. Creating content regularly is very tough. Your points are motivated me to move on.

    SEO Company in Chennai

  4. I didn't know about these functions and it's good to know it. Thanks a lot! I'm planning to introduce Microsoft Dynamics AX in my company, actually i'm a beginner with this software, but I will contact microsoft dynamics partner soon.

  5. Nice blogpost and thanks for sharing such useful information about Microsoft Dynamics. If you are looking for Microsoft Dynamics AX solution then you should visit atDynamics AX Partner Australia. DFSM Consulting has Microsoft Dynamics AX support, upgrade & implementation solutions for your business needs.