Filters and loops

tsv
tsv Member Posts: 49
edited 2009-03-27 in NAV/Navision Classic Client
hi all,
can any one tell me whether the following code is upto standard and would increase the performance or iam wrong
am using SQL 
if Table1.find('-') then 
  repeat
    Table2.reset;
    Table2.setcurrentkey(No.,Type,Amount);
    Table2.setrange(No.,Table1.No.);
    Table2.setrange(Type,Table1.Type);
    Table2.setrange(Amount,Table1.Amount);
    if Table2.find('-') then 
      repeat
        //some code that would update another table, say table3
      until Table2.next = 0; 
  until table1.next = 0;

and can i use the setcurrentkey in loops like this?
Thanks inadvance
with regards,
tsv

Comments

  • Belias
    Belias Member Posts: 2,998
    are table1,table2 and table3 pointing to the same table?
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • tsv
    tsv Member Posts: 49
    hi Belias,
    They are pointing to different tables.
    with regards,
    tsv
  • kine
    kine Member Posts: 12,562
    1) Native DB or SQL?
    2) If SQL, you do not need the SetCurrentkey if you do not need to go through the table in some predefined order. (it will only add sorting process to the set, if you remove it, it will be sorted by primary key)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • tsv
    tsv Member Posts: 49
    Thanx for the suggestion Kamil...
    am using SQL. My primary key is "No.", along with the primary field i need to setrange for two more fields
    (type, Amount). Will it increase the performance by sorting Table1 with three fields( No., type, Amount) as given in the above code.. Table1 and 2 is having lakhs of records.
    Thanks in advance
    with regards,
    tsv
  • Belias
    Belias Member Posts: 2,998
    if you have this key "No., type, Amount" and "maintainsqlindex" property of the Key = true
    you don't need to use setcurrentkey function, unless you need to order your dataset result (as said in kine's 2nd point)
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • kine
    kine Member Posts: 12,562
    tsv wrote:
    Thanx for the suggestion Kamil...
    am using SQL. My primary key is "No.", along with the primary field i need to setrange for two more fields
    (type, Amount). Will it increase the performance by sorting Table1 with three fields( No., type, Amount) as given in the above code.. Table1 and 2 is having lakhs of records.
    Thanks in advance

    As Belias wrote, the Setcurrentkey have no effect to which key will be used (not directly), it is adding only "ORDER BY" clausule to the resulting SQL query, and SQL is sorting the result as last thing. Which key will be used is on the SQL planning system...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • tsv
    tsv Member Posts: 49
    if you have this key "No., type, Amount" and "maintainsqlindex" property of the Key = true
    you don't need to use setcurrentkey function, unless you need to order your dataset result (as said in kine's 2nd point)

    "No., type, Amount" is not my primary key...
    do i have to keep this as my primary key.
    with regards,
    tsv
  • Belias
    Belias Member Posts: 2,998
    tsv wrote:
    if you have this key "No., type, Amount" and "maintainsqlindex" property of the Key = true
    you don't need to use setcurrentkey function, unless you need to order your dataset result (as said in kine's 2nd point)

    "No., type, Amount" is not my primary key...
    do i have to keep this as my primary key.
    no, you don't...or at least, it is not compulsory that "No., type, Amount" is primary.
    but it must exist as secondary key if you want to order your data like this...(SETCURRENTKEY returns error if the key does not exist :) )
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • tsv
    tsv Member Posts: 49
    thanx Belias and kine , i understood
    with regards,
    tsv

Categories