I am hosting a database on 8.0 advanced server and whenever I use the GROUP BY clause from FMP 8 on a client PC, the server goes to 100% utilization and takes nearly a half hour to return results. Can someone tell me if this behavior is normal for FMP when using GROUP BY. I know it's the GROUP BY clause because when I remove it, the code runs fine (still slow but tolerable). I need to know how to optimize this code so that it executes within seconds or if I am SOL. Any help would be appreciated - thanks!

The sql is as follows:

SELECT "Asset Inventory"."ProductID", "Asset Inventory"."OrderDate","Asset Inventory"."ProductType","Asset Inventory"."MfgrID"

FROM "Asset Inventory"

WHERE LOWER("Asset Inventory"."ProductType") = 'software' or LOWER("Asset Inventory"."ProductType")='swflicense' or LOWER("Asset Inventory"."ProductType") = 'swflicense-server'

GROUP BY "Asset Inventory"."ProductID", "Asset Inventory"."OrderDate"

How does the speed change if you reduce the number of columns your grouping by? If that helps, you might try nesting the queries, where you only group a single column in each statement.

One qustion one suggestion. Why are you using the group by function when you are not summarizing any columns? Always surround selection criteria "or" in the where clause with parenthesis. ie. ( a = b or c = d or e = f ). Properly placed parentheses can dramatically improve performance. Something to do with the sql execution plan I guess.