Choosing better query out of 2 queries, both returning same results

I am having two different sql queries one written by me and one automatically generated by C# when used with linq, both are giving same results.

I am not sure w开发者_如何学Chich one to choose, Iam looking for

1. Whats the best way to choose one query out of many, when all returns same result (most optimized query).
2. Out of my queries (below written), which one should i choose.

Hand Written

select * from People P
inner join  SubscriptionItemXes S
on
P.Id=S.Person_Id
inner join FoodTagXFoods T1
on T1.FoodTagX_Id = S.Tag2

inner join FoodTagXFoods T2
on T2.FoodTagX_Id = S.Tag1

inner join Foods F
on
F.Id= T1.Food_Id and F.Id= T2.Food_Id

where p.id='1'

Automatically Generated by LINQ

SELECT 
[Distinct1].[Id] AS [Id], 
[Distinct1].[Item] AS [Item]
FROM ( SELECT DISTINCT 
    [Extent2].[Id] AS [Id], 
    [Extent2].[Item] AS [Item]
    FROM    [dbo].[People] AS [Extent1]
    CROSS JOIN [dbo].[Foods] AS [Extent2]
    INNER JOIN [dbo].[FoodTagXFoods] AS [Extent3] 
    ON [Extent2].[Id] = [Extent3].[Food_Id]
    INNER JOIN [dbo].[SubscriptionItemXes] AS [Extent4] 
    ON [Extent1].[Id] = [Extent4].[Person_Id]
    WHERE (N'rusi' = [Extent1].[Name]) AND ( EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[FoodTagXFoods] AS [Extent5]
        WHERE ([Extent2].[Id] = [Extent5].[Food_Id]) 
            AND ([Extent5].[FoodTagX_Id] = [Extent4].[Tag1])
    )) AND ( EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[FoodTagXFoods] AS [Extent6]
        WHERE ([Extent2].[Id] = [Extent6].[Food_Id]) 
            AND ([Extent6].[FoodTagX_Id] = [Extent4].[Tag2])
    ))
)  AS [Distinct1]

Execution Plan Results

Hand Written: Query Cost (relative to batch):33%

Linq Generated: Query Cost (relative to batch):67%

I have found that two different queries, one hand-written and one generated by Linq might look wildly different but, actually, when you analyse the query plan in SSMS, you find that actually they are almost identical.

You need to actually run these queries in SSMS with Display Actual Execution Plan switched on, and analyse the different plans. It's the only way to correctly analyse the two and find out which is better.

In general, Linq is actually very good at generating efficient queries; even if the actual SQL itself is pretty ugly (in some cases, it's the kind of SQL that a human would write if they had the time!). If course, that said, it can also generate some pigs!

Additionally, asking SO to help with performance of a query over so many tables is fraught with problems for us, since it will be governed so much by your indexes :)

But they aren't quite returning the same thing... The first query grabs everyting (SELECT *) while LINQ is going to extract what you really want (id and item). Trivial you may say but streaming back lots of data that's never used is a good waste of bandwidth and will make your application appear sluggish. Additionally, the LINQ query seems to be doing a lot more which may or may not be the correct solution especially as data is populated into FoodTagXFoods

As for which performs better, I couldn't tell you without something like the actual query plans and/or results of statistics io from both queries. My money is on hand-written but maybe because I like my hands.

Examining SQL Server Query Execution Plan is the best way to choose the better query.

Hope below tutorials help.

SQL Tuning Tutorial - Understanding a Database Execution Plan (1)

Examining Query Execution Plans

SQL Server Query Execution Plan Analysis

SQL SERVER – Actual Execution Plan vs. Estimated Execution Plan