查询性能比较

Question

我有这个数据库模型日期与众多加盟，实体框架：

我使用此查询：

public List<Film> ListFilmsSortiesDes7DerniersJoursDVD() 
{ 
    DateTime dateDans7Jours = DateTime.Now.AddDays(7); 
    DateTime dateIlYa7Jours = DateTime.Now.AddDays(-7); 
    return Query(f => f.Releases.Where(r => r.Langue.langue_code == "FR" && r.TypeRelease.typerelease_code == "DVD").FirstOrDefault().release_date > dateIlYa7Jours 
     && f.Releases.Where(r => r.Langue.langue_code == "FR" && r.TypeRelease.typerelease_code == "DVD").FirstOrDefault().release_date < dateDans7Jours && !string.IsNullOrEmpty(f.film_image)).ToList(); 
} 

但是生成的SQL有不好的表现，约1.3秒返回结果（与SQL Server Express 2008和我已经在正确的字段索引）：

SELECT [Extent1].[film_id]    AS [film_id], 
     [Extent1].[film_image]   AS [film_image], 
     [Extent1].[film_image_thumb]  AS [film_image_thumb], 
     [Extent1].[film_format]   AS [film_format], 
     [Extent1].[film_motsclefs]  AS [film_motsclefs], 
     [Extent1].[film_nom]    AS [film_nom], 
     [Extent1].[film_nomvf]   AS [film_nomvf], 
     [Extent1].[film_synopsis]  AS [film_synopsis], 
     [Extent1].[film_anneeproduction] AS [film_anneeproduction], 
     [Extent1].[film_budget]   AS [film_budget], 
     [Extent1].[film_dateajout]  AS [film_dateajout], 
     [Extent1].[film_actif]   AS [film_actif], 
     [Extent1].[utilisateur_id]  AS [utilisateur_id], 
     [Extent1].[film_francais]  AS [film_francais], 
     [Extent1].[film_revenue]   AS [film_revenue], 
     [Extent1].[filmgroupe_id]  AS [filmgroupe_id] 
FROM [dbo].[Film] AS [Extent1] 
     OUTER APPLY (SELECT TOP (1) [Filter1].[release_date] AS [release_date] 
        FROM (SELECT [Extent2].[film_id]  AS [film_id], 
            [Extent3].[release_date] AS [release_date], 
            [Extent3].[typerelease_id] AS [typerelease_id] 
          FROM [dbo].[FilmRelease] AS [Extent2] 
            INNER JOIN [dbo].[Release] AS [Extent3] 
            ON [Extent3].[release_id] = [Extent2].[release_id] 
            INNER JOIN [dbo].[Langue] AS [Extent4] 
            ON [Extent3].[langue_id] = [Extent4].[langue_id] 
          WHERE N'FR' = [Extent4].[langue_code]) AS [Filter1] 
          INNER JOIN [dbo].[TypeRelease] AS [Extent5] 
          ON [Filter1].[typerelease_id] = [Extent5].[typerelease_id] 
        WHERE ([Extent1].[film_id] = [Filter1].[film_id]) 
          AND (N'CINEMA' = [Extent5].[typerelease_code])) AS [Limit1] 
     CROSS APPLY (SELECT TOP (1) [Filter3].[release_date] AS [release_date] 
        FROM (SELECT [Extent6].[film_id]  AS [film_id], 
            [Extent7].[release_date] AS [release_date], 
            [Extent7].[typerelease_id] AS [typerelease_id] 
          FROM [dbo].[FilmRelease] AS [Extent6] 
            INNER JOIN [dbo].[Release] AS [Extent7] 
            ON [Extent7].[release_id] = [Extent6].[release_id] 
            INNER JOIN [dbo].[Langue] AS [Extent8] 
            ON [Extent7].[langue_id] = [Extent8].[langue_id] 
          WHERE N'FR' = [Extent8].[langue_code]) AS [Filter3] 
          INNER JOIN [dbo].[TypeRelease] AS [Extent9] 
          ON [Filter3].[typerelease_id] = [Extent9].[typerelease_id] 
        WHERE ([Extent1].[film_id] = [Filter3].[film_id]) 
          AND (N'CINEMA' = [Extent9].[typerelease_code])) AS [Limit2] 
WHERE ([Limit1].[release_date] > '2013-02-04T00:07:48' /* @p__linq__0 */) 
     AND ([Limit2].[release_date] < '2013-02-18T00:07:48' /* @p__linq__1 */) 
     AND ([Extent1].[film_image] IS NOT NULL) 

你有什么想法来提高这个查询的性能吗？

Answer 1

好时，答案很简单，为什么搜索复杂：

public List<Film> ListFilmsSortiesDes7DerniersJoursCinema() 
    { 
     DateTime dateDans7Jours = DateTime.Now.AddDays(7); 
     DateTime dateIlYa7Jours = DateTime.Now.AddDays(-7); 
     return Query(f => f.Releases.Where(r => r.Langue.langue_code == "FR" && r.TypeRelease.typerelease_code == "CINEMA" && r.release_date > dateIlYa7Jours && r.release_date < dateDans7Jours).Any()).ToList(); 
    } 

我没有一个参加过