Why does adding OrderBy to LINQ to EF query improve its performance?

Question

See the query below. The object and property names have been obfuscated somewhat to not leak confidential/sensitive information, but the query structure is the same.

When the .OrderBy(p => "") is added, which is complete non-sense to me, the query runs much faster. The time it takes to execute the query goes from approx. 2000ms down to approx. 400ms. I have tested it a couple of times, adding and removing only the OrderBy statement.

I am completely puzzled, how can this be? The query is executed on a SQL database in an Azure environment.

I can understand that ordering data on property A, and then selecting records where property A equals some value could potentialy speed up the query. But ordering on an empty string!? What is going on here?

Also I want to note, that the query, without the OrderBy, using Expressions ( as suggested in this post to circumvent SQL parameter sniffing) lowers the execution time also to approx. 400ms. Adding the .OrderBy(p => "") then doesn't make any noticeable difference.

        var query = (from p in Context.Punders.Where(p => p.A == A)
                    .Where(p => null != p.SomeNumber)
                    .Where(p => p.StatusCode == Default ||
                                   p.StatusCode == Cancelled)
                    .Where(p => p.DatePosted >= startDate && p.DatePosted <= endDate)
                join f in Context.Founders.Where(f => f.A == A) on p.Code equals f.Code
                join r in Context.Rounders.Where(r => r.A == A) on p.Code equals r.Code
                    into rg
                from r in rg.DefaultIfEmpty()
                join pt in Context.FishTypes.Where(ft => ft.A ==A) on p.Code equals pt.Code
                where r == null
                select new
                {
                    p.Code,
                    f.B,
                    f.C,
                    p.D,
                    p.E,
                    pt.F,
                    pt.G,
                    p.H
                })
            .OrderBy(p => "");

