I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

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.

Print | posted on Tuesday, January 12, 2010 2:05 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SQL Server - How many times is the subquery executed?

I was just wondering, if you rewrite the above queries as follows to utilise a derived table how does the IO cost compare?

SELECT SOD.DetailCount AS c, SOH.*
FROM Sales.SalesOrderHeader SOH
JOIN (SELECT SalesOrderID, COUNT(*) DetailCount FROM Sales.SalesOrderDetail GROUP BY SalesOrderID) SOD ON SOD.SalesOrderID = SOH.SalesOrderID
ORDER by SOD.DetailCount DESC
1/22/2010 4:56 AM | Michael
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET