I have 3 tables (Products, Live Jobs, Archived Jobs)
There are around 300,000 records in the archived Jobs which grows by around 600 per day, each job has a productID field and a CustomerID and a date
There are around 8000 records in the live jobs which stays static as old jobs are archived and new jobs added, each job has a productID, CustomerID and a date
There are around 7000 product records and each prodct has a uniqueID
In the product table I have created the following fields;
ProductSoldCount (unstored calculation which adds the count of related fields from the 2 jobs tables by using the 3 fields above and matching productID)
This works fine when you browse and flick through the records in the product table, however when you try to either sort the records to show the most popular products or find the procts with ProductSaleCount greater than zero it takes an absolute age, around 10 minutes.
It is a hosted solution using FM12 server advanced.
Can anyone give me help in finding a more usable option?