Why this AppleScript don't work?

Hello Hiroto

The late version reach the end of the process but, running it 10 times in a loop requires 39 seconds.
One more time, it's the fact that it call the app row by row.

Here are two scripts achieving the same goal.

--

(* version 9 *)
on run
 set startingTime to current date
 repeat 10 times
 set reordered_values to my main(2, 45, 2, 5, 3, 5)
 end repeat
 display dialog "required : " & (current date) - startingTime & " secondes"
 reordered_values
end run
on main(row1, row2, col1, nbCols, swap1, swap2)
 script main
 property |old list| : {}
 property |new list| : {}
 property temp_list : {}
 
 set my |new list| to {}
 
 
 tell application "Numbers" to tell document "American Express 3.numbers" to tell sheet "Folha 9" to tell table "Transações"
 set my |old list| to value of cells row1 thru row2 of columns col1 thru (col1 + nbCols - 1)
 end tell
 
 repeat with i from 1 to (row2 + 1 - row1)
 set my temp_list to {}
 repeat with c from 1 to nbCols
 copy {item i of item 1 of my |old list|, item i of item 2 of my |old list|, item i of item 3 of my |old list|, item i of item 4 of my |old list|, item i of item 5 of my |old list|} to end of my temp_list
 end repeat
 
 set temp_item to item swap2 of my temp_list
 copy item swap1 of my temp_list to item swap2 of my temp_list
 copy temp_item to item swap1 of my temp_list
 copy my temp_list to end of my |new list|
 end repeat
 return my |new list|
 end script
 run main
end main
--

--

(* version 10 *)
on run
 set startingTime to current date
 repeat 10 times
 set reordered_values to my main(2, 45, 2, 5, 3, 5)
 end repeat
 display dialog "required : " & (current date) - startingTime & " secondes"
 reordered_values
end run
on main(row1, row2, col1, nbCols, swap1, swap2)
 script main
 property |old list| : {}
 --set my |old list| to {}
 tell application "Numbers" to tell document "American Express 3.numbers" to tell sheet "Folha 9" to tell table "Transações"
 set my |old list| to value of cells col1 thru (col1 + nbCols - 1) of rows row1 thru row2
 end tell
 
 repeat with i from 1 to (row2 + 1 - row1)
 tell item i of my |old list|
 set temp_item to item swap2
 copy item swap1 to item swap2
 copy temp_item to item swap1
 end tell
 end repeat
 return my |old list|
 end script
 run main
end main
--

On the same machine and the same table, both did the 10 treatments in one second.
It's due to the fact that they call Numbers only once and do the reordering job out of the app.
I feel that with a larger table, version 10 would be faster but I didn't check.

Yvan KOENIG (VALLAURIS, France) samedi 26 juin 2010 14:34:22

Hello Yvan,

Yes I know the late SCRIPT3 and SCRIPT3a are much slower than, e.g., SCRIPT2a CORRECTED, SCRIPT4 and SCRIPT4a, which get range of data in one call to Numbers.

I just wanted to show why the original code becomes unresponsive after some iterations and how to solve it by minimum fix to the offending point.

Practically we should use faster code to achieve the same goal, for it wastes less energy. 😉

Hiroto

Hello

I looked again at this exercise.

I discovered that version 9 was seriously buggued.
This loop :
--

repeat with c from 1 to nbCols
 copy {item i of item 1 of my |old list|, item i of item 2 of my |old list|, item i of item 3 of my |old list|, item i of item 4 of my |old list|, item i of item 5 of my |old list|} to end of my temp_list
end repeat
--

wasn't doing what it was supposed to do. It didn't build a list of five values named my temp_list but a list of five lists of five values.

So I edited it as version 9a1 :

--

(* version 9a1 *)
on run
 set startingTime to current date
 repeat 1000 times
 set reordered_values to my main(2, 45, 2, 5, 3, 5)
 end repeat
 display dialog "required : " & (current date) - startingTime & " secondes"
 reordered_values
end run
on main(row1, row2, col1, nbCols, swap1, swap2)
 script main
 property |old list| : {}
 property |new list| : {}
 property temp_list : {}
 
 set my |new list| to {}
 
 tell application "Numbers" to tell document "American Express 3.numbers" to tell sheet "Folha 9" to tell table "Transações"
 set my |old list| to value of cells row1 thru row2 of columns col1 thru (col1 + nbCols - 1)
 end tell
 
 repeat with i from 1 to (row2 + 1 - row1)
 set my temp_list to {}
 --repeat with c from 1 to nbCols
 copy {item i of item 1 of my |old list|, item i of item 2 of my |old list|, item i of item 3 of my |old list|, item i of item 4 of my |old list|, item i of item 5 of my |old list|} to my temp_list
 --end repeat
 
 set temp_item to item swap2 of my temp_list
 copy item swap1 of my temp_list to item swap2 of my temp_list
 copy temp_item to item swap1 of my temp_list
 
 copy my temp_list to end of my |new list|
 end repeat
 return my |new list|
 end script
 run main
end main
--

Those reading carefully will see that it no longer repead the treatment ten times but one thousand times. This way I hoped that speed differences will appear.

On my machine and my sample time.
It did the job in 37 seconds (0.37 second for 10 times)

I was not satisfied because as is, the script is limited to a range of cells embedding five columns. I wanted more flexibility so I edited it again as 9a2 :

--

(* version 9a2 *)
on run
 set startingTime to current date
 repeat 1000 times
 set reordered_values to my main(2, 45, 2, 5, 3, 5)
 end repeat
 display dialog "required : " & (current date) - startingTime & " secondes"
 reordered_values
end run
on main(row1, row2, col1, nbCols, swap1, swap2)
 script main
 property |old list| : {}
 property |new list| : {}
 property temp_list : {}
 
 set my |new list| to {}
 
 tell application "Numbers" to tell document "American Express 3.numbers" to tell sheet "Folha 9" to tell table "Transações"
 set my |old list| to value of cells row1 thru row2 of columns col1 thru (col1 + nbCols - 1)
 end tell
 
 repeat with i from 1 to (row2 + 1 - row1)
 set my temp_list to {}
 repeat with c from 1 to nbCols
 copy item i of item c of my |old list| to end of my temp_list
 end repeat
 
 set temp_item to item swap2 of my temp_list
 copy item swap1 of my temp_list to item swap2 of my temp_list
 copy temp_item to item swap1 of my temp_list
 
 copy my temp_list to end of my |new list|
 end repeat
 return my |new list|
 end script
 run main
end main
--

As you see, this time the individual row is built in a loop so, it would be easy to use it to treat a wider range.
It did the trick in 36 seconds.
As the difference of one secon may be in fact a 0.51 second one, I guess that it's meaningful.

But I have a question in head : what if the swap of two columns was made befgore building individual rows ?
This led me to version 9b1 :

--

(* version 9b1 *)
on run
 set startingTime to current date
 repeat 1000 times
 set reordered_values to my main(2, 45, 2, 5, 3, 5)
 end repeat
 display dialog "required : " & (current date) - startingTime & " secondes"
 reordered_values
end run
on main(row1, row2, col1, nbCols, swap1, swap2)
 script main
 property |old list| : {}
 property |new list| : {}
 
 set my |new list| to {}
 
 tell application "Numbers" to tell document "American Express 3.numbers" to tell sheet "Folha 9" to tell table "Transações"
 set my |old list| to value of cells row1 thru row2 of columns col1 thru (col1 + nbCols - 1)
 end tell
 
 set tempList to item swap2 of my |old list|
 copy item swap1 of my |old list| to item swap2 of |old list|
 copy tempList to item swap1 of my |old list|
 
 repeat with i from 1 to (row2 + 1 - row1)
 copy {item i of item 1 of my |old list|, item i of item 2 of my |old list|, item i of item 3 of my |old list|, item i of item 4 of my |old list|, item i of item 5 of my |old list|} to end of my |new list|
 end repeat
 return my |new list|
 
 end script
 run main
end main
--

Execution time is 37 seconds.

One more time I edited it to use a loop and got version 9b2 :

--

(* version 9b2 *)
on run
 set startingTime to current date
 repeat 1000 times
 set reordered_values to my main(2, 45, 2, 5, 3, 5)
 end repeat
 display dialog "required : " & (current date) - startingTime & " secondes"
 reordered_values
end run
on main(row1, row2, col1, nbCols, swap1, swap2)
 script main
 property |old list| : {}
 property |new list| : {}
 property temp_list : {}
 
 set my |new list| to {}
 
 tell application "Numbers" to tell document "American Express 3.numbers" to tell sheet "Folha 9" to tell table "Transações"
 set my |old list| to value of cells row1 thru row2 of columns col1 thru (col1 + nbCols - 1)
 end tell
 
 set tempList to item swap2 of my |old list|
 copy item swap1 of |old list| to item swap2 of my |old list|
 copy tempList to item swap1 of my |old list|
 
 repeat with i from 1 to (row2 + 1 - row1)
 set my temp_list to {}
 repeat with c from 1 to nbCols
 copy item i of item c of my |old list| to end of my temp_list
 end repeat
 copy my temp_list to end of my |new list|
 end repeat
 return my |new list|
 
 end script
 run main
end main
--

Execution time : 37 seconds.

At last, I ran version v10 applying the job 1000 times :

--

(* version 10 *)
on run
 set startingTime to current date
 repeat 1000 times
 set reordered_values to my main(2, 45, 2, 5, 3, 5)
 end repeat
 display dialog "required : " & (current date) - startingTime & " secondes"
 reordered_values
