Wednesday, April 28, 2010

Record caching principle

Which statement will perform faster?

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


or

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.

2 comments:

  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

    ReplyDelete
  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

    ReplyDelete