topic Re: Open Data Base Efficiency/Run Time in Discussions

Open Data Base Efficiency/Run Time

aharro — Mon, 21 Oct 2024 02:42:10 GMT

Hello,

When you use Open Database() it opens the database and runs the query. Well, when you need to run multiple queries on the same database does it hinder run time performance?

When I run local queries the run time is super quick every time. However,
when I run several queries on JMP with Open DataBase() I seem to get mixed results. I'm thinking I may be losing performance by reconnecting to the db over and over.

Is there a way to stay connected to the db and pass in queries?

for(i=1, i<=N Items(query_list), i++,
Open DataBase(server, query_list[i], "foo");
wait(1);
);

Re: Open Data Base Efficiency/Run Time

jthi — Mon, 21 Oct 2024 04:09:15 GMT

I would suggest using New SQL Query() instead of other methods ( ). It is the most complicated one and has lacking documentation (in my opinion) but it is still the way which will be getting enhancements.

Re: Open Data Base Efficiency/Run Time

mmarchandFSLR — Mon, 21 Oct 2024 13:31:49 GMT

New SQL Query can run multiple queries simultaneously, if you use the <<Run Background message.

And you can keep it simple by using Custom SQL.

For Each( {v, i}, queries,
			New SQL Query(
				Connection( constring ),
				Custom SQL(
					"SELECT Col1, Col2
 FROM Table1
 WHERE Col1 = 'Good Data'"
				),
				Query Name( v || " contents" )
			) << Run Background(
				On Error( Print( "Huh.  Didn't work." ) ),
				On Run Complete( dt = QueryResult...... )
			)
		)
	)
);