web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Season of Giving Solutions is Here!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / DaxGeek / While Select

While Select

Views (224)
Hossein.K Profile Picture Hossein.K 6,648
This section describes the different qualifiers and options that can be used in theselect statement, to achieve optimal database access performance.
The complete syntax for the select statement is as follows. 


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
[while] select [reverse] [firstfast]
[firstonly] [firstOnly10] [firstOnly100] [firstOnly1000]
[forupdate] [nofetch] [crosscompany]
[forcelitterals | forceplaceholders] [forcenestedloop]
[forceselectorder]
[repeatableRead] [validTimeState]
[ * | <fieldlist> from] <tablebuffer>
[ index [hint] <indexname> ]
[ group by {<field>} ]
[ order by {<field> [asc][desc]} ]
[ where <expression> ]
[ outer | exists | notexists ] join [reverse]
[ * | <fieldlist> from] <tablebuffer>
[ index <indexname> ]
[sum] [avg] [minof] [maxof] [count]
[ group by {<field>} ]
[ order by {<field> [asc][desc]} ]
[ where <expression> ]
]
<fieldlist> ::= <field> | <fieldlist> , <field>
<field> ::= fieldname | <function>(<field>)

General Optimization

To optimize general performance, the following tools and keywords may be
used.

Fieldlist

One way to optimize communication with the database is to specify which fields
are returned. For example, for a table with 40 fields, reading the information
from only four fields will reduce the amount of data sent from the database server
by up to 90 percent.

The following illustrates using a field list. 


1
2
3
4
while select amountMST from ledgerTrans
{
  amountMST += ledgerTrans.amountMST;
}

NOTE: Use this optimization with care. If the record returned from the database
is subsequently passed as a parameter to other methods, that method may have
been written on the assumption that all fields are set. Only use field lists when
controlling access to the information locally.

Best Regards,
Hossein Karimi

Comments

*This post is locked for comments