While statement in Axapta 2012

Use of while statement in Axapta

To access database

Syntax

     [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>)


How to increase performance on database access


1. Select required fields only which you would like to work with,
     instaead of all 20 fields,
     select requird fields like 4 fields only,
     which will increase performance by 50-90%


select sum(qty) from xyz;
select count(recId) from xyz;
select maxof()....
select minof()....

2. do not use while loop with in while loop, instead use join

    while select recId from inventTable
                    join qty from inventTrans
    where inventTrans.itemId == inventTable.itemId
    {
          Mqty += inventTrans.qty;
     }
   
3. Use ForceLiteral - Processor utilised very high

     Miscellaneous
     Main
     Transaction
     Worksheet Header
     Worksheet Line

     while select forceliterals InventTrans order by itemid

ForcePlaceholders
    ForcePlaceholders instructs the kernel not to reveal the actual values used in
where clauses to database server, kernal can reuse plan of another similler statement

while select forcePlaceholders salesLine
                                           join salesTable
where salesTable.SalesId == salesLine.SalesId

FirstFast
    It is prioritize fetching the first few rows only

FirstOnly
    It select first row only from database depends on whre statement
           Select firstonly inventTable
                         where InventTable.itemId = _ItemId;

Index / Index Hint
          To force of use defined index on table, incorrect order of index effect performace

ForceSelectOrder
          To forces the database server to access the tables in a join in the given
order

      while select inventTrans
                 index hint ItemIdx
       where inventTrans.ItemId == 'X'
           join inventDim
      where inventDim.inventDimId == inventTrans.inventDimId &&
              inventDim.inventBatchId == 'Y'


If indexes in a join, do specify forceSelectOrder
      while select forceSelectOrder inventTrans
                index hint ItemIdx
       where inventTrans.ItemId == 'X'
           join inventDim
      where  inventDim.inventDimId == inventTrans.inventDimId   &&
               inventDim.inventBatchId == 'Y'

ForceNestedLoops
      first table is fetched before trying to fetch any records from the second table

      while select forceSelectOrder forceNestedLoop
           inventTrans
           index hint ItemIdx
      where inventTrans.ItemId == 'X'
          join inventDim
     where inventDim.inventDimId == inventTrans.inventDimId  &&
              inventDim.inventBatchId == 'Y'

Cross Company
      If you would like to fetch data of current company with another DataareaId also, use cross Company, below query return records from all the company

      select crossCompany count(recId) from SalesTable;