Any advice on improving performance of a multi join ExecuteSQL command?

[crayfish]

 

 

The below code takes 5-6 seconds to process (it's inside a webviewer).  Client machine is connected to local Filemaker Server over wireless network.

 

I'm trying to avoid creating a relationship just for this one usage, hence ExecuteSQL().  There is an intermediate table JoinClientsWebsites between Clients and Websites since some clients have the same website.

 

Could I do something differently to improve performance?

 

Thanks!

ExecuteSQL("
	SELECT url
	FROM Websites
	JOIN JoinClientsWebsites
		ON Websites.id=JoinClientsWebsites.id_websites
	JOIN Clients
		ON JoinClientsWebsites.id_clients=clients.id and JoinClientWebsites.primary='yes'
	WHERE Clients.id=?" ; "" ; "" ; Clients::id 
)

[timdietrich]

What version of FileMaker Server are you using?

[crayfish]

Server 13.0.1

[ave!]

Assuming you want the url of the primary web site for the specified client, the query can be a lot simpler.

 

ExecuteSQL("
  SELECT url
  FROM JoinClientsWebsites
  LEFT JOIN Websites ON Websites.id = JoinClientsWebsites.id_websites
  WHERE JoinClientsWebsites.primary = 'yes'
    AND JoinClientsWebsites.id_clients = ?
" ; "" ; "" ; Clients::id
)

Where it's possible, use the first table (the one after FROM) to filter results.

In FileMaker, it's faster to search by a field of the layout table rather than by a field of a related table, right?

It's basically the same thing.

 

Regards,

Iwao

[crayfish]

Awesome... that's much faster.  Operation seems to have dropped down to less than a second.  Thanks for your help!

[timdietrich]

Another thought: Since you are using FMS13, you could run the query server-side using Perform Script on Server, and then pass the result back as a script result. 

[Wim Decorte]

SQL queries are already always executed by FMS unless there is an open record in the target tables in the user's session so there would be no immediate benefit by using PSoS.  And more vulnerability (PSoS not responding,...)

 

One note of caution on the webviewer: you don't always have control about when it fires.  So it is generally better to collect the URL through script (event triggered if needed) and set the URL in a field on the record (or global field).  That way, whenever the web viewer decides to refresh itself it just reads from the field and does not re-fire the SQL query.

[LaRetta]

In FileMaker, it's faster to search by a field of the layout table rather than by a field of a related table, right?

 

 

Actually, if the related field is indexed, searching a related table is just as fast as searching locally. :-)