SearchByExpression extremely slow - is there a way to use the Indexing capabilities and bypass LINQ

JoshL posted on Wednesday, February 25, 2009

I have a number of readonly object lists that have a numeric Id and other properties. Often I look up items by Id. I'd like to index Id so that the lookups are more efficient.

Currently I have a "FindById" function that iterates through the list of objects and returns the first one that matches the input Id.

I'm attempting to utilize the latest CSLA indexing features, and have done the following:
- added the [Indexable] attribute to the Id column
- used the following expression to find the item:
list.SearchByExpression(s => s.Id == searchValue).First()

This is much slower than my original brute-force method of searching: attempting 7000 searches on a 7000-item list, the brute-force method takes 7-10 seconds, while the LINQ method takes 15+ seconds. I have confirmed via the debugger that SearchByExpression is indeed finding the index on the Id column and using it.

I think that the performance hit is in the expression compile required for LINQ. If this is the case, it would be just wonderful if I could make use of the index without using LINQ at all. I know the property that I'm searching on and the value that I want to find. Is there any way of tapping into the CSLA indexing functionality to find a match for the property/value?

Thanks!

Jack replied on Wednesday, February 25, 2009

Out of curiosity - how long does it take the second time round?

How long does it take in LINQ without the index attribute on?  How long the second time round?

I haven't used the index on anything that large yet but I've always done multiple lookups over and over and over while initializing objects and its been on the server vs. the client.

There are default values for how you want the index to be created (OnDemand, IndexModeAlways) etc.  I believe the default is OnDemand but if you were to build the index right away maybe you can do it in the background so it is ready to go when you need to use it.

Jack

JoshL replied on Saturday, March 14, 2009

The test case ran the query 7000 times. The index was built the first time the query was run.

A standard LINQ query (without the indexable attribute) takes 19 seconds to complete; about the same as running it without the indexing.

In order to support id-based searches on my business objects, I've implemented a HashTable-based index that is updated as objects are added/removed from a list. Running the 7000 queries using this index access method is almost instant (0 seconds).

rsbaker0 replied on Sunday, April 12, 2009

I ran into this today also. Even executing a search 350 times (with a different search value each time) produced a noticeable delay.

I replaced the call to SearchByExpression with my own HashTable and got instantaneous results.

I can understand problems if the search expression required a call for each list item, but I'd think that simple equality with a passed string value should be as fast give or take as the hash table.