- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
How to run multiple SQL queries in parallel, wait for fnish, and get list of datatables
Hi,
I would like to have a function, that (i) runs multiple SQL queries in background (connection and sql statements from string lists with arbitrary lengths), (ii) waits until all queries are finished, and (iii) returns a list of datatables. Please find my code below. I don't know how to gather the data tables correctly.
Thank you in advance.
Run_SQL_Queries = Function({o_connections, o_sqls},
i_dts = List();
For(i_counter = 1, i_counter <= N Items(o_connections), i_counter++,
i_dt = Empty();
i_connection = o_connections[i_counter];
i_sql = o_sqls[i_counter];
query = New SQL Query(Connection(i_connection), Custom SQL(i_sql)) << Run Background(OnRunComplete(i_dt = queryResult));
Insert Into(i_dts, i_dt);
);
Return(i_dts);
);
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to run multiple SQL queries in parallel, wait for fnish, and get list of datatables
I've been doing something similar for a while. I run multiple queries simultaneously and then perform operations on the tables. I have adapted my solution for your application. It's not pretty, but I have tried many, many other ways in JMP, and nothing else has accomplished what I wanted.
Names Default To Here( 1 );
o_connections = {....Connection List....};
o_sqls = {....Query List....};
::i_dts = {};
Run_SQL_Queries = Function( {o_connection, o_sql, i_counter},
{Default Local},
Eval(
Eval Expr(
Expr(
Parse( "::i_dt" || Char( i_counter ) || " = Empty()" )
);
New SQL Query( Connection( o_connection ), Custom SQL( o_sql ) ) << Run Background(
OnRunComplete(
Expr(
Parse( "::i_dt" || Char( i_counter ) || " = QueryResult;" )
);
Insert Into( ::i_dts, QueryResult )
;
)
);
)
)
);
For( i=1, i<=N Items(o_connections), i++, Run_SQL_Queries( o_connections[i], o_sqls[i], i ) );
For( i = 1, i <= N Items( o_connections ), i++,
Eval( Parse( "While(!Is Scriptable(::i_dt" || Char( i ) || "), Wait(.5))" ) )
);
Show( ::i_dt1, ::i_dt2, ::i_dts );
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to run multiple SQL queries in parallel, wait for fnish, and get list of datatables
I'm not sure if you can build such a function in JMP. You might have to use << OnRunComplete within your << Run Background and perform whatever you are doing, after all queries are ready, using that.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to run multiple SQL queries in parallel, wait for fnish, and get list of datatables
Unfortunately JMP only provides a single execution thread for JSL. For my company I get around this limitation and do exactly what you're wanting to do by using a persistent Python session. I launch the Python session from JMP, which has a hook for when the STDOUT buffer is not empty, and in Python I start a localhost server and wait for commands. Then in JMP I can connect to the localhost server and send messages. Python will then, in parallel, complete the assigned tasks (whether it's SQL or file-transfers or anything needed) and have special print statements for when complete, thus closing the loop and allowing JMP to know when the queries are completed.
For SQL I have Python save the results in a temporary file then JMP can simply read the file.
It is a little round-about, but not too much more difficult than if JMP provided a means for concurrency or asynchronous execution.
This works quite well and the slowdown due to IPC is not noticeable.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to run multiple SQL queries in parallel, wait for fnish, and get list of datatables
I've been doing something similar for a while. I run multiple queries simultaneously and then perform operations on the tables. I have adapted my solution for your application. It's not pretty, but I have tried many, many other ways in JMP, and nothing else has accomplished what I wanted.
Names Default To Here( 1 );
o_connections = {....Connection List....};
o_sqls = {....Query List....};
::i_dts = {};
Run_SQL_Queries = Function( {o_connection, o_sql, i_counter},
{Default Local},
Eval(
Eval Expr(
Expr(
Parse( "::i_dt" || Char( i_counter ) || " = Empty()" )
);
New SQL Query( Connection( o_connection ), Custom SQL( o_sql ) ) << Run Background(
OnRunComplete(
Expr(
Parse( "::i_dt" || Char( i_counter ) || " = QueryResult;" )
);
Insert Into( ::i_dts, QueryResult )
;
)
);
)
)
);
For( i=1, i<=N Items(o_connections), i++, Run_SQL_Queries( o_connections[i], o_sqls[i], i ) );
For( i = 1, i <= N Items( o_connections ), i++,
Eval( Parse( "While(!Is Scriptable(::i_dt" || Char( i ) || "), Wait(.5))" ) )
);
Show( ::i_dt1, ::i_dt2, ::i_dts );