Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 2 queries, the results of their execution is the same, but I don't know which of these is the best in terms of performance and why.

Query #1:

SELECT 
CASE 
WHEN ( EXISTS (SELECT 
1 AS [C1]
FROM ( SELECT 
    (SELECT 
        COUNT(1) AS [A1]
        FROM  [dbo].[CompanyElement] AS [Extent2]
        INNER JOIN [dbo].[Company] AS [Extent3] ON [Extent2].[CompanyID] = [Extent3].[CompanyID]
        WHERE ([Extent1].[ElementID] = [Extent2].[ElementID]) AND (36 = [Extent3].[GroupID])) AS [C1]
    FROM [dbo].[Page] AS [Extent1]
)  AS [Project1]
WHERE 2 = [Project1].[C1]))THEN cast(1 as bit) 
WHEN ( NOT EXISTS (SELECT 
1 AS [C1]
FROM ( SELECT 
    (SELECT 
        COUNT(1) AS [A1]
        FROM  [dbo].[CompanyElement] AS [Extent5]
        INNER JOIN [dbo].[Company] AS [Extent6] ON [Extent5].[CompanyID] = [Extent6].[CompanyID]
        WHERE ([Extent4].[ElementID] = [Extent5].[ElementID]) AND (36 = [Extent6].[GroupID])) AS [C1]
    FROM [dbo].[Page] AS [Extent4]
)  AS [Project3]
WHERE 2 = [Project3].[C1])) THEN cast(0 as bit) END AS [C1]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]

Query #2:

SELECT [GroupBy2].[A1] AS [C1]
FROM ( SELECT 
COUNT(1) AS [A1]
FROM ( SELECT 
    (SELECT 
        COUNT(1) AS [A1]
        FROM  [dbo].[CompanyElement] AS [Extent2]
        INNER JOIN [dbo].[Company] AS [Extent3] ON [Extent2].[CompanyID] = [Extent3].[CompanyID]
        WHERE ([Extent1].[ElementID] = [Extent2].[ElementID]) AND (36 = [Extent3].[GroupID])) AS [C1]
    FROM [dbo].[Page] AS [Extent1]
)  AS [Project1]
WHERE 2 = [Project1].[C1]) AS [GroupBy2]
share|improve this question
1  
Can you post execution plans and statistics IO ON results? That would be helpful. –  Kin Dec 13 '13 at 13:15
    
In your case two subqueries are the same, so you can use WHEN EXISTS... and the other branch became ELSE (without subquery) –  Joe Taras Dec 13 '13 at 16:33
    
You should post the execution plans (not here as pictures, but use PasteBin or PlanExplorer's upload feature - it's free and very good at showing you what happens behind). We could compare plans and throw some ideas. PS: Yuck, that looks like generated by some ORM or some designer. If they're slow, can you write your own query for that request? –  Marian Dec 14 '13 at 12:14
    
Both queries are auto-generated from a similar linq expressions, but I don't know how sqlServer processes them. –  LucaGuerra Dec 29 '13 at 17:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.