topic Re: How to run multiple SQL queries in parallel, wait for fnish, and get list of datatables in Discussions

How to run multiple SQL queries in parallel, wait for fnish, and get list of datatables

UEJ — Mon, 15 Jan 2024 09:30:37 GMT

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

Re: How to run multiple SQL queries in parallel, wait for fnish, and get list of datatables

jthi — Mon, 15 Jan 2024 09:39:20 GMT

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.

Re: How to run multiple SQL queries in parallel, wait for fnish, and get list of datatables

ErraticAttack — Mon, 15 Jan 2024 23:06:07 GMT

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.

Re: How to run multiple SQL queries in parallel, wait for fnish, and get list of datatables

mmarchandTSI — Mon, 15 Jan 2024 23:59:22 GMT

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