211233_en-US

liliconcom

Biggest problem in JMP in sub setting tables.... sluggishness

I have used multiple ways to subset a long table (rows >100K, very common size of datatable). The problem arises because subsetting requires selecting rows which means a operation on the table before subsetting. And if the table is long it makes it very slow. specially when a foor loop is required to generate plots by subsetting everytime a new parameter.

it does not matter whatever you use, it is just slow: I am not sure if JMP team have any solution to speed up the subsetting??????

 

select rows where

get rows where

Re: Biggest problem in JMP in sub setting tables.... sluggishness

Why are you subsetting the data table?

 

Try selecting the data you don't want, set the Hide and Exclude row state, make your plot, and repeat for the next group.

Re: Biggest problem in JMP in sub setting tables.... sluggishness

Also, could you accomplish the same task using filters?

Re: Biggest problem in JMP in sub setting tables.... sluggishness

I think i mentioned it clearly that problem arises when you select the data, which slows it down.

but i may be not able to understand what you are suggesting. could you provide JSL example. surely i will try if it speeds up things.

Thanks

Ram

Re: Biggest problem in JMP in sub setting tables.... sluggishness

@Dan_Obermiller 

could you add JSL to do that in for loop without the filter pop up?

Thanks

Re: Biggest problem in JMP in sub setting tables.... sluggishness

I've never had a problem if I use get rows where or even just the rows.  You don't actually have to select them.  

 

names default to here(1);
dt = open("$SAMPLE_DATA\Big Class.jmp");
rows = dt << Get Rows Where(:sex == "M");
dt_sub = dt << Subset(rows(rows));

 What are you using to select the rows?  It's possible that's slow. 

Re: Biggest problem in JMP in sub setting tables.... sluggishness

Here is a simple example of how to do the analyses by groups without subsetting

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );

// Reset the data table row states
dt << clear row states;

// Find the different values for Sex
Summarize( byGroup = By( :sex ) );

// Loop across the different groups and analyze them
For( i = 1, i <= N Items( byGroup ), i++,

 // Reset the data table row states
 dt << clear row states;
 
 // Hide and Exclude the unwanted rows
 dt << select where( :Sex != byGroup[i] );
 dt << hide and exclude;
 
 // Run the analysis
 ow = dt << oneway( x( :age ), y( :height ) );
 
 // Change the title of the report to show what data are in the analysis
 Report( ow )[Outline Box( 1 )] << set title(
  Report( ow )[Outline Box( 1 )] << get title || ": Sex=" || byGroup[i]
 );
);

// Reset the data table row states
dt << clear row states;

Re: Biggest problem in JMP in sub setting tables.... sluggishness

It sounds like there are several things going on in your script - selection, subsetting, creating reports, all inside a loop.  I think it would be helpful to see a sample script.  How is selection being done?  Are the subsets linked to the original data table?  Which platforms are being created? How many iterations of this loop are run?  If the subset tables are linked, and the reports have auto-recalc enabled, then maybe the process is slowing down with each iteration as all previous reports get notifications about the selection occuring in the source table.  If this is the problem, then the data filter approach that others have suggested may fix the problem because each report would be isolated from the next iteration of the loop.

Re: Biggest problem in JMP in sub setting tables.... sluggishness

@vince_faller  currently i use the same method. but when file size becomes bigger, even get rows  matrix becomes bigger and slow. 

 

if you use small data table like

<SPAN class="token string">Big Class.jmp</SPAN>

, anything can work. problem is with Big data files.

Re: Biggest problem in JMP in sub setting tables.... sluggishness

@txnelson 

There are two  problems with your aproach.

1. you are using "select where(" which is the real root cause for bigger table. once you use select where it is operation of updating the data table of selecting rows (in my case >300K rows) you are just using Big class which is not even counted for a file size.

 

2. this does not work for the situation below

if i had to correlate height data on X_axis for sex="M" and height data on Y_axis for sex="F".

i hope it can help you to undestand the problem now

Thanks

Re: Biggest problem in JMP in sub setting tables.... sluggishness

@danschikore 

it is not the number of iterations. it is the selection of rows which is slow when you have large file size.

Re: Biggest problem in JMP in sub setting tables.... sluggishness

