A Short discussion on table-cache property

Hi there!

Its been a long time since I have updated this web site. The reason is that I have been working my xxxx off on version 4.0, now that we are about to get into the testing phase I have some free time to look around. During this period I have almost completed a project that allows parallel scheduling on machines but god knows when am I going to get into that mood and finish off the project. I have also been very busy with some personal matters.  I have decided to keep myself informed about the latest in the stem cell research and its a tough and time-consuming job.

Anyways...

The other day I was investigating some performance related thingys of master planning and I discovered something funny. The database that I was testing had about 200 warehouses and about 5000 items. I discovered that Axapta was going into the database quite often to get the invent location record. This is quite weird  since the cache-property of inventLocation is set to 'found'. So there is no reason for this behavior after Axapta selects the 200 locations once. 

What is happening is

When the table cache property is set to 'found', there is an upper limit on the size of the records per table. Once the selected records reach this upper limit, FIFO rule is applied i.e. the oldest selected record is discarded and the latest record is kept into the memory. This means for e.g. in master planning you are continuously looping across same records, the 'found' property does not give you any benefit, look at it this way. You are selecting records from A to Z in a loop and you are doing it twice. By the time you reach say record L, record A is discarded. When you go over to record M, record B is discarded. Needless to say that once the logic reaches A again, it has to fetch it from the database again. This means you are not going to get performance benefit from 'found' property. In such scenarios it may be better to keep a copy of the inventLocation records into memory using a map or record sorted list.

Incidentally something else about cache property 'complete table'. Even over here there is an upper limit on the size of the records. Once the records reach this upper limit a temporary table is created on the disc. So if you set the cache to 'complete table'  for a table with huge number of records then remember that now this table is accessed from disk and not directly from memory, which means there is still some overhead. Of course fetching the record from the disk is still better than getting it from the database.  

Now your question is what is this upper limit? Well, unfortunately I have forgotten this limit but do not worry, official MBS guide to behind the scenes Axapta will be in market in the next 5-6 months and this book will answer all your questions.

Send mail to harry@systomatics.com with questions or comments about this web site.

Disclaimer: I am working with Microsoft Business Solutions. The code on this site may or may not be related to my official duties with Microsoft. I do not claim in any expertise in modules represented on this website. Essentially there is just one person doing functional specifications (in head), design specifications (in head), coding and some testing. There is no way the project on this site will be free of bugs. The projects are intended as guidelines and may god help you if you decide to implement the projects without making any changes. If you implement any project resulting into data corruption or anything like that then do not even think of suing me because a. I have already warned you and b. I don't have any money. I may or may not respond to your emails about supporting the project. I may or may not upgrade the projects to the next service pack / version.