Linq to SQL Performance using contains.

Question

I'm overloading a vb.net search procedure which queries a SQL database. One of the older methods i'm using as a comparison uses a Stored Procedure to perform the search and return the query. My new method uses linq.

I'm slightly concerned about the performance when using contains queries with linq. I'm looking at equally comparable queries using both methods.
Basically having 1 where clause to Here are some profiler results;

Where name = "ber10rrt1"

• Linq query : 24reads

• Stored query : 111reads

  Where name = "%ber10%"

• Linq query : 53174reads

• Stored proc query : 23386reads

Forgetting for a moment, the indexes (not my database)... The fact of the matter is that both methods are fundamentally performing the same query (albeit the stored procedure does reference a view for [some] of the tables).

Is this consitent with other peoples experiance of linq to sql?

Also, interestingly enough;

• Using like "BER10%"

• resultset.Where(Function(c) c.ci.Name.StartsWith(name))

Results in the storedproc using 13125reads and linq using 8172reads

Answer 1

I'm not sure there is enough there for a complete analysis... I'm assuming we are talking about string.Contains/string.StartsWith here (not List<T>.Contains).

If the generated TSQL is similar, then the results should be comparable. There are a few caveats to this - for example, is the query column a calculated+persisted value? If so, the SET options must be exact matches for it to be usable "as is" (otherwise it has to re-calculate per row).

So: what is the TSQL from the SP and LINQ? Are they directly comparable? You mention a VIEW - I'm guessing this could make a big difference if (for example) it filters out data (either via a WHERE or an INNER JOIN).

Also - LIKE clauses starting % are rarely a good idea - not least, it can't make effective use of any index. You might have better performance using "full text search"; but this isn't directly available via LINQ, so you'll have to wrap it in an SP and expose the SP via the LINQ data-context (just drag the SP into the designer).

My money is on the VIEW (and the other code in the SP) being the main difference here.