Slow search for items using extended property on Exchange

Question

Problem at hand

Our C# Windows application uses EWS Managed API 2.0 to create appointments in a user's calendar. Each appointment has an extended property with a unique value. It later locates an appointment using FindItems and an ItemView.

Users experience significant delays the first time this search is performed. Subsequent response times are entirely acceptable.

("first time" is a little vague here, because users may experience the delay again later in the day)

// locate ID of appointment where extended property value equals 1234:
var filter = new Ews.SearchFilter.IsEqualTo(extendedPropertyDefinition, 1234);
var view = new ItemView(1, 0);
view.PropertySet = BasePropertySet.IdOnly;
var folder = new FolderId(WellKnownFolderName.Calendar, new Mailbox("..."));
var result = service.FindItems(folder, filter, view);

Remote server is an Exchange Server 2007 SP1.

Research

MSDN ties some comments to search folders and restricted views, however I am uncertain if these apply to our situation.

The act of applying a view to a folder creates search folders in the store. When a search folder is created, it is cached for later use. If a user tries to create a search folder which already exists, the cached search folder is used. This allows future viewings to be fairly quick. By default, Exchange does not cache all search folders indefinitely.

Specifically with regard to EWS:

It is also important to be aware of the fact that the first time an Exchange store search query is issued, it will run very slowly and possibly time out, whereas on future runs it will respond without issue. This is caused by back-end processes that occur on the Exchange server when a store search is performed.

They suggest creating search folders for non-changing, non-dynamic queries, which doesn't seem fitting in our case, since the query is different for each appointment.

If an application requires a specific query that has a fixed set of nonchanging parameters, you can use search folders. [...] search folders are useful only for nonchanging, nondynamic queries.

What we need is in essence to create an "index" - in database terms - on the property, ensuring that all searches on this specific property are fast, no matter the time or frequency.

Is it possible to "index" this property? Can anything be configured either client or server side to remove this initial delay?

Answer 1

I've hit the same sort of problem with an integration project. I wish there was a good solution...

You cannot create an index for a property that is not already indexed by Exchange. Creating a search folder for each is not viable if the number of Appointments grows high enough. Too many search folders on a single folder will cause further problems as they will all need to be updated when a new item is added to the folder. That's my understanding, at least. Also, Exchange 2007 is limited to 11 dynamic search folders per parent folder, so it may be even less viable depending on the number of appointments and how often they're accessed. Using existing indexed properties may not be viable as these can likely be changed by the user outside of your application. If you have some way of ensuring that the Appointments you create can only be accessed or altered from your application, then that's a different story.

The database table is a good way to go, but there's a potential snag that some people don't see until it's too late. ItemId is the obvious choice to link to your extended property, but ItemId is NOT constant. It's a calculated property based on several others. It can change if the item is moved to another folder, and it may also change with the installation of a service pack or given enough time passing, or so I've heard. I can confirm at the least the first one. ItemId is not viable for long-term storage, at least not without additional checks. You could potentially store ItemId and your extended property. If a bind using the ItemId fails, fall back to the extended property search. If the bind is successful, then check it against the extended property in the database to be certain that it matches. Update the ItemId once you have the item if it doesn't match up. Do you need to work with anything beyond the Appointment objects, ie, meeting responses, forward notifications, etc, or is this concerned only with the Calendar?

It isn't pretty, but it should be a somewhat reasonable compromise. You may still have the occasional search, but they should be few and far between as long as the user doesn't decide to move Appointments to different folders or plans some Appointments way in advance, and even then the sync should help mitigate that as well. Just be prepared to repopulate that table if there are upgrades to Exchange.

Of course, if Microsoft had either added the capability to index additional properties or even added a blank string field or two to the index in Exchange Search for this very purpose, we wouldn't have this problem. Heck, an index on the GlobalObjectId properties on Appointments and associated objects would help, but alas...no. I'm not a fan of repurposing existing indexed fields. Not all of them are applicable to Appointments and the ones that are tend to be either required or editable by the user. Unless you know precisely what you're doing, repurposing those fields could potentially have unforeseen consequences down the road.

In any case, I don't claim to be an expert in all matters of EWS/Exchange, so maybe there is a better way than this. Take it with a grain of salt.

Answer 2

There isn't a way to switch on indexing for your property. I'm not familiar with which properties are indexed in Exchange 2007. Since your application appears to be using appointments, perhaps you could repurpose one of the other non-appointment properties to store your unique value. Perhaps use the AssistantName property via an extended property (to work around restrictions imposed by the EWS schema and service). This way, most clients will not be using that property for calendar items.

According to this topic, http://technet.microsoft.com/en-us/library/jj983804(v=exchg.150).aspx, that property is indexed (in 2013). That property has existed for a long time so it may be indexed for 2007.

Hey, this is a long shot, and not optimal by any means, but perhaps it might work for your scenario.

Answer 3

After reading this thread some more, I see that you are not looking for all items with your Extended Property but a specific item. Sorry I didn't catch that in my first response. I agree that the Search Folder alone would not work for you, since you would be required to update the filter each time you were searching for your item. This would obviously be pretty expensive (probably worse than your current approach). One idea I have is creating a View that sorts by your Extended Property. I could be wrong, but I believe you can apply this view to the above Search Folder (note that I'm talking about explicitly creating the Search Folder and View and storing them in the mailbox, they can be hidden or exposed to the OL UI under the Search Folders tree). The search folder would filter only Appointments that have your Extended Property. Then the View would sort the folder by the property value. In some reading I've been doing on the ESE internals, I've seen some commentary that indicates that sorting by a property will cause Exchange to create an index on the ESE (wish I could find it now). The section on ESE B-Tree Indexes seems to confirm this: http://books.google.com/books?id=12VMxwe3OMwC&pg=PA73&lpg=PA73&dq=how+to+create+exchange+ese+indexes&source=bl&ots=D5hJyJIEo5&sig=ppZ6RFJh3PnrzeePRWHFJOwXgeU&hl=en&sa=X&ei=QQ7HUtgggvTbBdjcgfAP&ved=0CFwQ6AEwBQ#v=onepage&q=how%20to%20create%20exchange%20ese%20indexes&f=false

You'd then have to use the same approach you used above on the Search Folder to find the specific item matching your criteria. One challenge of course is the issue with Exchange throwing away your index (which is probably what is happening in your current approach). Perhaps you could programmatically touch the search folder periodically to ensure that this doesn't happen? This link is also helpful to understand the performance impacts of creating a Search Folder/View: http://technet.microsoft.com/en-us/library/cc535025%28EXCHG.80%29.aspx

If you find a good solution (or this one works), I'm very interested to hear about it (and I'm sure many others are too). Oh the joy of Exchange Development :-)

Answer 4

Creating a search folder with your extended property as the criteria is the way to go. You'll pay the price while the search folder builds initially, but after the index is created as long as the folder exists and is running it will be updated automatically by Exchange. We use this technique quite successfully to find the proverbial "needle in a haystack".

http://msdn.microsoft.com/EN-US/library/dd633687(v=exchg.80).aspx