So with this table of a million rows.  It takes a sixth of second to run.  It's probably something to do with your script.  Like @danschikore said, if you gave an example script of it running slow we may be able to help you. 

 

Names Default to Here( 1 );
dt = New Table("Test", 
 Add Rows(1000000), 
 New Column("X", Character, <<SET each="" value=""></SET>

If you're calling a sixth of a second sluggish, then I don't know what to tell you.  

Re: Biggest problem in JMP in sub setting tables.... sluggishness

So with this table of a million rows.  It takes a sixth of second to run.  It's probably something to do with your script.  Like Dan said, if you gave an example script of it running slow we may be able to help you. 

 

Names Default to Here( 1 );
dt = New Table("Test", 
 Add Rows(1000000), 
 New Column("X", Character, <<SET each="" value=""></SET>

If you're calling a sixth of a second sluggish, then I don't know what to tell you.  

 

Also, if you're using a for loop to generate a bunch of plots for each parameter, is there a reason you're not using a by?

Re: Biggest problem in JMP in sub setting tables.... sluggishness

Maybe your select criteria are very complex and that's what is slowing things down.  We can't help you any further unless you provide:

1. Your script

2. A sample of your data (anonymized if necessary)

Re: Biggest problem in JMP in sub setting tables.... sluggishness

@pmroz 

@vince_faller  

Just being practicle here instead of pure theorist .

Below is the script with some added columns. depending on the runs, the result can take up to 2sec. i also added a wait(0), so that it can accomodate the display time updating also.

This time taken is just  the subsetiing two files. After that you can add extra time taken by script to join the two files by some criteria. and then making a bivariate chart. It really becomes slow.

One LOT worth of data is enough speed. but if i just add few more lots......

 

 

 

Try(close(datatable("Test"),nosave));
dt = New Table("Test", 
 Add Rows(1000000), 
 New Column("X", Character, <<SET each="" value=""></SET>

Re: Biggest problem in JMP in sub setting tables.... sluggishness

We can only do theoretical stuff because you haven't told us what you're actually doing.  

 

So when running this it took about .3-.4 seconds for me for a single table (not two of them). I find that completely reasonable.   If you're doing a bunch of other actions along with the subset, then it's not fair to say subset is slow.  If you're running these in a for loop then it's likely what you're doing in the for loop that is causing the problem.  If you're running a script then I would say don't put in the wait(0) unless you need the user to see the table.  In fact you should make them private/invisible so that they never render and use unnecessary time. 

 

What are you actually trying to do?  I feel like this is an XY problem.  

 

If you let us know what you're actually doing we might be able to help you speed it up.  

Re: Biggest problem in JMP in sub setting tables.... sluggishness

How complex are your "get rows where" queries?  What you posted should be quite fast because it's only looking for one value, but I've seen slower performance with more complicated queries.

 

Here's a simple example that shows a time difference for the subset command, by making the subsets invisible and then private.

dt = data table("Probe");
t1 = hp time();
new_rows = dt << get rows where(:Process == "New");
t2 = hp time();

old_rows = dt << get rows where(:Process == "Old");
t3 = hp time();

//s1 = dt << subset(rows(new_rows), invisible);
s1 = dt << subset(rows(new_rows), private);
t4 = hp time();

//s2 = dt << subset(rows(old_rows), invisible);
s2 = dt << subset(rows(old_rows), private);
t5 = hp time();

tn = t2 - t1;
to = t3 - t2;
ts1 = t4 - t3;
ts2 = t5 - t4;

print("New rows: " || char(tn));
print("Old rows: " || char(to));
print("New subset: " || char(ts1));
print("Old subset: " || char(ts2));

Invisible subset timings:

"New rows: 1095"
"Old rows: 964"
"New subset: 3527"
"Old subset: 3366"

Private subset timings:

"New rows: 949"
"Old rows: 883"
"New subset: 1861"
"Old subset: 1638"

As you can see changing from invisible to private reduced the subset time in half.

Re: Biggest problem in JMP in sub setting tables.... sluggishness

My computer is really busy, so the numbers vary a bit, but changing to linked subsets and removing the wait helps, a bunch. Removing the wait is roughly the same as invisible; it gets the table's paint-to-screen out of the measurement. Using a linked subset is a big win since a lot of data no longer needs to be moved about.

st=HPTIME();
rows = dt << Get Rows Where(:X == "C");
dt << Subset(rows(rows),outputtable("sub1"),"linked");
rows = dt << Get Rows Where(:X == "B");
dt << Subset(rows(rows),outputtable("sub2"),"linked");
//wait(0);
st=1e-6*(HPTIME()-st);
print(char(st)|| " Sec");

"2.534772 Sec" -- unlinked with wait(0)

"1.50435 Sec" -- unlinked

"0.467098 Sec" -- linked

 

Re: Biggest problem in JMP in sub setting tables.... sluggishness

From the sounds of it, you are subsetting tables by a column and joining them together.  You may have just oversimplified it for example sake, but if I wanted to plot Y(height, sex==M), X(height, sex == F).  I would just split the table.  These two scripts, a subset and join vs a split, show the split takes about a third of the time.  

 

names default to here(1);
dt = new table("Test", 
 add rows(1000000), 
 New column("sex", character, <<SET each="" value=""></SET>

Also, with Linked you cant change column names and other properties so you couldn't change the name of the column so the join looks a little uglier.  

 

 

Re: Biggest problem in JMP in sub setting tables.... sluggishness

Here is a simpler overview of what i am doing. remeber this is the simplest form of datatable and script which is still fast compared to actual. I ran this on my comp and getting avg 2sec. some people say this is not bad. but remember we are talking about computer time not human time. 2sec is like WW2 time period.

you can try this to know how bad it can be: reduce and increase number of rows by a factor of 10 in the " add rows(1000000).

Try(close(datatable("Test"),nosave));
dt = New Table("Test", 
 Add Rows(1000000), 
 New Column("X", Character, <<SET each="" value=""></SET>

Re: Biggest problem in JMP in sub setting tables.... sluggishness

The biggest problem might be you failed to mention the update / join.

 

Re: Biggest problem in JMP in sub setting tables.... sluggishness

@HI @ram_asra_gmail_,

I'm not sure how much of an issue this introduces in terms of speed, but it certainly will in terms of results: the syntax for your update appears incorrect. The proper specification for match columns using Update is Match Columns() not Matching Columns(). The latter will glue the update table on to your original table row-wise, which in fact will very likely be faster than doing the actual match. If you use Join, it's "By Matching Columns()."

 

Here's the correct syntax for Update:

 

datatable("sub1") << update(
 With( Data Table( "Sub2" ) ),
 Match Columns( :X1 = :X2, :P1 = :P2 )
);

 

Certainly not the only thing going on, but thought I should mention in case it wasn't spotted elsewhere!

@julian 

 

 

Re: Biggest problem in JMP in sub setting tables.... sluggishness

Yeah, this has almost nothign to do with subsetting tables.  I can bring this script down to 100 rows and it still takes 7 seconds.  

 

My first suggestion would be to not run each bivariate independently.  Run them as platforms (or inside vlistbox()) so that they don't evaluate and append.  Then just show at the end.  Doing just that brought me to 3 seconds.  Script is attached.  

 

But it REALLY looks like you're just trying to split a data table.  The split function would probably be better than doing this.  If you have to do it like this for whatever reason. I'd concat all your dt_sub1 tables together with a by column and run them all at once.  But I bet this could be optimized a ton.  

Re: Biggest problem in JMP in sub setting tables.... sluggishness

With the corrected syntax referred to by @julian and a reduced row count of 10,000 I had an average execution time of 2.45 seconds (I also made the bivariates invisible).

 

Replacing the update with an invisible join reduced the average execution time to 0.67 seconds.  Critical to this was dropping columns not needed for the bivariate.  Not dropping them would make it significantly slower.

 

If the join is the bottle-neck you might want to think about performing a virtual join.  To do that you would first need to create a single column that would be the unique key for the join.

 

Re: Biggest problem in JMP in sub setting tables.... sluggishnessI think this trick can help to reduce some time. I already take advantage of invisible subset. but it can do so much only.
Thanks for doing some experiments.Re: Biggest problem in JMP in sub setting tables.... sluggishness

@Craige_Hales linked subset seems can reduce time by a lot. but i have to check with the script i posted two things:

1. By repeating do i get the improved avg time

2. will i be still able to join the two subset tables to make XY chart subsequently.

Thanks