locked
CRM 2013 SQL Data Updating vs Console App vs WorkFlow Approach RRS feed

  • Question

  • We have few console/workflow created to update some fields of Account Entity. they work great , but they are limited to 5000 records processing at a time which i can work around, but they take tremendous amount of time due to having more than 100,000 records.

    So i am thinking to do SQL statement update directly instead which i guess take less than 10 seconds.
    Any recommendation whether this approach is appropriate or does any one have done this other way?
    Thursday, June 26, 2014 1:35 PM

Answers

  • You can also bundle multiple operations in one call, using ExecuteMultiple. This can have a significant performance benefit if you have noticeable latency with connections to the Crm Server (this is most significant if using Crm Online)

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Marked as answer by Nicksoft2011 Wednesday, July 23, 2014 3:00 PM
    Friday, June 27, 2014 12:00 PM
    Moderator
  • Hi,

    Unfortunately the SQL statement way is unsupported and with good reason.

    Have a look at SQL profiler when you update something. Sometimes loads of other tables are also updated.

    As to your data updates, multi-Server, multi-threaded approach may speed up your updates. Additionally you could use SQL queries on the views to return keys, which when you use them on updates  will increase your through put substantially.

    One other thing that you may have found out already. Remove your static methods from the helper classes (ie make them thread safe) that come with the SDK. 1000% increase throughput last time i tested.

    Hope this helps

    Don

    • Marked as answer by Nicksoft2011 Wednesday, July 23, 2014 3:00 PM
    Thursday, June 26, 2014 8:49 PM

All replies

  • Hi,

    Unfortunately the SQL statement way is unsupported and with good reason.

    Have a look at SQL profiler when you update something. Sometimes loads of other tables are also updated.

    As to your data updates, multi-Server, multi-threaded approach may speed up your updates. Additionally you could use SQL queries on the views to return keys, which when you use them on updates  will increase your through put substantially.

    One other thing that you may have found out already. Remove your static methods from the helper classes (ie make them thread safe) that come with the SDK. 1000% increase throughput last time i tested.

    Hope this helps

    Don

    • Marked as answer by Nicksoft2011 Wednesday, July 23, 2014 3:00 PM
    Thursday, June 26, 2014 8:49 PM
  • You can also bundle multiple operations in one call, using ExecuteMultiple. This can have a significant performance benefit if you have noticeable latency with connections to the Crm Server (this is most significant if using Crm Online)

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Marked as answer by Nicksoft2011 Wednesday, July 23, 2014 3:00 PM
    Friday, June 27, 2014 12:00 PM
    Moderator