I've mapped an EDM entity to a database(SQL Server 2005) View. The entity is a simple Movie Entity which has properties of ID, Name and DateInserted which corresponds to a View which has the following definition:
SELECT iMovieID, vchName, dtInsertDate
FROM dbo.t_Movie WITH (NOLOCK)
The table t_Movie has the following definition:
CREATE TABLE [dbo].[t_Movie](
[iMovieID] [int] IDENTITY(1,1) NOT NULL,
[vchName] varchar NOT NULL,
[dtInsertDate] [datetime] NULL,
CONSTRAINT [PK_t_Movie] PRIMARY KEY CLUSTERED
( [iMovieID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
When I write a simple Linq to Entities Query like so:
var q = from m in context.v_Movie where m.vchName.Contains("Ocean") select m;
foreach (var movie in q)
{
Console.WriteLine("{0}:{1}",movie.iMovieID, movie.vchName);
}
Here is the SQL generated by the Entity framework captured by the profiler:
SELECT
[Extent1].[iMovieID] AS [iMovieID],
[Extent1].[vchName] AS [vchName],
[Extent1].[dtInsertDate] AS [dtInsertDate]
FROM (SELECT
[v_Movie].[iMovieID] AS [iMovieID],
[v_Movie].[vchName] AS [vchName],
[v_Movie].[dtInsertDate] AS [dtInsertDate]
FROM [dbo].[v_Movie] AS [v_Movie]) AS [Extent1]
WHERE (CAST(CHARINDEX(N'Ocean', [Extent1].[vchName]) AS int)) > 0
The DBA has concern that the Inner SELECT:
SELECT
[v_Movie].[iMovieID] AS [iMovieID],
[v_Movie].[vchName] AS [vchName],
[v_Movie].[dtInsertDate] AS [dtInsertDate]
FROM [dbo].[v_Movie] AS [v_Movie]) AS [Extent1]
will cause some serious performance issues over time as the table grows since its selecting all the rows from the view into a temp table([Extent1]) and then the outer SELECT is selecting from this temp table.
Any particular reason why EF needs to do this, is there any reason why the following could not have been the generated SQL:
SELECT
[v_Movie].[iMovieID] AS [iMovieID],
[v_Movie].[vchName] AS [vchName],
[v_Movie].[dtInsertDate] AS [dtInsertDate]
FROM [dbo].[v_Movie] AS [v_Movie]
WHERE (CAST(CHARINDEX(N'Ocean', [Extent1].[vchName]) AS int)) > 0
I populated the table with 100,000 records using the following SQL but did not notice any performance degradation when executing the LINQ query. Profiler showed that the query ran under a second:
BEGIN
declare @counter int
set @counter = 0
while @counter < 100000
begin
set @counter = @counter + 1
INSERT INTO t_Movie(vchName) values('Movie'+CONVERT(varchar,@counter))
end
END
Is this a valid concern?
P.S -
(CAST(CHARINDEX(N'Ocean', [Extent1].[vchName]) AS int)) is not a concern here since the LINQ to Entities query I've used is just for illustration.
Any insights would be much appreciated
