SQL Server - How many times is the subquery executed?

Adam Machanic launced his second TSQL tuesday: Invitation for T-SQL Tuesday #002: A Puzzling Situation. The theme is to show something interesting and not expected by the first look. You can follow posts for this theme on Twitter by looking at #TSQL2sDay hashtag.

Ok here we go. Let me ask you this: How many times will the subquery in the below SQL statement be executed?

SELECT  (SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOH.SalesOrderID) AS c, *
FROM    Sales.SalesOrderHeader SOH
ORDER BY c DESC

How about the subquery in this in the below SQL statement be executed? It’s the same query only we’re repeating the subquery in the order by

SELECT  (SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOH.SalesOrderID) AS c, *
FROM    Sales.SalesOrderHeader SOH
ORDER by (SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOH.SalesOrderID) DESC

If you said once you’d be wrong. At first look I also thought it would run once because SQL server query optimizer would be smart enough to realize we’re ordering by the same values as we’re selecting. However this isn’t a case. Let’s take a look at the IO cost of both queries:

Query #1:

-- Table 'SalesOrderHeader'. Scan count 1, logical reads 703
-- Table 'SalesOrderDetail'. Scan count 1, logical reads 1238

Query #2:

We can see the table SalesOrderDetail being scanned twice. Once for select part and once for order by part.

-- Table 'SalesOrderHeader'. Scan count 1, logical reads 703
-- Table 'SalesOrderDetail'. Scan count 2, logical reads 2476 <- READ TWICE! ONCE FROM SELECT AND ONCE FROM ORDER BY!

Of course the second example isn’t a way to write SQL but it sure is interesting how stuff works.

Avtor: Anonymous, objavljeno na portalu SloDug.si (Arhiv)

Please note that we won't show your email to others, or use it for sending unwanted emails. We will only use it to render your Gravatar image and to validate you as a real person.

Leave a comment