Hi Toby,

What version are you looking at? There have been quite a few changes to Communication views in the last couple of years. There were a few performance problems around about v7.1 SP1 that were resolved in patches.

I guess the reasoning behind this is that all columns for your entity are available for use when you're doing your own customisations - you don't need to create or modify views if you want to add an additional column to a screen.

It's certainly possible to change a system view, but you're likely to run into the following issues:

1: All required fields mightn't be included in your modified view if you're enumerating fields. It's easy to miss a screen or system action that uses the view.

2: The modified view might be wiped out on upgrade, or return incorrect data if the underlying schema is changed.

3: The view might need to be modified if you add a new field.

4: If changing the view logic (as opposed to just the field listing) you might return different results to the vanilla view. Hilarity will ensue.

That said, it's done fairly regularly, though I don't really recommend it.

Alternative options:

1: Create a new view for your screen. If you want to replace a system screen (or list) you can often build a new one relatively quickly using the COM API. I wouldn't change the target view for an existing CRM screen.

2: Check your version. Live I said, there's been a fair bit of work in this area. If the issue is really a problem with the SQL statement rather than anything else, I'd be inclined to think of it as a bug, to be fixed.

3: Check your indexes. You can set up sorting on your indexes whatever way you want - the only problem is that the record lookups back to the clustered index might kill performance if the index isn't covering, and you're pulling back a lot of records.

Can you post up an example SQL statement and execution plan so I can take a quick look? You should be able to export the execution plan to XML format.

Thanks,

Rob

Hi Rob,

Thanks very much. I am reluctant to start messing with the view too much, but analysing the longest queries to run, anything with vListCommunication is always on top, every day.

The version of CRM is v7.1.f.s.

Been on top of the indexes, they are in good condition, and the extents are looking real sweet.

I have looked into explicitly stating hash joins, but, that made it worse in honesty, and as you said, I do not want to mess with the view too much, as I want to ensure the view returns the correct data.

Attached is the execution plan, saved as a .sqlplan. As you can see, the sorts are quite costly, and reducing the select omits these sorts. Alternative to cutting the select, would be to look at compound indexes, so that the query doesn't have to hit the table as much, if at all.

Only other thing I can think to look at here, is how the index is handling the CRMEmailPhoneData table, as it is going off to it twice, which could be duplicating workload.

Thanks for your help on this, whilst it is interesting, it sure is a bit of a head scratcher.