One thought I had was echoing the inspection::accountID field down into the lineItem table. Would have to be scripted, but doable.
I had a similar issue recently and found that switching the IN for EXISTS helped a lot.
When using IN the subquery has to run until it has a complete result set before the comparison can be performed, with EXISTS the subquery stops as soon as it finds a match. If the subquery is large then EXISTS can speed things up quite a lot.
Wonderful! Thank you! Spares the users the pain of having to update 150 or so child records every time they update the assigned inspector ...
You're welcome. Although that little tip seems insignificant compared to what I've learnt from you over the last few months!
Hey, it helped me. That makes it significant.
Uh oh. Looks like I spoke too soon. You're right; it is much faster, but it doesn't return the right result. It returns a lot more records that don't match what I'm looking for.
Here's what I tried:
FROM lineItemElement JOIN lineItem
ON lineItemElement.lineItemID = lineItem.lineItemID
FROM lineItem JOIN inspection
ON lineItem.inspectionID = inspection.inspectionID
WHERE inspection.accountID = <<currentAccountID>>
I see you have your answer (though it might not be correct, now). I wanted to add that you can do a JOIN on a JOIN. And also, sometimes you should start from a grandchild to get the child and parent information - much like we would print (or export) Order Items with Order and Customer data in FMP. Think in terms of what do you have in the RG (relationship graph) and where would you search for the data. But it depend on what you really need to get at. I'm having trouble following what you have (and what you need). I don't know if this helps
FROM grandchild g
JOIN child c ON g.childID_fk = c.childID_pk
JOIN parent p ON c.parentID_fk = p.parentID_pk
That actually makes sense because you had 'WHERE lineItem.lineItemID IN' so that acted as the filter which has now been removed. You need to rewrite the subquery to include that filter.
Am I right in thinking that you're trying to find all lineItemElements which a particular Inspector created?
Yes, you are correct. That is the purpose.
Unfortunately, when I left the filter in the WHERE clause, it threw a syntax error.
OK. I'll take a look a little later (sorry, I can't do it now)
That syntax error is correct, you can't using the filter with EXISTS which is why we need to rework that subquery to include it.
Yes, that seems to work. Still not very speedy, though. I'll try the new syntax at the next run.
In SQL, if you need to refer to a value later in a query, typically in a sub-query, you create a "correlated sub-query" not just a regular sub-query.
Having never done these in FMP, though they're not unusual, I don't know if FMP supports these queries.
Thanks, morkus. I think Beverly's version has me about as good as it's going to get. It dropped the time from 20 - 25 seconds down to 4 - 6 seconds. Huge improvement.
Now I have to look at the other part of the process that's really slow. But that's a new thread ...
Well, Yes, Mike! ExecuteSQL() has speed issues. This is a known factor, compounded by how the data is structured.
Sometimes an ExecuteSQL() can get us to a virtual table (see the FTSA) and that can be queried with another ExecuteSQL(). And sometimes 'native FMP' will get us where we need to be.
It's not a SQL server, so we work with what we can...