I am new to Filemaker 12 and eager to utilize ExecuteSQL queries as much as possible, but unsure of its strengths and limitations.

Here is an example of a common scenerio in my database that I am hoping to streamline with its use:

When I generate a report in my database that is filtered through a KEY calculation field, I am limited by the fact that the data is used to calculate the KEY field has to be indexed for the report to generate. In order for the KEY field to remain indexed, any data that is referenced in the KEY calculation has to also be stored. If the referenced data comes from another table or relationship, that data will not refresh on its own without using a script.

I know that ExecuteSQL is somehow a better fit here, but I am not sure where it can be used best Should I replace each LOOKUP field that is used by the KEY with a stored ExecuteSQL calculation, or am I better off to place my Queries right in the KEY calculation, and get rid of the other lookup fields altogether?

Do ExecuteSQL Queries remain refreshed at all times? Can they slow down the database by using them too much?

No and no.

ExecuteSQL() is just another function and follows the rules of all other functions. If it's in a calculation field and references a related field, FMP will required it to be unstored. If it's in an auto-enter calculation, it won't refresh unless a local field referenced in the calculation is modified.

On a related note, I don't see Lookups as being useful. Auto-enter calcs are the way to go. But we may not be in agreement with what "Lookup" means in your post.