1. Always specify a column set
This is a no-brainer, or at least it should be, always specify a column set when performing a query otherwise you're pulling back every column in the table every time. The amount of times I see the following in code is not funny:var cols = new ColumnSet(true);I've even been a perpetrator myself! We really need to get better at specifying our columnsets. Breaking it down to basics, specifying a column set can mean the difference between this:
select * from contact
and this
select contactid, fullname, birthdate from contact
I have seen companies hit the max columns on either contact or account, so that might just give you an idea of what sort of impact specifying all columns will have.
2. Nolock = true
Unfortunately, this is only available on the QueryExpression or via FetchXml in a FetchExpression object. Be aware that this tip isn't necessarily as black and white as "always do this", as there are situations where it will not be feasible.The upside of setting NoLock = true is that it won't request a record lock during the SQL read and therefore should neither block nor get blocked from making the call. This not only makes the call faster, but makes load balanced servers more efficient as they can read the same records without waiting on the data to be freed from a lock.
There is a downside to setting this which is the possibility of reading uncommitted data or even duplicate data. Depending on what you are reading you may or may not care about this, so you'll need to make a judgement accordingly.
For example, imagine I was reading a settings table to find out if I should display a field in red or blue. I happen to read some uncommitted data that changed the colour and was subsequently rolled back. This usually wouldn't be considered detrimental. In fact, this is probably a good example of when you should consider using NoLock.
On the other hand what if you were reading something more crucial? For example you read an uncommitted price of a stock on the market that in turn caused you to price an option incorrectly due to uncommitted data being rolled back. Probably an unrealistic scenario, but definitely something you would consider as a bigger issue!
The question you need to answer before applying this is - which is worse...
- A deadlock
- Inaccurate data
If inaccurate data isn't a huge problem then NoLock is going to offer a favourable solution.
3. OrganizationService.Retrieve locks the record!
This leads on from the previous point, if you perform an OrganizationService.Retrieve you'll end up locking the record on the database. If the retrieve doesn't need to lock then use a QueryExpression (or FetchExpression) to perform a NoLock and retrieve the record that way instead. This will return an entity collection which you'll just need the first record from, but if performance is king this shouldn't be a problem.4. LINQ locks too!
Again, as with Retrieve, LINQ doesn't allow you to specify a NoLock. Which means you need to use a Query Expression (or FetchExpression) instead. This is particularly annoying as LINQ is such a beautiful way to access CRM, but such a pity you can't gain access to the QueryExpression it creates under the hood. (Well... you can... but not without performing some reflection to invoke a private method... let me know if you'd like to know how to do this)5. Indexing
Finally, slightly beyond the realm of coding, but still something you could do - take a look at indexing in the database or lack thereof. If you have a DBA team these guys will be able to generate reports on missing indexes for you. On the other hand, if you know of fields that you are constantly searching / filtering then consider adding them to the quickfind views which will in turn create indexes in the background. Getting a DBA to add coverage is ultimately better, but quickfind is a quick (be it slightly dirty!) way to get a field indexed.Take care though, indexes can deplete performance on other operations, such as inserts. So don't start adding indexes willy nilly all over the database! Something to be aware of.
