Re: LINQ + .Contains(s) + sp_executesql = big performance problem

References: <43A4072A-F3A3-43F5-BBFA-30175BD36E89@microsoft.com>
In-Reply-To: <43A4072A-F3A3-43F5-BBFA-30175BD36E89@microsoft.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <ueZ7YUlQJHA.444@TK2MSFTNGP05.phx.gbl>
Newsgroups: microsoft.public.dotnet.framework.adonet
NNTP-Posting-Host: perseus.xs4all.nl 213.84.90.73
Lines: 38
Path: border1.nntp.dca.giganews.com!nntp.giganews.com!ne wshub.sdsu.edu!msrtrans!TK2MSFTFEEDS02.phx.gbl!TK2 MSFTNGP01.phx.gbl!TK2MSFTNGP05.phx.gbl
Bytes: 2807
X-Original-Lines: 1
Xref: number1.nntp.dca.giganews.com microsoft.public.dotnet.framework.adonet:147023

smnbss wrote:
> I've discovered that LINQ auto-generated SQL search commands performs
> 3-4 times slower than manually created. And performance is much worse
> with bigger tables (2M+).
>
> I moved fast query on top. Cleaned plan cache, updated statistics but
> LINQ generated query still performs 4*N times slower. I've tested this
> on 5 different SQL servers (i386, x64, v9.0.3282 SP2 CU9) and results
> always the same.
>
> It looks like problem lies in SQL server and not in LINQ. But if it
> affects all databases worldwide then its a bummer. At this moment I
> don't know any workaround for this problem. If you have any info or
> workaround for this issue then please post it here.
>
> Note: Table/Index must have at least 2 000 000 records to see huge
> difference, my guess, it must not fit (be cached) into the RAM). I guess
> LINQ users with big databases are screwed - no FullText support and
> 'LIKE' is slow as *** .

Your post is really about the fact that a LIKE operator on a table with
millions of rows is slow. That's not a new thing, LIKE is in general
slow on a lot of rows.

It should be possible to create a function call mapping for Linq to sql
for the CONTAINS operator for full text search. At least that's how I
added full text search in our linq provider, and as Linq to Sql also has
some form of .net method to db function mapping system, you could give
it a shot.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------