Microsoft Dynamics Ax developer's blog

Tuesday, July 31, 2007

Unwanted fields fetching when selecting by unique index

UPD: Sorry, EmplTable is not good example - because of cache enabled. But this behavoiur is tested with a specially created table with CacheLookup==None.

Last friday i was optimizing a form with a lof of display fields with an implementation like the following:

display EmplName emplName()
{
return EmplTable::find(this.EmplID).Name;
}

The one of sources to increase performance of such code is to select only fields which will be used lately. So I have replaced this code with the following:

display EmplName emplName()
{
return this.EmplID ?
(select Name from EmplTable
where EmplTable.emplID==this.EmplID).Name
:
"";
}

but when I enabled SQL tracing, I found, that this code producess select with ALL fields of EmplTable.
It was strange... With a great help of my co worker, i've found the following:
  • when there is only fields of any unique index in the where condition, all fields are fetched
  • when there is an extra field in the condition, only required fields is selected (and the extra fields in where)
  • So I converted previously mentioned methods to the following:


display EmplName emplName()
{
return this.EmplID ?
(select Name from EmplTable
where EmplTable.emplID==this.EmplID
&&
EmplTable.recID
).Name
:
"";
}

1 comment:

MichaƂ Kupczyk said...

Usually you use field list on select statement to improve performance and possibly use Covering Indexes feature.
Assuming the table has 'not in TTS' or stronger caching scheme, I would expect if application want to get a field which does not belong to any index, forcing db engine to reach row's data (contents of the indexes is not enough), it's certainly worth to fetch ALL fields to be able to put entire row into AX cache (for a cost of ~105% of original time).

The behaviour you are describing is buggy, and it should be other way round: If your field list and where clause contain only fields from index AX should follow your code and prepare 'small' statement. But if you include fields outside of index then IMHO it's more worth to get entire row if DB has read that data anyway.