fetch cursor bug for sql-option

robikom

symptoms:
Navision works with ms-SQL database,
"Cust. Ledger Entry" table includes 3 mln records,
we post bank statement,
"Gen. Journal Line" table includes 3000 lies,
each line applies a few thousand entries.


During the time of cust.entries appliement, navision suddenly stops working
by calling "OldCustLedgEntry.next"(CU12, function "ApplyCustLedgEntry").
It takes a few minutes!!!
And during this time MSSQL server is processing (cursor fetching).

What is more interesting, the function "ApplyCustLedgEntry" includes more
then one loop at the same cursor:

(1 loop)
....
REPEAT
....
UNTIL OldCustLedgEntry.next=0;
....

(n loop)
....
REPEAT
....
UNTIL OldCustLedgEntry.next=0; -> MSSQL server stop working
....


This problem appears with the big table (ap. 2 mln records)
Probably when the filter is modified inside the loop.


We rebuilt the function by using temporary record ("OldCustLedgEntry")
and we get a higher efficiency:

standard function "ApplyCustLedgEntry" - 6 entries per 1 minute.
modified function "ApplyCustLedgEntry" - 4000 entries per 1 minute.


Who can explain such a big difference?

Sorcerer

Did you use a temporary record or a new local variable of the record?

efpmari

We are currently having the same problem. Could give us a hint of how you solved this? Does anyone why this is a problem in the first place?

bylle

Hi!

I have changed the function (as suggested) to use an alternative variable (local var) to handle the repeats - and thereby the filter is not modified inside the loop. Changed some keys which needed to be optimized. (found them with the client monitor)

Well all that did improve the performance (it went from taking about 6 sec per entry to 0 - 16 ms. per entry - for must of the entries) - but there are still some entries which is really slow... they take about 10 - 20 secs (for just one entry). With the client monitor i could see that the sql server is making an index.seek. Does anyone have an idea why? The Client Monitor says that it is the statement Complete := OldCustLedgEntry.NEXT=0; and that a NEXT caused a SELECT statement.

robikom - Is it possible to get an example of your code?

Has anyone else done these changes? Is it nessesary to change all the repeats? Or is it enough to only change the last one where the statement Complete := OldCustLedgEntry.NEXT=0 exists?

/Bylle

wheresjustin

Hi,

Does this issue still exist in V4?

Could someone please post their solution to this issue.


Cheers,
Justin

gra8ful10

We are having the same problem. Could someone please post their solution for this issue? Thank You.

ovicash

gra8ful10 wrote:

We are having the same problem. Could someone please post their solution for this issue? Thank You.

Has someone else tried this?

Thank you

DeSp

Try this hack:
http://www.mibuso.com/forum/viewtopic.p ... highlight=

pummi

Hi,

Has Anyone Solved this problem of slowwww application of customer payments. It is just crawling

Param