axStart: Table caching in AX

[Blog bot]

Источник: http://axstart.spaces.live.com/Blog/...C0A0!371.entry
==============


There is a lot of misunderstanding about caching of data in AX.
Axapta 3.0 and 4.0 only caches data if you search exactly with the values of the primary index. I will show you with the next example. The primary index on the CustTable is on the field AccountNum.
Example 1 will place the record in my cache. The second example will read from cache. Example 3 and 4 will not do it.
static void cachingTest(Args _args)
{
    CustTable custTable;
    CustTrans custTrans;
 
    //example 1*
    select custTable where custTable.AccountNum == "4001";
    info(strfmt("%1",custTable.wasCached())); //not cached
 
    //example 2
    select custTable where custTable.AccountNum == "4001";
    info(strfmt("%1",custTable.wasCached())); //cached
 
   //example 3
    select custTable where custTable.AccountNum == "4001" &&
                                              custTable.Name == "The Glass Bulb";
    info(strfmt("%1",custTable.wasCached())); //not cached
 
    //example 4
    select custTable where custTable.AccountNum == "4001" join
                custTrans where custTable.AccountNum == custTrans.AccountNum;
    info(strfmt("%1",custTable.wasCached())); //not cached
}
* Incase the records was already cached on the AOS cache status is SvrRecordCached
In AX 2009 the caching mechanism has improved. Any unique index will be used for caching optimizing. So let’s repeat the test in AX2009. In the next example (2009), we search 2 times for the same record. The first time we use index PartyID that places the record in the cache the second time we search by accounnum index and will find the result in the cache.
static void cachingTest(Args _args)
{
    CustTable custTable;
    CustTrans custTrans;
 
    //example 1*
    select custTable where custTable. PartyId== "215"; //PartyID is also has also an unique index
    info(strfmt("%1",custTable.wasCached())); //not cached
 
    //example 2
    select custTable where custTable.AccountNum == "4001"; //same record like partyID == “215”
    info(strfmt("%1",custTable.wasCached())); //cached
 
   //example 3
    select custTable where custTable.AccountNum == "4001" &&
                                              custTable.Name == "The Glass Bulb";
    info(strfmt("%1",custTable.wasCached())); //not cached
 
    //example 4
    select custTable where custTable.AccountNum == "4001" join
                custTrans where custTable.AccountNum == custTrans.AccountNum;
    info(strfmt("%1",custTable.wasCached())); //not cached
}
* Incase the records was already cached on the AOS cache status is SvrRecordCached
NOTE: Still there is a primary index property in the AOT, I don’t know why.
 


Источник: http://axstart.spaces.live.com/Blog/...C0A0!371.entry

[gl00mie]

Цитата:

Сообщение от Blog bot

NOTE: Still there is a primary index property in the AOT, I don’t know why.

Рискну предположить: потому что СУБД совершенно фиолетовы улучшения в механизме кэширования в DAX 2009, и она (СУБД) по-прежнему различает UNIQUE и PRIMARY KEY constraints.

[axstart]

Hi gloomie,
can you provide me some more invormation about your replay, google translate tels me something about primary key constraint, But to my opinion SQL has no primary thing at all..

dick

[gl00mie]

Цитата:

Сообщение от axstart

can you provide me some more invormation about your replay

Well, the idea is that a DBMS (Ms SQL Server or Oracle DS) doesn't care about DAX caching improvemets - it still distinguishes between PRIMARY KEY (which can be set from MorphX via a corresponding table property) and UNIQUE constraints (which can be set from MorphX via a table index property). Of course if we take into account that DAX neither uses itself (except for RecVersion field value since AX 3 KR3) nor allows to use from X++ NULL table field values, then PRIMARY KEY and UNIQUE constraints differences merely vanish. But still there can be scenarios when you use some tables in a DAX database for a data exchange with a foreign system and enforce a FOREIGN KEY constraint on some other tables involved in the exchange. Obviousely you'll need a primary key for this as none of unique keys will be suitable for such a scenario. And it would be handy to declare a primary key on your DAX table from MorphX rather then altering it from a DBMS side and dealing with AOT Data Dictionary synchronization issues when DAX simply drops all indexes/constraints that are not declared in AOT.

Цитата:

Сообщение от axstart

to my opinion SQL has no primary thing at all...

It seems this statement is not quite correct.

PS. There's a good book «Expert one-on-one Oracle» by Tom Kyte where he's stressing one idea: don't use a DBMS as a "black box"; if your application relies on a database - learn the DBMS you're using otherwise your project is most likely doomed to be non-scaling application with not more then several concurrent users. DAX is the system that relies on a database so even if DAX moves from ternary SQL logic (value matched - value not matched - value is NULL) to a binary logic (value matched - value not matched) which makes PRIMARY KEY and UNIQUE constrains not so different, you still can't just ignore the underlying DBMS that doesn't care about assumptions and simplifications introduced in your system.