fmp17 Which external SQL select faster? Base Elements or Monkeybread Software?

[Will_Logic]

Hi, I am using the Base Elements plugin Execute SQL command to select SQL on a table in another FileMaker file. For a file with many fields and quite a lot data in some fields, this times as about 1 minute just for SQL SELECT command for 1,000 rows. I haven't used MBS (monkeybread software) plugin yet, but I wonder if anyone knows if it is significantly faster?

I.e. BE_FileMakerSQL vs. MBS(FM.SQL.Execute..) - is one significantly faster?

Here's the code to select 1,000 rows for reference, $timer_seconds comes as about 60 seconds here. If I export same table internally in FileMaker using the menu export to Excel, it takes 29 minutes for all 41,000 rows, so timing seems similar for the Base Elements SQL. (NB there is some flag going on here in the FileMaker database so internal export gets 41,000 rows but script SQL SELECT COUNT(*) gets 200,000 rows for same table, but I don't think that is relevant to my question).

 

Set Variable [$time1; Value: Get(CurrentTimeUTCMilliseconds)]
Set Variable [$part_ca; Value: BE_FileMakerSQL ( "SELECT * FROM Contact_ACTIVITY OFFSET 100000 ROWS FETCH FIRST 1000 ROWS ONLY" ; "&≠≠&" ; "EOR≠≠EOR" ; "DataContacts11" )]
Set Variable [$timer_seconds; Value: (Get(CurrentTimeUTCMilliseconds) - $time1) / 1000]

MBS command I think would be something like:

MBS( "FM.SQL.Execute"; "DataContacts11"; "SELECT * FROM Contact_ACTIVITY OFFSET 100000 ROWS FETCH FIRST 1000 ROWS ONLY" ; ...delimiters here I'm assuming.. )

We only have base FileMaker here on machine I am using, which can run the Base Elements plugin, but to run MBS plugin, needs FileMaker Pro Advanced to install it I think, am getting several databases in roughly same FM format where script workspace disabled, hence need to use SQL select run from script in my own external FileMaker file. Might consider getting FMP Pro Advanced if MBS is faster.

Tx any suggestions!

[Wim Decorte]

why not use FM's native ExecuteSQL() function since you're only doing SELECT?

As to the delay: since your query is fairly simple I'll guess it may be because you have an open record in the table you are querying.  It's a FM behavior that I demoed at the 2014 Devcon and you can download the demo file here:

https://www.soliantconsulting.com/blog/executesql-named-buckets/

With an open record in your session in the target table you're forcing FMS to send you the whole table so that your client can do the query, instead of FMS itself doing the query.  So make sure to commit before running the query.

[Will_Logic]

Thanks kindly @Wim Decorte , this is really good to know, but unfortunately the database is built in a program on top of FileMaker, and this program stops access to the script workspace within the database. Therefore I cannot use the native ExecuteSQL() function.

So I need to use FM Open File command first on the external database (which then prompts me for credentials which I have), as the only way to gain a reference to the files and tables. Then I can only use command like Base Elements or MBS, my main goal is to dump large parts of the data out in one script running one time, so I guess in this case means I can't avoid the problem of FM 'sending whole table' as you describe in your post, and this slowing down SQL SELECT execution presumably by many times over? I'm not using FileMaker Server here by the way, just FileMaker Pro (non-Advanced version).

I don't think there is way to reference external FM database table without there being a record open?

[Wim Decorte]

ExecuteSQL() is a function like any plugin function so anywhere you can use a plugin call, you can use the ExecuteSQL() function... so I don't follow that part of your explanation.  We're not talking about the "Execute SQL" script step (note the space in the name), two different things, perhaps that's where the confusion comes from?

And yes: you can reference an external table without having to have an open record.

 

 

[Will_Logic]

Thanks @Wim Decorte but my understanding is ExecuteSQL() function (not step) can still only access a table in the current 'context' file, ie same database in which the script is running. https://fmhelp.filemaker.com/help/17/fmp/en/index.html#page/FMP_Help/executesql.html I don't see any way to specify/reference an external FM file using this function? I am having to run this script in a file external to the database I am querying, which I can do with BE_FileMakerSQL for example, but then only by opening the files first to get the reference.

[Wim Decorte]

Since you have access to the external file, just add TOs to your file for tables based on the 'real' file.

ExecuteSQL() works on any TO of the file your run the function in, but those TOs are not limited to base tables of its own file.  The TOs can be from any other file.   Years ago I provided a demo file of a self-building data dictionary that works completely based on this principle.  Maybe a google search will turn up that demo if you want to see it in action.

In short: just add TOs to your file and then you're done.

[Will_Logic]

Thanks kindly, @Wim Decorte, sounds like this could speed up process, but sorry, I don't know what a TO is!

So I create a new table or something in the file where I am running the script, which itself is some kind of mirror or reference to the different table I want in a different file in the external database which I wish to query? Then I put this new table name in the ExecuteSQL() query?

By the way, currently I am using the Open File step to open this external database, then I can specify the file and table names with BE_FileMakerSQL function, which requires the file to be open in order to reference it. If the file has not been opened with Open File, then if I put filename into BE_FileMakerSQL, it fails because doesn't know which file I am referring to. Also, this db prompts me to enter password credentials after running the Open File step, which I have.

Tx kindly, sorry if my original question not clear.

[Wim Decorte]

A TO is a Table Occurence: the 'tables' you see in the relationships graph in FM.  Using your own file, you go to the graph and you add a reference to the 'real' file.  Once you've authenticated there you pick each base table from that 'real' file in turn, which will add a TO for that external table into your own file, give them an easy name (doesn't have to be name of the actual base table).  Once you have that done, close the "manage database" dialog and now you can target any of those TOs by the name  you have given them.

 

 

