Filters and loops

tsv

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

Belias

are table1,table2 and table3 pointing to the same table?

tsv

hi Belias,
They are pointing to different tables.

kine

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)

tsv

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

Belias

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)

kine

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...

tsv

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.

Belias

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 )

tsv

thanx Belias and kine , i understood