Help understanding resources used to get results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help understanding resources used to get results

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-03-20 : 05:35:53

Please see the query below. This is basically a count of different columns in a table variable. Initially the query wasn't commented, and so was an outer and inner query. I did this because I THOUGHT that SQL would re-calculate all the sums in the last column. After removing the outer query and performing the whole thing in a single query, I don't think there's any difference. From the basics I can see in the Client Statistics it seems similar.

Am I correct in thinking this? Does SQL really 'see' that the data required for the last column's calculations has already been done, and just re-use them?


--SELECT
--	q.*,
--	(q.count1 - q.count2) as [total] -- removed from here into formerly sub-query
--FROM
--	( -- Sub-query does initial counts

	SELECT
		linkID,
		COALESCE(COUNT(linkID), 0) as [count1],
		COALESCE(COUNT(notAppCol), 0) as [count2],
		COALESCE(COUNT(linkID), 0) - COALESCE(COUNT(notAppCol), 0) as [count3] -- no more resources used if performed here?
	FROM 
		@tbl_results
	GROUP BY
		linkID

--	) as q

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-20 : 07:34:37

If you look at the graphical query plan, that may explain it. The optimizer would calculate the aggregates (using something like Stream Aggregate) in on step, and in a later step, would do the scalar computations. Resources used up by the scalar computations would be minimal to none.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-03-20 : 07:59:41

Hi

The graphical plan shows this:

I assume that matches with what your saying, so SQL is smart enough to not calculate twice over?!?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources