Bug in ApplyItemLedgEntry due to SQL optimizer

Eugene
Eugene Member Posts: 309
edited 2007-07-10 in SQL Performance
If in Item Journal Line you have several lines the lines after first may be not applied when they shoudl. This happens because the 
CASE EntryFindMethod OF
          '-':
            IF ItemLedgEntry2.NEXT = 0 THEN
              EXIT;
          '+':
            IF ItemLedgEntry2.NEXT(-1) = 0 THEN
              EXIT;
        END;
in the ApplyItemLedgEntry function (codeunit 22) skips some open records due to SQL optimizer

I wonder if MS is going to fix it ?

Comments

  • ara3n
    ara3n Member Posts: 9,258
    I am not clear on your description of the problem. Replicate it in cronus company, and send it to MS. I'm sure they will look at it. If they don't know about it, they won't fix it, given that it actually is a bug.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • stryk
    stryk Member Posts: 645
    ... due to SQL optimizer ...
    What do you mean by "SQL Optimizer"?

    And - "just shooting into the dark":
    If due to a NEXT statement records are "skipped", this usually means that you have changed the value of a field which is used in the current sorting (SETCURRENTKEY) ... you should check this ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • Eugene
    Eugene Member Posts: 309
    Yes under MSSQL some records may get skipped whereas under native database it does not happen.
  • stryk
    stryk Member Posts: 645
    Hmmm, I'm still wondering what you mean by "SQL Optimizer".

    If you experience a difference with recordsets & sortings between native and SQL you have to be aware, that there is a difference in sorting CODE fields:
    While in "native" CODE values are sorted in a numeric way 1,2,3,4,5,6,7,8,9,10,etc. in SQL Server this is alphabetic: 1,10,11,12,13,14,15,2,20,etc.

    My guess is, that when you start your "ItemLedgerEntry2" loop you do a SETCURRENTKEY on a CODE-type field which is changed within the loop ... this could cause the trouble you experience ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool

Categories