[Will_Logic]

Thanks again @Wim Decorte not sure if I did this exactly correct, but I tried timings on SQL Select of 5,000 rows of same (large) table using the native ExecuteSQL() function, without opening the external file first, versus the BE_FileMakerSQL() function, where I need to Open File script step first, and got 206.8 seconds vs. 300.3 seconds, ie about 30% faster.

Just in case anyone can be bothered to look(!) I enclose the code I used, this 30% gain may not be worth it for what I'm doing. FYI this is selecting all fields of this table, and the SQL select string for first 1000 rows was 40,257,300 characters from FM Length() function. I'm not going to need the longer fields in the end, but I am just doing trying to do time comparisons now.

I also enclose picture of the referenced external table in relationships graph - just checking if I did anything wrong? 🙂

1 - Here is the script where external file is opened, using Base Elements plugin command BE_FileMakerSQL - shows 300.3 seconds for this table:

Open File [; "DataContacts11"]

Set Variable [$offset; Value:0]
Set Variable [$timer1; Value:Get(CurrentTimeUTCMilliseconds)]
Loop
	If [$offset = 0]
		Set Variable [$offset_string; Value:""]
	Else
		Set Variable [$offset_string; Value:" OFFSET " & $offset & " ROWS "]
	End If
	
	Set Variable [$be_activity; Value:BE_FileMakerSQL ( "SELECT * FROM Activity" & $offset_string & "FETCH FIRST 1000 ROWS ONLY" ; "," ; "¶" ; "DataContacts11" )]

	# To mark progress, create empty file named "offset_" & $offset & "rows.txt"
	Set Variable [$progress_file; Value:BE_WriteTextToFile ( "C:\FileMaker_Script_Testing\FM_SQL_Select_Progress\offset_" & $offset & "rows.txt" ; "" )]
	
	Set Variable [$offset; Value:$offset + 1000]
	Exit Loop If [$offset > 5000]
End Loop

Set Variable [$time_seconds; Value:(Get ( CurrentTimeUTCMilliseconds ) - $timer1) / 1000]
Show Custom Dialog ["Time in Seconds"; $time_seconds]

Exit Script []

2 - Here is the script using native ExecuteSQL() where file is not opened, ie restarting FileMaker and running this script first, gives 206.8 seconds:

Set Variable [$offset; Value:0]
Set Variable [$timer1; Value:Get(CurrentTimeUTCMilliseconds)]

Loop
	If [$offset = 0]
		Set Variable [$offset_string; Value:""]
	Else
		Set Variable [$offset_string; Value:" OFFSET " & $offset & " ROWS "]
	End If
	
	Set Variable [$sql_activity_select; Value:"SELECT * FROM Activity" & $offset_string & "FETCH FIRST 1000 ROWS ONLY"]
	Set Variable [$activity_data; Value:ExecuteSQL ( $sql_activity_select; ","; "¶" )]

	#create empty file named "offset_" & $offset & "rows.txt"
	Set Variable [$progress_file; Value:BE_WriteTextToFile ( "C:\FileMaker_Script_Testing\FM_SQL_Select_Progress\offset_" & $offset & "rows.txt" ; "" )]
	
	Set Variable [$offset; Value:$offset + 1000]
	Exit Loop If [$offset > 5000]
End Loop

Set Variable [$time_seconds; Value:(Get ( CurrentTimeUTCMilliseconds ) - $timer1) / 1000]
Show Custom Dialog ["Time in Seconds"; $time_seconds]

Exit Script []

Thanks any comments.

[IdealData]

So, now you know how to add TOs to your file then why not establish a relationship to it?

Relationships can be sorted, so you can get a reverse sorted table to access the end of the table first.

AFAIK FM relationships can be cached, however SQL is never cached.

The report to XLSX is fairly slow, try CSV instead.

[Will_Logic]

Thanks @IdealData I'm just going to dump the data though, possibly convert to JSON within FM first, from a number of databases same format, all with script workspace disabled. I dont think making a relationship will speed up dumping the data. I'm not doing report, just going to write to text file. I'm changing delimiters as there are commas and return characters in the actual data, so text file anyway. the focus of my question is really about data dumping speed.

[Wim Decorte]

Just as an FYI, you don't need a plugin to write to a text file.  The FM file script steps can do that for you.

[MonkeybreadSoftware]

I would like to add that FM native, MBS and Base Elements all use the same built-in SQL engine from FileMaker. Speed should be no difference.

And for writing file, you can use built-in script steps if a script is okay. The plugin can be used in custom function, scripts and any other calculation you may have.