Performance: ExecuteSQL() versus List()

[Alex Taylor]

I've been working on optimizing a complex part of a solution that relied on the List() function to send multiple parameters to a custom function. Through my research I came across this article:

 

http://www.teamdf.com/weetbicks/the-search-for-fast-aggregates--trial--error/99/

 

This article discusses the fact that when you use List(), Sum() or other aggregate functions, FileMaker sends the entire record set to the client, not just the data from the requested field(s). 

 

Does anyone know if this is also the case with ExecuteSQL()? I would assume that an SQL query would only return the requested data, but I'd like to know if this is definitely happening or not.

[Priyabrata]

Hi,

 

For you question regarding the SQL result in filemaker. Your assumption is correct.

The ExecuteSQL function would only return the list of values for the fields requested in its query.

 

 

Regards

Priyabrata Sahoo

Mindfire Solutions, India

[Charity]

"FileMaker sends the entire record set to the client, not just the data from the requested field(s). "

 

wow.  then ExecuteSQL would be faster because it would have far less to download I think.  Another plus for SQL. 

[Alex Taylor]

Indeed. That was my hope! Thanks for the clarification, Priyabrata.

[David Jondreau]

I don't think the answer given is correct. Or is, at least, being misinterpreted.

 

In general, if FileMaker is getting the value from a field of one record, it is getting all the data for that record, there are some exceptions (container fields), but those exceptions are not based on the function used to access the record. ExecuteSQL() is no different than List() in this regard.

 

If you run some tests and find out differently, I'd be very interested in knowing.

[Priyabrata]

Hi David,

 

You are correct mate, I only clarified that the ExecuteSQL() works as they wanted. The list also works the same and i didn't deny it.

So i think we both are right.

 

Thanks