General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
APPEND FROM vs ALTER TABLE efficiency
Problem: Need to run update on massive database (several files near 2Gb) that among other things, will add or modify fields and add index tags. No data transforms or data-destructive field attribute changes. Update process uses ALTER TABLE to accomplish this. Benchmarked run times of well over 24 hours.
Possible speedup: Copy system, zap data files in the copy, update this empty system. Then, APPEND FROM existing data.
Discussion: Set up a PC with two fresh, fast hard drives. Put a copy of full source system on one, blank target system and TEMP folder on the other. Using blank target system, APPEND FROM source data across the drives. The theory being that one drive will be read-only and the other will essentially be write-only, and both the reads and writes will take place on "single stripes".
Would appreciate any feedback on the theory and/or implementation. Assuming the theory isn't fatally flawed, specific questions:
-- Would I save more time if I APPENDED without reindexing and then created fresh .CDXs afterwards? (I assume creating them during the APPEND will cause some thrashing and slow things down.) Any gotchas to doing this?
-- Recommendations on how to optimize the disks and controllers -- should I use one controller per drive, set the drives up as master/slave or "separate but equal", etc. Thanx!!
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only