cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Martin
Level V

Query Too Slow for Script

I have used Query Builder to script several queries. Then I placed these scripts back to back. Next I want to work with the new data tables formed form the queries.

 

My problem is that one of the queries takes >10 seconds to run, and the JSL following the queries tries to work with the "to-be-formed" table (which is not there when the script gets to that portion of the code). This obviously causes an error and quits the script.

 

I have tried to use the "Run Background" command, but that just seems to end the script all together.

 

Any ideas how I can run the queries and force a WAIT until all queries have returned their tables prior to then running the rest of the script (which acts on those tables)?

 

Thank you,

Martin

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

Re: Query Too Slow for Script

I suspect that the query << Run returns immediately.  I'm not sure how you could sync this up.

 

I use execute SQL instead, and that seems to wait until completion before advancing in the script.  For example if you had 4 SQL statements to run:

dbc = create database connection(<connection-string>);

dt1 = execute sql(dbc, sql1);
dt2 = execute sql(dbc, sql2);
dt3 = execute sql(dbc, sql3);
dt4 = execute sql(dbc, sql4);

close database connection(dbc);

 

 

View solution in original post

8 REPLIES 8
pmroz
Super User

Re: Query Too Slow for Script

Did you put a wait(0); command after each retrieval?  That should force JMP to wait for completion of the previous command(s).

Martin
Level V

Re: Query Too Slow for Script

Yes. Wait(0) actually does not do as you suggest. The queries actually return in a random order, based on (what appears to be) their size and complexity.  I have 7 queries and it is the 4th one that takes a long time. I have tried to put that query first in the script, but the others run so fast that this one still has not completed when the remaining code in the script executes.

 

Here is part of the code I am using, with much of it removed to protect the source. But, it gives the general structure. I have removed queries 4-7 and the last 4 Join statements at the end for brevity.

 

Query1 = New SQL Query(

       Connection( ... 

       ),

       Order By(  ),

       PostQueryScript( ... 

       )

);

Query1 << Run;

Wait(0);

 

 

Query2 = New SQL Query(

       Connection( ... 

       ),

       Order By(  ),

       PostQueryScript( ...

       )

);

Query2 << Run;

Wait(0);

 

 

Query3 = New SQL Query(

       Connection( ...

       ),

       Order By(  ),

       PostQueryScript( ...

       )

);

Query3 << Run;

Wait(0);

 

//Join Tables

Table12 = Data Table( "01" ) << Join(

       With( Data Table( "02" ) ),

       Merge Same Name Columns,

       Match Flag( 0 ),

       By Matching Columns(

              :

       ),

       Drop multiples( 0, 0 ),

       Include Nonmatches( 0, 0 ),

       Preserve main table order( 1 ),

       Output Table( "12" )

);

 

Table123 = Data Table( "12" ) << Join(

       With( Data Table( "03" ) ),

       Merge Same Name Columns,

       Match Flag( 0 ),

       By Matching Columns(

              :

       ),

       Drop multiples( 0, 0 ),

       Include Nonmatches( 0, 0 ),

       Preserve main table order( 1 ),

       Output Table( "123" )

);


pmroz
Super User

Re: Query Too Slow for Script

I suspect that the query << Run returns immediately.  I'm not sure how you could sync this up.

 

I use execute SQL instead, and that seems to wait until completion before advancing in the script.  For example if you had 4 SQL statements to run:

dbc = create database connection(<connection-string>);

dt1 = execute sql(dbc, sql1);
dt2 = execute sql(dbc, sql2);
dt3 = execute sql(dbc, sql3);
dt4 = execute sql(dbc, sql4);

close database connection(dbc);

 

 

Martin
Level V

Re: Query Too Slow for Script

I will try that and let you know how it turns out.

 

Thanks

Martin
Level V

Re: Query Too Slow for Script

Thank you so much, pmroz!!!! This requires a bit of re-writing, but works like a charm!

Eric_Hill
Staff

Re: Query Too Slow for Script

Sorry I was late to this - Brian's suggestion of using Run Foreground on the query in JSL is the way to assure that a query finishes before the next line of JSL runs.  No need to re-write!

Eric

Re: Query Too Slow for Script

Hi Martin,

 

You could also try turning off background operations for queries.  This worked for me.  You can either go to "Preferences->Query Builder->ODBC->Run queries in the background when possible" and uncheck that, or in script you can do something like:

 

Query Builder Settings( Background Query( 0 ) )

 

At the top of your script.

 

Brian Corcoran

JMP Development

Re: Query Too Slow for Script

Actually, even better is using Run Foreground.  Sorry for not getting this correct, right away, but something like:

 

Query1 = New SQL Query(

       Connection( ... 

       ),

       Order By(  ),

       PostQueryScript( ... 

       )

);

Query1 << Run Foreground;

 

should work.

 

Brian