none
RemoveMemberListRequest causes SQL time-out error

    Question

  • Hi all,

    I've run in to a dead end here. We have implemented a custom process to add and remove members from a marketing list by selection a number of contacts to add from specific zip codes. A post plugin registered on the marketing list executes on create and update, at maintains the members on the particular list based on the values the user has provided.

    We can't use the standard process for this, since the actual contacts being selected from a given zip code might not be all of them and furthermore must be added randomly, ie. if there's 5.000 contacts in zip code A, the user would like to add 2.000 of them to the list. The 2.000 should be added by randomly selecting 2.000 contacts amongst the 5.000. This is not possible using advanced find, so the standard process is out of the question.

    The user has the option to first add fx. 2.000 and then later on reduce this number. When adding members, we use the AddListMembersListRequest, which takes a ListId of the marketing list to add to and a collection of id's of the members to add. This works fast and without errors.

    Unfortunately there's no equivalent request to remove a collection of members. This must be done one at a time using the RemoveListMemberRequest. And here's where it crashes. If we need to remove 2.000 members from a list, we need to execute 2.000 RemoveListMemberRequests. In our code, we use the new ExecuteMultipleRequest to package all the individual request into batch of no more than 1.000 requests. However, no matter if we want to remove just 1 or 1.000, an SQL time-out error is thrown when executing the ExecuteMultipleRequest. In the eventlog the following entry is added after aprox. 30 seconds:

    Query execution time of 30.9 seconds exceeded the threshold of 10 seconds. Thread: 42; Database: RMMPRODCOPY_MSCRM; Server:KBNDVCRM32\DEV01; Query: select

    "list0".Type as "type"

    , "list0".LockStatus as "lockstatus"

    , "list0".ListId as "listid"

    , "list0".OwningBusinessUnit as "owningbusinessunit"

    , "list0".OwnerId as "ownerid"

    , "list0".OwnerIdType as "owneridtype"

    from

    ListBase as "list0"

    where

    ("list0".ListId = '287ddd17-e92d-e411-9409-005056924289').

    The plugin is running synchronously and appearantly, CRM tries to query the marketing list record from the database when it executes a RemoveListMemberRequest, and this query causes a time-out and in turn throwns an exception in the plugin.

    So, we're kinda stuck here, and before we start redesigning or hacking away at something unsupported solution, I wanted to hear if anyone in here has some bright ideas.

    Thanks!

    /Dennis Moesby

    Thursday, August 28, 2014 11:32 AM

All replies

  • Looking at the numbers of records I think for CRM it should not be any problem. Retrieve records in batches of 500 and then use RemoveListMemberRequest using ExecuteMultiple.


    Regards Faisal

    Thursday, August 28, 2014 1:23 PM
  • Hi Faisal,

    Thanks for your input. We are in fact doing exactly what you're suggesting already. The problem is that an SQL time-out error is thrown when we execute the ExecuteMultiple request through the service. So what we need is a different way to do it, since the error is thrown no matter if we remove 1 or 1000 members from the list. Same error. The marketinglist record is probably being locked in the database - and stays locked because we run the plugin synchronously (all though this should mean that it's outside the transaction scope), so when the service tries to query the db, it waits for the record to become unlocked.

    I think it should be easy enough for anyone to reproduce: Just register a synchronous post plugin on the marketing list entity, which removes a member from the marketing list -> SQL time-out.

    Then in the same plugin, try to add one or more members to the list: No time-out error.

    What to do?

    Thanks in advance.

    /Dennis

    Thursday, August 28, 2014 2:37 PM
  • Have you tried running the plugin asynchronously, and is it acceptable to have it run asynchronouly - that should avoid any locking issues


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

    Thursday, August 28, 2014 3:04 PM
    Moderator
  • We have tried to run it asynchronously and it works without error. However, we would like to avoid it, because we need the process to be finished before the user should be allowed to make any other changes to the list. If we have to, we will handle it via changing the status and thus locking the form based on the status, but we would rather find a way to make it work. Looking at the standard functionality, you're able to remove members from a list using advanced find. This occurs in a pop-dialog and the process is completed quickly and before you are returned to the form. I am curious as to how CRM does it using the standard functionality, because using advanced find to remove fx. 10.000 members from a list happens within a few seconds. Much faster, than what we can accomplish using the RemoveListMemberRequest even when combined with a ExecuteMultiple request. So there must be a way to remove a collection of members from a list as fast as you can add them (ie. an remove equivalent to the AddListMmembersListRequest).

    DB-wise, removing members from the list is a simple process. It's deleting a row in the ListMemberBase-table. However the ListMember entity does support executing a DeleteRequest on it, so that doesn't work as a workaround. Obviously it would be an unsupported hack to implement custom code, which deletes the rows in the table - and it would also potentially break any workflows or plugins, which may be executed when a member is removed from a marketing list. So we don't want to do that. For the time being, we've supressed the user's ability to remove members through the custom selection. This is to avoid the error poping up. But this is really not suitable in the long run.

    Thanks for your input.

    /Dennis
    Thursday, August 28, 2014 3:27 PM
  • Thursday, August 28, 2014 3:49 PM
  • Unfortunately increasing the timeout setting wont fix it. I tried executing the query manually through SQL server studio manager when I was debugging the plugin at was halted at a breakpoint. The query just waits untill the plugin has finished, so no matter what timeout I set, it will never complete while being in the plugin. I think that we're dealing with a bug in the service or something.
    Friday, August 29, 2014 7:11 AM