Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    87

    Exclamation Unanswered: Best Method to update table...

    Hy everyone.
    I've got a little question regarding the speed of an update query...

    situation:
    I've got different tables containing information wich i want to add to one big table trough a schedule (or as fast as possible).

    Bigtable size:
    est. 180000 records with 25 fields (most varchar).

    Currently I've tried two different methods:
    delete all rows in the big table and add the ones from the little tables again. (trough union all query)
    -> Speed ~ 15 Seconds

    refresh all changed rows (trough timestamp <>) and add new titles (trough union all query)
    -> Speed ~ 20 Seconds

    Does anybody know a faster solution? The union queries block the table for those 20 Seconds...

    Thanks for any reply!

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Re: Best Method to update table...

    RE: situation: I've got different tables containing information which i want to add to one big table trough a schedule (or as fast as possible).
    Bigtable size:
    est. 180000 records with 25 fields (most varchar).

    Currently I've tried two different methods:
    delete all rows in the big table and add the ones from the little tables again. (trough union all query)
    -> Speed ~ 15 Seconds

    refresh all changed rows (trough timestamp <>) and add new titles (trough union all query)
    -> Speed ~ 20 Seconds

    Q1 Does anybody know a faster solution? The union queries block the table for those 20 Seconds... Thanks for any reply!
    A1 Maybe.

    As with many things, it depends on the requirements. For example, some possible considerations may include various permutations and combinations of any of the following: (not an exhaustive list)
    a using a lower isolation level for the union queries, and conditionally unioning only updated tables
    b implementing triggers to update the target as dml is commited at the source tables
    c a create, populate, and rename table scheme (dropping the old table)

Posting Permissions

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