topic Re: Why is Tables/Concatenate so slow? in Discussions

Why is Tables/Concatenate so slow?

hogi — Tue, 05 Nov 2024 20:56:54 GMT

Has this ever happened to you?
Sometimes it takes a very long time for Tables/Concatenate to finish merging two tables.

More than a minute for just a few columns and some 100k rows.

What could be the reason?

Re: Why is Tables/Concatenate so slow?

hogi — Tue, 05 Nov 2024 21:11:28 GMT

I was wondering if it's because of the character columns - or because of the new compact columns (to compute the logic in the background).
Is it much faster to merge 2x the same table - instead of merging to "different" tables?

no - no - no

dt1 = new table("test",<< add rows(500000));
for(I=1, i<=20, i++,
dt1 << new column ("x", Character, set each value(Hex(random Integer(1000000))));
);
for(I=1, i<=20, i++,
dt1 << new column ("y", Character,Compact set each value(Hex(random Integer(1000))));
);

dt2 = dt1 << Subset( All rows, Selected columns only( 0 ) );

t0= hptime();
dt1 << Concatenate(	dt1);

time=(hptime()-t0)/1000000;
print("concat same file: ", time);

t0= hptime();
dt1 << Concatenate( dt2);

time=(hptime()-t0)/1000000;
print("concat 2 files: ", time);

Re: Why is Tables/Concatenate so slow?

hogi — Tue, 05 Nov 2024 22:32:32 GMT

You may have noticed the FILES "scripts" - they look quite innocent:

but concerning speed, they can be devastating:

dt1 = new table("test",<< add rows(1), new column ("x",  set each value(1)));

t0=hptime();

Nmax=200;

for(i=1, i<100,i++,
filesN = As list(Index(1,Nmax)`);
filelist = transform each({i},filesN,Eval List({"myfile"||Char(i)||".txt", 1, 1}));
Eval (Eval Expr(
dt1 << New Table Script(	"Files",Expr(filelist)	)
)));
time=(hptime()-t0)/1000000;
print("add files info: ", time);

dt2 = dt1 << Subset( All rows, Selected columns only( 0 ) );

t0= hptime();
dt1 << Concatenate( dt2);

time=(hptime()-t0)/1000000;
print("concat 2 files: ", time);

Re: Why is Tables/Concatenate so slow?

hogi — Sat, 09 Nov 2024 17:12:11 GMT

So from time to time, either delete the Files scripts - or merge them into one script *)

This will speed up the Concatenate step by orders of magnitude.

Even more important: if you trigger the Update manually:
With the new Preview Tab, the strange "processing" has to be done EVERY time again and again:
1) when the Concatenate menu opens

2) when tables are added via Add

3) after clicking on OK

This video illustrates the problem - to make it not extremely boring, I sped it up by reducing the for loop to 50:

(view in My Videos)

*) almost surprising: the same content in the table scripts - but orders of magnitude faster:

dt1 = new table("test",<< add rows(1), new column ("x",  set each value(1)));

t0=hptime();

Nmax=200;

for(i=1, i<100,i++,
filesN = As list(Index(1,Nmax)`);
filelist = transform each({i},filesN,Eval List({"myfile"||Char(i)||".txt", 1, 1}));
Eval (Eval Expr(
dt1 << New Table Script(	"Files",Expr(filelist)	)
)));
time=(hptime()-t0)/1000000;
print("add files info: ", time);

//merge the table scripts 
merged Script = dt1 << get script();
dt1 << delete scripts(dt1 << Get Table Script Names);
Eval(eval Expr(dt1 << new  table script ("merged",Expr(Name Expr(merged Script))))); 

dt2 = dt1 << Subset( All rows, Selected columns only( 0 ) );

t0= hptime();
dt1 << Concatenate( dt2);

time=(hptime()-t0)/1000000;
print("concat 2 files - merged scripts: ", time);

vs before:

(view in My Videos)

Re: Why is Tables/Concatenate so slow?

hogi — Sat, 09 Nov 2024 16:33:50 GMT

according to JMP support, the bad performance is no bug, but as expected: TS-00173141

Comparing all the text in all the script in the tables being concatenated takes a long time. It is a lot of information to compare. It is not surprising that the concatenation is so much slower with the large number of table scripts your example tables have.

Re: Why is Tables/Concatenate so slow?

hogi — Thu, 18 Sep 2025 10:47:03 GMT

seems to be fixed in JMP19: