Hard to say without knowing all the details of your design. The simpler you can keep the SQL query the faster it will perform. I would probably do both approaches and test them.
Apologies for the slowness in replying - I didn't realise you'd replied to my question!
I prefer ExecuteSQL as I figure it's easier to build the SQL based on the users selections so I'm tackling that approach at the moment but optimising the SQL is going to be the challenge here. It's taking 25-26 seconds to perform the query even using PSOS - acceptable but I'd like to improve it. Let's see how that goes...
How many records in the customer and subscriptions table are you talking about?
The types of SQL queries you are talking about may be very difficult to optimize to make run fast in FM. Since FM is not a native SQL database.
Couple of options:
1. Use a Virtual List ( google it, you should find a lot of examples and explanations ), to pull in relevant data only. Then query that with your SQL. The kind approach like chopping off large chunk of rock that won't be in the final statue so you can work with as little stone as possible.
2. Use native FM finds. You may find, as long as you are not searching on unstored calcs, that they are much faster and you can creatively combine them to generate the exact results you want.
There are 12,000 Customers and 57,000 Subscriptions.
I found an old discussion which suggested that instead of using JOINs it was best to run separate queries and then use IN to get the final list of IDs. That seems to be working really well so far but I'm going to run native finds as well and see which works the best in terms of performance.
I've heard of the Virtual List technique but have never used it so I'll be checking that out too.
Thanks for your help