Probably the simplest thing you can do with the greatest effect is to drop the indexes on the forms into which you are importing data. Once you hit a few hundred thousand rows, the task of updating the indexes after each insert can take longer than the insert itself. Add the indexes back manually after your import task is done. They will rebuild in only a fraction of the time it would take them as part of the import process.
Rick On Thu, Sep 13, 2012 at 6:29 AM, Veeral Oza <[email protected]> wrote: > ** > Hi Jiri, > > Thank you for your reply. We can migrate 2000 records in 1 hour. > We are on MSSQL 2008 R2 Enterprise. > I would really like to explore your idea, about, "Regarding using > multiple import tools, have you tried configuring private server queues for > each and running the parallel import tools each connected to its own > private queue?****" > I never knew this was possible. I will check out the documentation on how > to achieve this. > > Regards, > Veeral Oza > > On Thu, Sep 13, 2012 at 3:52 PM, Jiri Pospisil < > [email protected]> wrote: > >> ** >> >> Hi,**** >> >> ** ** >> >> You say there is no room for code optimisation. But still worth asking ... >> **** >> >> Have you tried to just import a single record and evaluate how long each >> operation takes to find the most expensive ones and see if they can be >> improved?**** >> >> Are there any SQL operations (searches/inserts/updates) that would >> benefit from adding/removing/optimising indexes? You do not mention what DB >> you are using, but would DBAs be able to optimise the DB for data load?** >> ** >> >> Is there any workflow on the target server which could be disabled?**** >> >> Regarding using multiple import tools, have you tried configuring private >> server queues for each and running the parallel import tools each connected >> to its own private queue?**** >> >> ** ** >> >> Just a couple of thoughts. Hope of some use.**** >> >> ** ** >> >> Regards**** >> >> Jiri Pospisil**** >> >> ** ** >> >> *From:* Action Request System discussion list(ARSList) [mailto: >> [email protected]] *On Behalf Of *Veeral Oza >> *Sent:* 13 September 2012 11:11 >> *To:* [email protected] >> *Subject:* Legacy Data Migration from 6.3 to IM 7604SP2**** >> >> ** ** >> >> ** **** >> >> Hi All,**** >> >> **** >> >> We are doing data migration activity through import tool for a customer. >> The target is to migrate 1.5 million records in next 1 week.**** >> >> We started off this activity a few weeks back and were able to complete >> 1/2 or 0.5 million records until now. Go-live date is approaching and now >> we need to find a way to speed up the migration activity.**** >> >> * ***** >> >> *Background:***** >> >> 1) 1.5 million 6.3 HPD and CHG records are required to be migrated.**** >> >> 2) Custom mappings created for import tool.**** >> >> 3) Custom filters written to populate some fields, create SR records, >> Worklogs, etc..**** >> >> * ***** >> >> *Activities done so far to speed up the migration:***** >> >> 1) Archived HPD and SR records, so that HPD:Helpdesk table would become >> lighter.**** >> >> 2) No scope for further code optimization.**** >> >> 3) No scope for further reducing the network latency.**** >> >> 4) Parallel imports through multiple machines throw unwanted timeout >> errors. *Is this a recommended approach?***** >> >> 5) Will disabling escaltions improve migration speed? We tried this on >> Dev server and found that there is no significant improvement.**** >> >> * ***** >> >> *We are trying the below steps:***** >> >> *1. Increase the JRE run time >> parameters on the laptop\DeskTop in the registry. By default the >> parameters are >> low. To accomodate the larger data items of the BMC Import you will need >> to set >> the -Xms to a value of 512m and -Xmx to value of 2048m.(32 bit >> requirements of >> JRE run time will not allow it be set higher) >> >> 2. In the .bat and .ini files of the Import Tool you will need to match >> up the >> same parameters in each file.***** >> >> *3. In the Chunking value of the tool you can increase as well so the >> Bulk* >> *Transaction is not used as much so the memory does not gather up too >> large. 500* >> *to a 1000 is a good setting for this.***** >> >> **** >> >> Can anyone please suggest any more ways to improve the migration speed?** >> ** >> >> **** >> >> **** >> >> Regards,**** >> >> *Veeral Oza***** >> >> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ **** >> >> >> >> A copy of the LCH.Clearnet e-mail disclaimer can be found at: * >> www.lchclearnet.com/disclaimer/email* >> >> >> >> LCH.Clearnet Limited, Registered Office: Aldgate House, 33 Aldgate High >> Street, London EC3N 1EA. >> >> Recognised as a Clearing House under the Financial Services & Markets Act >> 2000. Reg in England No.25932. >> >> LCH.Clearnet SA, Siège Social, 18 rue du Quatre Septembre, 75002 Paris, >> Chambre de Compensation conformément au Code Monétaire et Financier. >> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
