Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: Database design problem.

    I developing a system which allow customer to purchase data records such as company info.

    The system has about 1 million data records(2GB) and 1000+ customers. Each customer purchase about 5000 data records each time. The data record is updated at the rate of 2000 records per day.

    When a customer puchase the data records again, only the lastest modified records is sold to the customer. I put a flag and version column on the sales table so that when the product table is updated, records in sales table will be updated as well. The product and sales table will then be matched to produce the lastest update data records.

    Using the above way i encounter a performace issue whereby the search for the lastest record is extremely slow. Moreover, the sales table is taking a huge amount of storage space.

    Can anyone suggest me one better way to solve this problem.
    Thanks a lot.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You might want to split the tables into two tables, one for history and one for the current (last updated) status. This should speedup your app as it wont have to search for the last updated record. Also check your indexes etc.

    Alan

  3. #3
    Join Date
    Mar 2004
    Posts
    4
    I added View to speedup the process at the moment, but with the rate of 2000+ reccords updated per day. Its not a long term solution.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by kbl64
    I added View to speedup the process at the moment, but with the rate of 2000+ reccords updated per day. Its not a long term solution.
    Do you mean a Materialized View? I don't see how a regular view could speed up a process, though it may provide a convenient shorthand for it.

  5. #5
    Join Date
    Oct 2003
    Posts
    87
    Originally posted by andrewst
    Do you mean a Materialized View? I don't see how a regular view could speed up a process, though it may provide a convenient shorthand for it.
    I agree; a MV isn't an option here, too many updates! The view might have exploited indexes better. The poster needs to check the indexing on the related tables to ensure they are being used and do some Explains/TKProfs to see if some more indexes are needed.
    Oracle - DB2 - MS Access -

  6. #6
    Join Date
    Oct 2003
    Posts
    87
    We need your table definitions and SQL in order to help you. Seems to me I'd just timestamp every "product" row and always just insert, no updates. That way you can track the latest timestamp each customer has purchased and then simply select all rows greater that their last purchased timestamp.
    Oracle - DB2 - MS Access -

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •