Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. Add a FirstOrderDate field to CustomerMaster. Program the application to set the value when the customers orders for the first time.
  2. Same as #1 but use a trigger in the database instead of using the application.
  3. 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

share|improve this question
1  
"...but it does require reading all the records in CustomerOrders." So? I'd take this approach instead of embedding unneeded data. Set-based queries, tuned as good as possible, will be your best bet in my opinion. –  Thomas Stringer Jun 4 '13 at 13:53
1  
@Thomas Stringer: That's 9.7m 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. –  poke Jun 4 '13 at 14:36
    
@poke: You should probably mention those performance issues directly in the question. –  FrustratedWithFormsDesigner Jun 4 '13 at 15:02
add comment

1 Answer

up vote 2 down vote accepted

I would suggest doing it in the application rather than in the database with a trigger, only because it's not integral to the structure of the database; rather it's for performance reasons and I don't think doing it in the database will actually improve performance of the query that's running (it might slightly improve performance of the actual insert/update statements, but that's something that profiling could tell you better than I).

Doing it in the application would also give you more control of performing this operation optionally. It sounds like you would want this to always happen, but I have seen situations where testing would have been made easier if such logic were moved out of triggers so they could be turned on and off for testing purposes.

share|improve this answer
    
Since this is a write-once, read-many environment, using this approach should be reasonably safe from data integrity issues. –  poke Jun 5 '13 at 14:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.