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.

11 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
  3. 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.

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Quickbooks is mostly used accounting software in the world because it is work in large & small scale business companies. If you are already used this software or facing any issue. Dial our toll free number Quickbooks Proadvisor Support Phone Number 1-800-986-4607 for fast & instant solution.

    ReplyDelete
  6. I really enjoyed while reading your article, the information you have mentioned in this post is really good. I am waiting for your upcoming post.

    Best Career in SAS Training Course with Highly package
    Want to Learn Python Programming Training with Placement Support

    ReplyDelete