Linq to SQL query taking forever

Question

Ok, first I thought I had a problem with how I was querying things. But apparently the problem lies in how linq translates my query to sql.

Here's my linq:

var items = (from p in ctx.bam_Prestatie_AllInstances
             join q in ctx.bam_Zending_AllRelationships on p.ActivityID equals q.ReferenceData
             join r in ctx.bam_Zending_AllInstances on q.ActivityID equals r.ActivityID
             orderby p.LastModified descending
             where r.PrestatieOntvangen >= vanaf && r.PrestatieOntvangen <= tm
             select new Data.BAMPrestatieInstance
               {
                Aanvaard = p.PrestatieAanvaard,
                Contactnummer = r.ContactNr,
                Identificatie = p.Identificatie,
                Foutmelding = ((p.Foutmelding == "" || p.Foutmelding == null) && p.PrestatieAanvaard == null) ? "De prestatie is bezig met verwerkt te worden." : p.Foutmelding.Replace("\r\n", " "),
                Ontvangen = p.PrestatieZendingOntvangen,
                Uitvoerdatum = p.Uitvoerdatum.Replace('-', '/'),
                Zender = r.Zender,
                PrestatieCode = p.PrestatieCode,
                ZendingsNr = r.Zendingnummer.ToString(),
                GroepsAanvaarding = r.Identificatie
               }).Take(100);

Which gets translated in:

SELECT TOP (100) [t3].[Zender], [t3].[ContactNr] AS [Contactnummer], [t3].[Identificatie], [t3].[value] AS [Uitvoerdatum], [t3].[PrestatieZendingOntvangen] AS [Ontvangen], [t3].[PrestatieAanvaard] AS [Aanvaard], [t3].[value2] AS [Foutmelding], [t3].[PrestatieCode], [t3].[value3] AS [ZendingsNr], [t3].[Identificatie2] AS [GroepsAanvaarding]

FROM (

    SELECT [t2].[Zender], [t2].[ContactNr], [t0].[Identificatie], REPLACE([t0].[Uitvoerdatum], @p0, @p1) AS [value], [t0].[PrestatieZendingOntvangen], [t0].[PrestatieAanvaard], 

        (CASE 

            WHEN (([t0].[Foutmelding] = @p2) OR ([t0].[Foutmelding] IS NULL)) AND ([t0].[PrestatieAanvaard] IS NULL) THEN CONVERT(NVarChar(3800),@p3)

            ELSE REPLACE([t0].[Foutmelding], @p4, @p5)

         END) AS [value2], [t0].[PrestatieCode], CONVERT(NVarChar,[t2].[Zendingnummer]) AS [value3], [t2].[Identificatie] AS [Identificatie2], [t2].[PrestatieOntvangen], [t0].[LastModified]

    FROM [dbo].[bam_Prestatie_AllInstances] AS [t0]

    INNER JOIN [dbo].[bam_Zending_AllRelationships] AS [t1] ON [t0].[ActivityID] = [t1].[ReferenceData]

    INNER JOIN [dbo].[bam_Zending_AllInstances] AS [t2] ON [t1].[ActivityID] = [t2].[ActivityID]

    ) AS [t3]

WHERE ([t3].[PrestatieOntvangen] >= @p6) AND ([t3].[PrestatieOntvangen] <= @p7)

ORDER BY [t3].[LastModified] DESC

As you can see, first it selects EVERYTHING and then it takes the top 100 and does the where. Why is this? Why can't it directly do the top 100, I think the problem why my queries run so long is because of this. Is there a better way to construct my linq query then?

Thanks

Funny thing I noticed btw, if i REMOVE the TOP 100 from the query it takes 4 seconds and shows me 22000 items, if i set the top 100 back again, it takes 55seconds. I think it has something to do with copying it to a temporary table or something — WtFudgE, Jan 27 '10 at 15:35
I temporarily fixed my problem by just selecting all and looping through the first 100 lines, it goes a lot faster but I'm not that pleased :( — WtFudgE, Jan 27 '10 at 15:36

score 0 · Accepted Answer · answered Jan 27 '10 at 14:51

Try this:

var items =  from p in ctx.bam_Prestatie_AllInstances.OrderByDesc(p => p.LastModified).Take(100) 
             join q in ctx.bam_Zending_AllRelationships on p.ActivityID equals q.ReferenceData 
             join r in ctx.bam_Zending_AllInstances on q.ActivityID equals r.ActivityID 
             where r.PrestatieOntvangen >= vanaf && r.PrestatieOntvangen <= tm 
             select new Data.BAMPrestatieInstance 
               { 
                Aanvaard = p.PrestatieAanvaard, 
                Contactnummer = r.ContactNr, 
                Identificatie = p.Identificatie, 
                Foutmelding = ((p.Foutmelding == "" || p.Foutmelding == null) && p.PrestatieAanvaard == null) ? "De prestatie is bezig met verwerkt te worden." : p.Foutmelding.Replace("\r\n", " "), 
                Ontvangen = p.PrestatieZendingOntvangen, 
                Uitvoerdatum = p.Uitvoerdatum.Replace('-', '/'), 
                Zender = r.Zender, 
                PrestatieCode = p.PrestatieCode, 
                ZendingsNr = r.Zendingnummer.ToString(), 
                GroepsAanvaarding = r.Identificatie 
               };

Since the sort applies only to your first table, I'd try to force the order by and take 100 to be applied before the join.

Tried this btw, went a bit faster, but my alternative solutions was still better. But also not good enough :( — WtFudgE, Jan 27 '10 at 15:40
It sure seems so. See http://stackoverflow.com/questions/857807/linq-to-sql-take-w-o-skip-causes-multiple-sql-statements/869340#869340 for an explanation why LINQ to SQL creates a sub-query that it then TOPs. — Jonas Elfström, Jan 27 '10 at 15:47
@WtFudgE, noting your comments here and in the question, all I can say is that sometimes LINQ isn't the best answer. Code generation doesn't necessarily lead to optimized code. If you still want to use LINQ (and I certainly would want to), you might consider coding the join as a view in the database, then using LINQ to query the view. — Cylon Cat, Jan 27 '10 at 16:01

Linq to SQL query taking forever

1 Answers1