Tip #609: Higher SQL compatibility level does not mean better

Gayan “Performance Wizard” Perera continues to live up to his nickname with another awesome performance tip. tl;dr Beware setting the SQL database compatibility level to a level higher than 110 on a CRM 2016 system. The scenario You have an entity with more than 5 secured fields. When the record is opened it either times […]

Tip #405: Convert Your SQL To FetchXML The Easy Way (or Cai)

Kingswaysoft has recently updated their SQL2Fetch Convertor Let’s say you are moving from CRM On premises to CRM Online and you want to update your reports to work with CRM Online, you can take the SQL query from your report, paste it in the SQL box and click the “convert” button to generate FetchXML. This […]

Tip #240: Once bitten, twice filtered

Anything that’s been done twice is usually better. That includes double cream and double barreled. Except that report pre-filtering does not work if you need a filtering entity to be included more than once in your SQL. Consider this totally random piece of SQL: You cannot just apply pre-filtering because alias CRMAF_FilteredAccount cannot be used […]

Tip #219: You want the top one

One invoice only

One of the challenges of creating reports is the lack of real data in development environment. There is not enough Nancies Davolios to simulate real volumes. For that reason alone production organization is often replicated “back” to test and development environments. Reports suddenly come to life and designers can see 7-figures opportunities and otherwise empty […]

Tip #216: In a parallel dimension

I’ve had the following discussion with two different CRM administrators in the past week. We’ll call them “Larry.” Larry: “My CRM organization is very slow. I try to search for records and it takes 25 seconds. I tried to import an organization, and it took 25 hours to complete.” Me: “What is your Max Degrees of Parallelism setting in […]

Tip #39: Custom indexes in CRM Online

A little known secret for CRM Online users and a good reminder from Daniel Cai. If you’ve added additional search fields to your Quick find or other views, the underlying entity contains a large number of records, and the response time is slow, you might need a custom SQL index added to improve the performance. […]