Which SQL Query is Better?

Question

Is this SQL query statement:

SELECT p.id, p.[name], SUM(ps.sales_amount) AS GROSS_SALES
FROM products p
  LEFT OUTER JOIN product_sales ps ON p.id = ps.product_id
GROUP BY p.id, p.[name]

better than:

SELECT SUM([t2].[value]) AS [SalesAmount], [t2].[id] AS [ProductId], [t2].[name] AS [ProductName]
FROM (
    SELECT (
        SELECT SUM([t1].[sales_amount])
        FROM [dbo].[product_sales] AS [t1]
        WHERE [t1].[product_id] = [t0].[id]
        ) AS [value], [t0].[id], [t0].[name]
    FROM [dbo].[products] AS [t0]
    ) AS [t2]
GROUP BY [t2].[id], [t2].[name]

The second one is a result of a LINQ2SQL query. Still finding a way to re-write the LINQ expression...

What would be the most optimized SQL query in the example?

Your thoughts? Thanks!

Answer 1

The real answer is that you can't tell without measuring. Even then you don't really know unless you measure on a realistic dataset.

Check out this blog post (not by me) where the generated sql looked worse but performed much better. His example specifically deals with subselects vs joins so I think it is very relevant to your situation.

Answer 2

Well, the first query is better because it is more readable. However, since the second query was generated than that explains it and since you won't (shouldn't) have to deal with the actual SQL generated from the LINQ expression there probably isn't a problem with the second one.

I would assume that the SQL Server engine can flatten that out into a standard join like the first query. If you want to know fire up SQL Server Management Studio and look at the actual execution plan of both.