locked
How to get 1.1 million simple records into CRM 2011 in under an hour? RRS feed

Answers

  • If you are of the mindset that 'direct SQL inserts are verboten in ALL instances', the best success I have had so far is using Scribe with multiple parallel clients. Basically I would break the input data into X similarly-sized 'chunks' using a variable passed in to the scribe package (using mod X or something similar on an identity field for example to split the data for parallel processing).

    I found I could run a few instances of the scribe workbench on my laptop before saturating my network/CPU/disk, and then adding a second machine running a few more workbenches scaled up very well. Obviously you will hit the limit somewhere, and your environments may vary.

    In my simple test case I peaked out at 227 records per second, which I think equates to 80 minutes for 1.1 million records. That is with one scribe server and two scribe client machines each running 4 instances of the scribe workbench. The CRM server was about 65% utilized at that point, so there was still some headroom as well, and the CRM server was not extremely powerful, so if you have a very strong CRM server, your mileage will certainly vary...watch out for network saturation as well :)

    Ken

    Tuesday, January 31, 2012 5:03 PM
  • You can have a look at deep inserts - associate or push records together and save changes in bulk considering you have few fields and one entity.
     
     
     
    Tuesday, January 17, 2012 11:19 AM
  • Hi Jamie,

    Daniel Cai just released a new data integration tool that is built on SSIS.  I have not tried it so not sure how fast it is. 

    Since this is only one entity with no relationships, you could also try the CRM import wizard.  Again, I haven't tested it for speed in this scenario.  There is also Scribe and Cozy Roc.  I would run a test with all solutions to see which is best to meet this requirement.  If nothing more, it would make a great blog article :)

    Best Always


    Regards, Donna

    Monday, January 30, 2012 12:42 PM

All replies

  • You can have a look at deep inserts - associate or push records together and save changes in bulk considering you have few fields and one entity.
     
     
     
    Tuesday, January 17, 2012 11:19 AM
  • Hi Jamie,

    Daniel Cai just released a new data integration tool that is built on SSIS.  I have not tried it so not sure how fast it is. 

    Since this is only one entity with no relationships, you could also try the CRM import wizard.  Again, I haven't tested it for speed in this scenario.  There is also Scribe and Cozy Roc.  I would run a test with all solutions to see which is best to meet this requirement.  If nothing more, it would make a great blog article :)

    Best Always


    Regards, Donna

    Monday, January 30, 2012 12:42 PM
  • If you are of the mindset that 'direct SQL inserts are verboten in ALL instances', the best success I have had so far is using Scribe with multiple parallel clients. Basically I would break the input data into X similarly-sized 'chunks' using a variable passed in to the scribe package (using mod X or something similar on an identity field for example to split the data for parallel processing).

    I found I could run a few instances of the scribe workbench on my laptop before saturating my network/CPU/disk, and then adding a second machine running a few more workbenches scaled up very well. Obviously you will hit the limit somewhere, and your environments may vary.

    In my simple test case I peaked out at 227 records per second, which I think equates to 80 minutes for 1.1 million records. That is with one scribe server and two scribe client machines each running 4 instances of the scribe workbench. The CRM server was about 65% utilized at that point, so there was still some headroom as well, and the CRM server was not extremely powerful, so if you have a very strong CRM server, your mileage will certainly vary...watch out for network saturation as well :)

    Ken

    Tuesday, January 31, 2012 5:03 PM
  • Keiman,  that's pretty close to what we are doing at this point.  Just not with Scribe.  We are using a multi-threaded application.  It's still a little slow.  We also tried all of the other items suggested minus Cozy Roc.
    Jamie Miley
    Check out my about.me profile!
    http://mileyja.blogspot.com
    Linked-In Profile
    Follow Me on Twitter!
    Tuesday, January 31, 2012 6:25 PM
    Moderator
  • Out of curiousity, what have you all found to be the bottleneck in your case? In my brief testing, I never maxed out the CRM Server or the DB, it seemed to be purely limitations on the client machines running the import process, either CPU or network I/O, which was initially resolved and started scaling again by adding a second client machine. Granted that can only take you so far, but it seems like the speed you are looking for should be achievable as I just stopped trying with two clients and did not take it any further...

    Wednesday, February 1, 2012 4:53 PM
  • recently, we succeeded in loading 1.3 to 1.9 million records using cozyroc crm destination component. This load was mostly an insert into one of the CRM entities. 
    Monday, July 23, 2012 12:59 PM