LINQ to SQL Performance Comparison

Question

I have a problem with linq to sql query - performance is wise. What I'm trying to do is find out if the items in my collection match about 500 items (in the list) of the db record. Currently, this operation requires only about 300 seconds! The database contains more than a million rows and should grow in the future, so this level of performance is so early that it is unacceptable. Example below:

var query = from item in db.DataTable.Where(x => x.date == suppliedDate)
            where inputList.Contains(item.name)
            select new { item.name};

Help!

edit: Thanks so much for all your suggestions! I just wanted to add a few extra observations as I was now able to view the SQL output of my LINQ query (see below)

SELECT [t0].[name]
FROM [dw].[DataTable] AS [t0]
WHERE ([t0].[name] IN (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17)) AND ([t0].[date] = @p18)
-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Mark]
-- @p1: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Owen]
-- @p2: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [James]
-- @p3: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [John]

etc..

500 ? ( )? - ? ( ). , 300 126 , , , db 10 .

Answer 1

-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Mark] 

varchar?

, , sql nvarchar (DOOOOOM!)

, varchar.

---

reset ( ansi- ).

http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.getcommand.aspx http://msdn.microsoft.com/en-us/library/system.data.dbtype.aspx

ExecuteReader , DbDataReader. DbDataReader Translate datacontext, IEnumerable<T>, linq.

http://msdn.microsoft.com/en-us/library/bb534213.aspx

,

Answer 2

500 , !

sql, sql- , Linq-to-sql , .

Answer 3

Try

var query = from item in db.DataTable
            where item.date == suppliedDate
            where inputList.Contains(item.name)
            select new { item.name};

LinqPad, , , SQL .

Answer 4

, , .

, / , .

MSSQL 2008 SQL- MERGE, , . - , .

. , ( , RAID 5/6, 6/12 , ,

Answer 5

linq, inputList sql ( xml), , . linq sp .