Jump to content

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


This topic is 2572 days old. Please don't post here. Open a new topic instead.

Recommended Posts

 

 

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 
)

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. :-)

Link to comment
Share on other sites

This topic is 2572 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.