2

I have a query like that :

context.Diffusions.Where(x => x.ProgrammeId == programmeID).Include("Chaines").Include("Version").ToList();

The query generated is:

SELECT 
[Extent1].[Duree] AS [Duree], 
[Extent1].[Id] AS [Id], 
[Extent1].[ProgrammeId] AS [ProgrammeId], 
[Extent1].[VersionId] AS [VersionId], 
[Extent1].[ChaineId] AS [ChaineId], 
[Extent1].[Debut] AS [Debut], 
[Extent1].[Fin] AS [Fin], 
[Extent1].[ReRun] AS [ReRun], 
[Extent1].[DateModification] AS [DateModification], 
[Extent1].[DateDiffusion] AS [DateDiffusion], 
[Extent2].[Id] AS [Id1], 
[Extent2].[Nom] AS [Nom], 
[Extent2].[Code] AS [Code], 
[Extent2].[Abreviation] AS [Abreviation], 
[Extent3].[Id] AS [Id2], 
[Extent3].[ProgrammeId] AS [ProgrammeId1], 
[Extent3].[CleVersion] AS [CleVersion], 
[Extent3].[Numero] AS [Numero], 
[Extent3].[NumeroModification] AS [NumeroModification], 
[Extent3].[VO] AS [VO], 
[Extent3].[TitrePresse] AS [TitrePresse], 
[Extent3].[Description] AS [Description], 
[Extent3].[Remarque] AS [Remarque], 
[Extent3].[SousTitre] AS [SousTitre], 
[Extent3].[DureeTheorique] AS [DureeTheorique], 
[Extent3].[Format] AS [Format], 
[Extent3].[Interdit] AS [Interdit], 
[Extent3].[LangueId] AS [LangueId], 
[Extent3].[TypeCoteDiffusionId] AS [TypeCoteDiffusionId]
FROM   [dbo].[Diffusion] AS [Extent1]
INNER JOIN [dbo].[Chaine] AS [Extent2] ON [Extent1].[ChaineId] = [Extent2].[Id]
INNER JOIN [dbo].[Version] AS [Extent3] ON [Extent1].[VersionId] = [Extent3].[Id]
WHERE [Extent1].[ProgrammeId] = 1926475

My problem is that the Table as a lot of entries and it makes an inner join for each entry and then do the "WHERE" so it takes like 6sec.

When I do the query without the include it's instant. I would like to have a linq query that do the "WHERE" and then the "INCLUDE" for each row returned without having to do it manually for each entry (a Programme can have like 1 000 diffusions).

Pak
  • 2,639
  • 2
  • 21
  • 27
  • I'm not sure there is any way, but if you are sure it affects the performance significantly then go for a stored procedure. – Alireza Oct 16 '13 at 14:24
  • 6
    SQL does not work like that, it is more likely you just need to create a index in your database on `Diffusion.ProgrammeId`. Indexes on `Diffusion.ChaineId` and `Diffusion.VersionId` likely wouldn't' hurt either. Run the [Database Engine Tuning Adviser](http://technet.microsoft.com/en-us/library/ms166575.aspx) and see what indexes it recommends. (I am assuming `Id` on each table is the primary key and is already indexed) – Scott Chamberlain Oct 16 '13 at 14:29
  • 1
    There's nothing wrong really with the query LINQ generated. The alternative would be the infamous Select n+1 problem http://stackoverflow.com/questions/97197/what-is-the-n1-selects-issue. – Petko Petkov Oct 16 '13 at 14:33
  • I would advise against using `LinqToEntities` with `MySQL`. The code in the provider quite aggressively uses sub queries, when MySQL's performance with sub-queries is pretty dire. I would advise you to use any Database engine except those written by Oracle. I hear that MariaDB is a good fork of MySQL. – Aron Oct 16 '13 at 15:09
  • YOu really need to get a better understanding of how SQL works. It does not do things in order, the engine decides the best way to get the data. This is likely the most efficient query at this level of simplicity. Look at indexing the foreign keys and the feild inteh where clause and learn to use the explain plan to see where the problem is. – HLGEM Oct 16 '13 at 17:54

1 Answers1

0

try this Code And use Contains for example follow code 

 context.Diffusions.Where(x => x.ProgrammeId == programmeID).Contains("Chaines").Include("Version").ToList();
roozbeh
  • 304
  • 1
  • 2
  • 10