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

[smnbss]

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 *** .


--------------------------------------------------------------------------------
Test Code:
--------------------------------------------------------------------------------

-- Make sure we have up-to-date statistics
UPDATE STATISTICS dbo.TestTable1 WITH FULLSCAN

-- Remove all cached query plans and
-- execution contexts from the plan cache.
DBCC FREEPROCCACHE

-- Custom query, no parameters: Takes 6 seconds. Use Index/Table Scan.
exec sp_executesql N'SELECT Count(*) FROM dbo.TestTable1 WHERE [Name] LIKE
''%pattern%'''

-- Make sure we have up-to-date statistics
UPDATE STATISTICS dbo.TestTable1 WITH FULLSCAN
-- Remove all cached query plans and
-- execution contexts from the plan cache.
DBCC FREEPROCCACHE

-- 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%'


--------------------------------------------------------------------------------
Code to create and fill with TestTable1 table:

--------------------------------------------------------------------------------

-- Drop test table.
DROP TABLE [TestTable1]
-- Columns
CREATE TABLE [dbo].[TestTable1]
(
[Name] [nvarchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
-- Constraints and indexes
CREATE NONCLUSTERED INDEX [IX_TestTable1] ON [dbo].[TestTable1] ([Name]) ON
[PRIMARY]

-- Insert 100 records into temp table.
DECLARE @Table AS TABLE (Id nvarchar(36) PRIMARY KEY)
INSERT INTO @Table SELECT newid() UNION SELECT newid() UNION SELECT newid()
UNION SELECT newid()
INSERT INTO @Table SELECT TOP 96 newid() FROM @Table AS t0
CROSS JOIN @Table AS t1 CROSS JOIN @Table AS t2 CROSS JOIN @Table AS t3
-- Generate 2 000 000 records (8 min).
INSERT INTO dbo.TestTable1 SELECT t0.Id+t1.Id FROM @Table AS t0
CROSS JOIN @Table AS t1 CROSS JOIN @Table AS t2
INSERT INTO dbo.TestTable1 SELECT t0.Id+t1.Id FROM @Table AS t0
CROSS JOIN @Table AS t1 CROSS JOIN @Table AS t2
SELECT Count(*) FROM dbo.TestTable1


Simone Basso

 

[smnbss]

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

Thanks a lot

Simone