I have parent-child relationship as shown below. I need to find the first order date for each customer. Obviously, writing a query to do this is fairly straight-forward but it does require reading all the records in CustomerOrders.
I've considered the following optimization approaches:
- Add a
CustomerMaster. Program the application to set the value when the customers orders for the first time.
- Same as #1 but use a trigger in the database instead of using the application.
- Some other possibility that is better than the first two.
What is the best approach to handling this?
CustomerMaster CustomerID Customer Name . . . CustomerOrders OrderID CustomerID OrderDate . . .
As for performance, there are 9.7 million records right now. The query takes 34 seconds. The data is growing all the time. 34 seconds is too slow right now and I expect it to be double that in two years