> Den 2011-08-10 12:52 skrev Thomas Steinmaurer såhär: >> > We're going to publish data from a large database into a smaller one. >> > Primary keys and othe rindexed values will probably be inserted in >> > random order. >> > >> > Is it more efficient to add indexes/pk:s after the insert than before? >> >> Yes. > > Thanks. To separate DDL and DML in my code I'd still like to create > indexes where I create the tables. So, excepting PK:s, I may want to > just deactivate indexes while pumping the data and the reactivate them.
You can't deactivate the underlaying index of a primary key constraint. > Will this have performance as if I deferred index creation to after > pumping the data? Activating an index rebuilds the index. > I think I can live with the performance hit of having the PK index > active during data pump. The PK:s are bigints. Is this a bad idea? I > think there will be 5-10 million inserts in total across all tables, the > largest three tables containing about 1 million records each. It would be good if you re-calculate the selectivity after the import for those indices, which have been active during the import. I have a stored procedure in all my databases which execute a SET STATISTIC on all indices. -- With regards, Thomas Steinmaurer Upscene Productions http://www.upscene.com http://blog.upscene.com/thomas/ Download LogManager Series, FB TraceManager today! Continuous Database Monitoring Solutions supporting Firebird, InterBase, Advantage Database, MS SQL Server and NexusDB! ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