Query without the .OrderBy(...

SELECT [Filter1].[q]              AS [q], 
       [Filter1].[c1]                 AS [edoc], 
       [Filter1].[oc1]            AS [wnrdc], 
       [Filter1].[otc1]        AS [weener], 
       [Filter1].[ptc1]      AS [pmtpdc], 
       [Extent4].[isr]          AS [isr], 
       [Extent4].[rac] AS [rac], 
       [Filter1].[arn]              AS [arn] 
FROM   (SELECT [Extent1].[pcid]  AS [pcid1], 
               [Extent1].[edoc]            AS [c1], 
               [Extent1].[pmtpdc] AS [ptc1], 
               [Extent1].[q]        AS [q], 
               [Extent1].[arn]        AS [arn], 
               [Extent1].[dateposted]      AS [DatePosted], 
               [Extent2].[pcid]  AS [pcid2], 
               [Extent2].[wnrdc]       AS [oc1], 
               [Extent2].[weener]   AS [otc1] 
        FROM   [fnish].[post] AS [Extent1] 
               INNER JOIN [fnish].[olik] AS [Extent2] 
                       ON [Extent1].[olikedoc] = [Extent2].[edoc] 
               LEFT OUTER JOIN [fnish].[receivable] AS [Extent3] 
                            ON ( [Extent3].[pcid] = @p__linq__4 ) 
                               AND ( [Extent1].[edoc] = 
                                     [Extent3].[pepstedoc] ) 
        WHERE  ( [Extent1].[arn] IS NOT NULL ) 
               AND ( [Extent1].[posttedoc] IN ( N'D', N'X' ) ) 
               AND ( [Extent3].[id] IS NULL )) AS [Filter1] 
       INNER JOIN [fnish].[paymenttype] AS [Extent4] 
               ON [Filter1].[ptc1] = [Extent4].[edoc] 
WHERE  ( [Filter1].[pcid1] = @p__linq__0 ) 
       AND ( [Filter1].[dateposted] >= @p__linq__1 ) 
       AND ( [Filter1].[dateposted] <= @p__linq__2 ) 
       AND ( [Filter1].[pcid2] = @p__linq__3 ) 
       AND ( [Extent4].[pcid] = @p__linq__5 ) 

Query with the .OrderBy(...

SELECT [Project1].[q]              AS [q], 
       [Project1].[edoc]                  AS [edoc], 
       [Project1].[wnrdc]             AS [wnrdc], 
       [Project1].[weener]         AS [weener], 
       [Project1].[pmtpdc]       AS [pmtpdc], 
       [Project1].[isr]          AS [isr], 
       [Project1].[rac] AS [rac], 
       [Project1].[arn]              AS [arn] 
FROM   (SELECT N''                               AS [C1], 
               [Filter1].[c1]                 AS [edoc], 
               [Filter1].[ptc1]      AS [pmtpdc], 
               [Filter1].[q]              AS [q], 
               [Filter1].[arn]              AS [arn], 
               [Filter1].[oc1]            AS [wnrdc], 
               [Filter1].[otc1]        AS [weener], 
               [Extent4].[isr]          AS [isr], 
               [Extent4].[rac] AS [rac] 
        FROM   (SELECT [Extent1].[pcid]  AS [pcid1], 
                       [Extent1].[edoc]            AS [c1], 
                       [Extent1].[pmtpdc] AS [ptc1], 
                       [Extent1].[q]        AS [q], 
                       [Extent1].[arn]        AS [arn], 
                       [Extent1].[dateposted]      AS [DatePosted], 
                       [Extent2].[pcid]  AS [pcid2], 
                       [Extent2].[wnrdc]       AS [oc1], 
                       [Extent2].[weener]   AS [otc1] 
                FROM   [fnish].[post] AS [Extent1] 
                       INNER JOIN [fnish].[olik] AS [Extent2] 
                               ON [Extent1].[olikedoc] = [Extent2].[edoc] 
                       LEFT OUTER JOIN [fnish].[receivable] AS [Extent3] 
                                    ON ( [Extent3].[pcid] = 
                                         @p__linq__4 ) 
                                       AND ( [Extent1].[edoc] = 
                                             [Extent3].[pepstedoc] ) 
                WHERE  ( [Extent1].[arn] IS NOT NULL ) 
                       AND ( [Extent1].[posttedoc] IN ( N'D', N'X' ) ) 
                       AND ( [Extent3].[id] IS NULL )) AS [Filter1] 
               INNER JOIN [fnish].[paymenttype] AS [Extent4] 
                       ON [Filter1].[ptc1] = [Extent4].[edoc] 
        WHERE  ( [Filter1].[pcid1] = @p__linq__0 ) 
               AND ( [Filter1].[dateposted] >= @p__linq__1 ) 
               AND ( [Filter1].[dateposted] <= @p__linq__2 ) 
               AND ( [Filter1].[pcid2] = @p__linq__3 ) 
               AND ( [Extent4].[pcid] = @p__linq__5 )) AS [Project1] 
ORDER  BY [Project1].[c1] ASC 

---

Conclusion

From what I have learned, with a bit of a guess: It is case specific behavior. In my case, the performance gain is likely due to a different execution plan being constructed by the SQL server that is yielding a better performing query. I've seen a different execution plan with the query without the OrderBy using the SQL statement OPTION(RECOMIPILE) that showed similar performance gain. So adding the OrderBy to the LINQ query is very likely (I think) producing a different execution plan that yields a better performing query.

Answer 1

Given your note

Also I want to note, that the query, without the OrderBy, using Expressions ( as suggested in this post to circumvent SQL parameter sniffing) lowers the execution time also to approx. 400ms. Adding the .OrderBy(p => "") then doesn't make any noticeable difference.

The most reasonable explanation is: OrderBy has the same effect as using explicit values instead of parameters. So if you had pre-cached plan for given query, and with particular parameter values this plan is not optimal (2 seconds) - changing this query by adding useless OrderBy to it will force SQL Server to create new execution plan for this query, and so will negate effect of old non-optimal execution plan. Of course, it should be clear that this is not a good way to negate plan caching.