Query Too Slow for Script

[#1]

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

[#2]

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

[#3]

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" )

);

---

[#4 · Accepted solution]

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);

[#5]

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

Thanks

[#6]

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

[#7]

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

[#8]

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

[#9]

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