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

References: <43A4072A-F3A3-43F5-BBFA-30175BD36E89@microsoft.com> <ueZ7YUlQJHA.444@TK2MSFTNGP05.phx.gbl> <234C33A6-975A-4A10-ACD4-1A12DFAA151A@microsoft.com>
In-Reply-To: <234C33A6-975A-4A10-ACD4-1A12DFAA151A@microsoft.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <uG8XJbxQJHA.1164@TK2MSFTNGP03.phx.gbl>
Newsgroups: microsoft.public.dotnet.framework.adonet
NNTP-Posting-Host: perseus.xs4all.nl 213.84.90.73
Lines: 76
Path: border1.nntp.dca.giganews.com!nntp.giganews.com!ne wsfeed00.sul.t-online.de!t-online.de!TK2MSFTFEEDS02.phx.gbl!TK2MSFTNGP01.phx. gbl!TK2MSFTNGP03.phx.gbl
Bytes: 4462
X-Original-Lines: 1
Xref: number1.nntp.dca.giganews.com microsoft.public.dotnet.framework.adonet:147026

smnbss wrote:
> The problem is not the LIKE. This is used on a backend system so I can wait
> a little bit more
>
> The problem is that this
> -- Query generated by LINQ (row.Name.Contains("pattern")) Takes 20 seconds.
> Use Index Seek.
> exec sp_executesql N'SELECT Count(*) FROM dbo.TestTable1 WHERE [Name] LIKE
> @p0', N'@p0 nvarchar(9)', @p0='%pattern%'
>
> take 4 times more than this
> -- Custom query, no parameters: Takes 6 seconds. Use Index/Table Scan.
> exec sp_executesql N'SELECT Count(*) FROM dbo.TestTable1 WHERE [Name] LIKE
> ''%pattern%'''
>
> Anyway, can you send me the code you used to add CONTAINS operator to SQL
> I know this is possible, but we don't have too much time at the moment to
> investigate how to do it

I said: I wrote it for our linq provider, so it's for another o/r
mapper, llblgen pro.

Linq to sql has a similar mechanism. See the Linq to Sql documentation
-> under 'Background information' -> User defined functions -> first
topic about scalar functions. There you'll see an example how to use the
FunctionAttribute to specify a .NET method which is representing a DB
function (in your case the full text search function CONTAINS()). Using
that .NET function inside the query will result in a call to the
CONTAINS() function.

FB

>
> Thanks a lot
>
> Simone
>
> "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in message
> news:ueZ7YUlQJHA.444@TK2MSFTNGP05.phx.gbl...
>> 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.


--
------------------------------------------------------------------------
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#)
------------------------------------------------------------------------