This is the third post in a 8 part series describing the most common errors made by Dynamics CRM developers. It will cover:
Issue #3 - Querying for all columns
This issue is very obvious, but unfortunately still quite common. Whoever has any SQL experience knows that SELECT * is (almost) never a good idea. Why query for data you don't need?
Exactly the same rule applies for CRM. Do not retrieve data you don't need. If you need 3 fields from an entity to perform some calculation, retrieve those 3 field. Not one more. The bigger the number of columns queries, the slower the query will perform. How much slower? Significantly.
Real life example:
"Large" entity with ~200 fields and ~100.000 records. Connection over internet.
Retrieve top 5000 records with all columns - 59 seconds
Retrieve top 5000 records with just 1 column - 1.2 second!!!
Yes, the difference is almost ... fifty times.
If this doesn't convince someone - I don't know what will ;)
There are off course some exceptions, like when you build a custom auditing or duplicate detection solution. But even then it's not always needed to retrieve all columns.
To summarize:
Exactly the same rule applies for CRM. Do not retrieve data you don't need. If you need 3 fields from an entity to perform some calculation, retrieve those 3 field. Not one more. The bigger the number of columns queries, the slower the query will perform. How much slower? Significantly.
Real life example:
"Large" entity with ~200 fields and ~100.000 records. Connection over internet.
Retrieve top 5000 records with all columns - 59 seconds
Retrieve top 5000 records with just 1 column - 1.2 second!!!
Yes, the difference is almost ... fifty times.
If this doesn't convince someone - I don't know what will ;)
There are off course some exceptions, like when you build a custom auditing or duplicate detection solution. But even then it's not always needed to retrieve all columns.
To summarize:
How to spot this?
Whenever you see:
- select 'theObject' in LINQ queries
- new ColumnSet(true) in QueryExpression
- <all-attributes/> in Fetch XML
... you can sense that something is wrong.