end run
on main(row1, row2, col1, nbCols, swap1, swap2)
 script main
 property |old list| : {}
 
 tell application "Numbers" to tell document "American Express 3.numbers" to tell sheet "Folha 9" to tell table "Transações"
 set my |old list| to value of cells col1 thru (col1 + nbCols - 1) of rows row1 thru row2
 end tell
 
 repeat with i from 1 to (row2 + 1 - row1)
 tell item i of my |old list|
 set temp_item to item swap2
 copy item swap1 to item swap2
 copy temp_item to item swap1
 end tell
 end repeat
 return my |old list|
 end script
 
 run main
end main
--

Execution time : 39 seconds.

It's a bit longer than the others but it's the one which I will keep because it's the one which is the easyest to read and to edit if the required change become more complex than a simple swap of two columns.

What is now clear, is that extracting the datas with a single call give us the ability to do the job 100 times faster than extracting them one by one.

CAUTION, we will not be able to do that for all needs. Some times we may need to check if a cell contain a value or a formula or to check the format of the cell. In such cases, working cell by cell would be required but, it's useful to know that in some cases, we may fasten the code dramatically.

Yvan KOENIG (VALLAURIS, France) lundi 28 juin 2010 15:09:00

Hello Luiz Siqueira Neto,

You're quite welcome. I'm very glad to know it helped.
And indeed I also learnt things in answering your question ! 😉

---
The following is just for preciseness.

More careful look on this 'script object and deep copy' issue as shown in previous CODE1, 2 & 3 reveals that -
a) when so1 (copied object) is global and rr (objects list) is not global, 'copy so to so1' will eventually fail with stack overflow; and
b) when rr is global, script will become unresponsive at around i = 15.

The reason would be, in each iteration -
a*) so1 (in global scope) grows in size and in recursion depth arithmetically; and
b*) rr (in global scope) grows in size exponentially and in depth arithmetically.

Thus those comments in CODE1, 2 & 3 (and some other previous posts of mine) regarding copied object' size to grow exponentially should be corrected as in CODE4a & 4b below.

All the best,
H

--CODE4a
(* script object, deep copy to global context and stack overflow *)
script so
 property p1 : 0
end script
repeat with i from 1 to 200
 try
 copy so to so1 -- [1]
 on error errs number errn
 return {i, errs, errn} -- e.g., {59, "Stack overflow.", -2706}
 end try
end repeat
return so1's p1
(*
 [1] so1 (in global scope) will grow in size and recursion depth arithmetically in each iteration
 because it will contain previous self as the global context of current script object (so).
 Consequently it will let the script to fail with stack overflow at around i = 59.
 (The recursion depth to cause stack overflow may vary depending upon run-time environment.)
 
 Explanatory note :
 Legends:
 so1(k) denotes contents of variable (so1) at end of iteration i = k;
 G(k) denotes global context of script object (so) at beginning of iteration i = k;
 G~(k) denotes G(k) - so1(k-1);
 X:Y denotes X includes Y;
 
 Then
 so1(1):G(1)
 so1(2):G(2):so1(1) => so1(2):G(2):G(1)
 so1(3):G(3):so1(2) => so1(3):G(3):G(2):G(1)
 ...
 so1(k):G(k):so1(k-1) => so1(k):G(k):G(k-1): ... :G(1)
 
 Thus 
 so1(k)'s recursion depth = k
 so1(k)'s size ≈ |G~(k)| + |G~(k-1)| + ... + |G~(2)| + |G(1)|
*)
--END OF CODE4a

--CODE4b
(* script object, deep copy to global context and memory overload *)
script so
 property p1 : 0
end script
set rr to {}
repeat with i from 1 to 14
 log i
 copy so to end of rr -- [1]
end repeat
return rr
(*
 [1] rr (in global scope) will grow in size exponentially in each iteration
 because it will contain previous rr as the global context of current script object (so).
 Consequently it will let the script become unresponsive at around i = 15.
 (Probably due to memory management overload.)
 Explanatory note :
 Legends :
 rr(k) denotes contents of list (rr) at end of iteration i = k;
 G(k) denotes global context of script object (so) at beginning of iteration i = k;
 G~(k) denotes G(k) - rr(k-1);
 X:Y denotes X includes Y;
 
 Then
 rr(1):G(1)
 rr(2):(rr(1) + G(2):rr(1))
 rr(3):(rr(2) + G(3):rr(2))
 ...
 rr(k):(rr(k-1) + G(k):rr(k-1))
 
 Thus 
 rr(k)'s recursion depth = k
 rr(k)'s size ≈ 2 ^ (k-1) * |rr(1)| + 2 ^ (k-2) * |G~(2)| + 2 ^ (k-3) * |G~(3)| + ... + |G~(k)|
*)
--END OF CODE4b

Message was edited by: Hiroto