Hosk CRM DEV Tip – Always filter your queries

Is important to keep good habits and keep your CRM solutions zipping along and in most CRM projects there will lots of queries retrieving records related to the main record.

CRM queries should like Bruce Lee one inch punch.  Mean and Lean with no wasted actions

The one inch punch is so awesome it has it’s own wiki page

The one-inch punch is a punching exercise from Chinese martial arts (kung fu) performed at a range of 0–15 cm (0–6 in). The one-inch punch was popularised by actor and martial artist Bruce Lee. It is designed to improve punching power and technique.

Your CRM code should be as simple as possible 

CRM code and CRM queries should be as simple as possible but not simpler

Last year I was doing some optimization and one of the main culprits of poor performance was due to queries not filtering and returning all rows.  There were examples of no filtering in OData in Javascript and plugins.  You can read more about my investigation into CRM performance here

What to look for

  • new ColumnSet(true))
  • Odata queries with no “filter=”

what are the consequences of not filtering your queries, apart from being on Hosk’s naughty list

Poor performance

The downside of not filtering your queries is you are returning fields you don’t need, this will cause the query to take longer to run and bring down more data for every row returned in your query.

The bottom line is your queries are going to take longer and the more rows you return the worse the performance.

Updating all fields which can trigger more actions

When a query has selected all the fields for an entity another mistake the CRM developer can make is to update the record which updates all the fields in the record.  This can

  • Creating confusing audit trails with fields being updated but not changed
  • trigger workflows and other plugins

I have had some conversations with customers asking why the audit record was saying a field had been updated but no one had changed the field.

The triggering of other plugins/workflows can not only have contribute to poor performance but also trigger new records and values being changed.

Code review shaming

Peer reviews are a great way of stopping poor code like this entering your code.  If the CRM Developer knows the code is going to be code reviewed they are less likely to take shortcuts, write poor code because they know it will be found in a code review.

If a CRM developer does write queries without filters then the code reviewer knows they need to be watched because they are a lazy coder.

Lazy coding

In my blog code Bad code is like a virus, don’t get infected I mention the broken code theory.

If the no poor code had been checked in, the CRM developer would see the code checked in is a high standard, the developer understands the code in this project should be a high standard and will follow the herd checking in high-quality code

If the code repository has been infected with bad code the CRM developer will see it’s OK to check in poor quality code, the easiest action for the CRM developer to take is the quick and easy fix, which is of lower quality and will cost you more time in the long run and make it harder to change the code in the future.

I believe you need to keep the coding standards of a project as high as you can and ensure CRM developers adhere to best practise.  Once bad code gets into source control then you will soon find it starts to grow as other CRM developers check in similar quality code.


There is no excuse to not filter your queries so don’t do it.


4 thoughts on “Hosk CRM DEV Tip – Always filter your queries

  1. mwolfaardt October 26, 2015 / 1:31 pm

    I always add the status code in my query as well. Just to limit the result set


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s