Monday, August 27, 2007

Caching and Indexes

Well, today I have to trouble shoot a weird problem for my customer (Ax30SP5). The problem is like this:

A LedgerTable consists of 822 records. Using the following select statements (in a job) yield different results.

a) While select ledgerTable
// work...
[This returns 822 records]

b) While select ledgerTable
where ledgerTable.accountNum >= [something] &&
ledgerTable.accountNum <= [something]
// work...
[This would only returns 278 records]

The interesting aspect of this problem is it only fails in a certain company. Yet the data in ledgerTable is the same as in other companies. Also, there's no problem if I use other fields in the where clause.

After playing around with parameters and settings I've figured:
i) If I change the caching from Entire Table cache to another type of cache, the problem's gone but this impact the performance of the system. [The problem comes back if I change it back to Entire Table cache]
ii) I can change the "Primary index" to another index to solve the problem. [The problem actually disappear even after I change the primary index back to its original one]

During the debug process I read through some articles and other resources. While I have no logical explanation on this, it seems that there's a slight chance the client side record caching would fail. Which in turn leads to problem in record selection related to primary key. [For record cache populates itself with primary key]

P.S. Anyone who has a better idea of what's happening please kindly share it with me. Thanks in advance.