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).
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!
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)