Tuesday, October 27, 2015

Querying with no table lock

By default when you're querying the MSCRM database the transaction lock the data used by the query. If you're doing several parallel queries on the same data the locking mechanism can highly increase the data retrieval.
You can configure the query to not use the lock mechanism.

First of all, be careful ! Table lock is an essential database functionality which without a multi-user environment could not work.
What is it ?
A database lock is used to “lock” some data in a database so that only one database user/session may update that particular data. So, database locks exist to prevent two or more database users from updating the same exact piece of data at the same exact time. When data is locked, then that means that another database session can NOT update that data until the lock is released (which unlocks the data and allows other database users to update that data. Locks are usually released by either a ROLLBACK or COMMIT SQL statement.
Source : Programmer Interview

Once you are aware of that, you can use the no-lock clause to increase the performance of your queries.

Two methods, first with Query Expression, simply add the NoLock property to true :
var query = new QueryExpression
  {
    EntityName = "entitylogicalname",
    ColumnSet = new ColumnSet(true),
    NoLock = true
  };
EntityCollection ec = _orgService.RetrieveMultiple(query); // Where _orgService is an instance of IOrganizationService

With FetchXML, add the no-lock attribute in the fetch node :
<fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='true' >
  <entity name='account'>                                                                
    <all-attributes />
  </entity>
</fetch>

Sources :
MSDN - QueryExpression.NoLock Property
MSDN - FetchXML schema