Why is SQL slower on server than on client?

Questions
,
21

100% avoid local eSQL queries against tables that have any open records in the current session. You can use PSOS to query tables with open records, but they will not include the uncommitted changes, if that's acceptable.

2 Likes
22

There's no built-in command to commit all open windows is there? I imagine I'd need to make a script that freezes, then gets a window list and cycles through each committing separately, ideally ending in the same order they were already in.

23

No, each window is a separate entity in its own right. Memory management in FileMaker Pro isn’t brilliant as far as opening and closing windows (we get to monitor the memory. management within our RemoteApp streaming servers). The fact that you sent the SQL to the server would indicate you can do this without multiple windows, as new window is not an option within FileMaker server, hence a commit before you run it should be possible without worrying about other windows. It is just before you run the SQL you need to commit - never use a set field using ExecuteSQL, always set a variable and then set field. If running from a layout with a portal and if dependent on a portal record, then you need to record the portal row/object, pick up the criteria as variables, commit (and lose focus), run the SQL, then return to the object/portal row.

I don’t know if any of the above is of help, but it is what you should be aiming for.

Kind regards

Andy

24

That's news to me. We often use multiple windows on server to manage and switch between context. It works so I am fairly certain windows, albeit virtual, exist on the server.

25

Ooops, yes sorry, my bad. Was working from memory, but see it is available under server compatibility. I’m pretty sure this only became available with the swap from MDI to SDI, v15 and before didn’t work. It is just window commands for adjusting, arranging, moving, etc. that are not compatible.

Thanks for picking me up on that one.

26

I don't think @weetbicks was saying to avoid a custom function to get the field's SQL-friendly field name and table name, if the custom function is just doing this (for field):

GetValue ( Substitute ( GetFieldName ( someFieldReference ) ; "::" ; "¶" ; 2 )

I think he was saying that, if your custom function queries the database's schema metadata table to get the BASE table, that can become slower. If it just does the GetValue(Sub(GFN(
))) method above, that shouldn't be any slower than doing it explicitly in your calculation, and using the CF is a lot easier to read. But, make sure it isn't going some kind of GetBaseTable method that queries the metadata table "FileMakerTables" et al.

27

Right, pretty sure that was a misunderstanding of what the SQLTableName and SQLFieldName CFs do. They don’t actually use eSQL, they just fetch SQL-safe table/field names for use in eSQL calcs.

The ones we use also have that whole business wrapped in Quote ( ) for extra SQL safe-ness.

That said, we have seen those CFs (and GetFieldName in particular) slow things down if you have a large, overly-connected graph, like with complex Selector-Connector models. Even though you wouldn’t think it would need to, if the TO in your calc is related to the current context, FM has to “walk the graph” to get there. It’s quicker to fetch a GFN call from an unrelated TO. Therefore, if you do have large connected graph, and don’t have the bandwidth/budget to un-connect it, it may help to add a collection of disconnected TOs just for fetching table/field names for eSQL.

7 Likes