With versus Subquery Which is Faster? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2008 Forums

Transact-SQL (2008)

With versus Subquery Which is Faster?

Author

Topic

phenreid
Starting Member

29 Posts

Posted - 2010-09-01 : 01:53:12

Which of these two statements is faster/better and why?

Is there another more efficient suggestion than either of them?

select * from 
(
select resellerkey,salesordernumber,OrderDateKey, SalesAmount,
 row_number() over (partition by resellerkey order by ResellerKey,OrderDateKey desc) as 'RowNum' 
 from phsales
 ) as a
 where A.RowNum=1


With mytable as
(
select resellerkey,salesordernumber,OrderDateKey, SalesAmount,
 row_number() over (partition by resellerkey order by ResellerKey,OrderDateKey desc) as 'RowNum' 
 from phsales
)
select * from mytable where RowNum=1

Thank You.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-01 : 02:50:00

I can't see the subquery. I can see a derived table in the first query.
And no, there is no difference in performance.

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-01 : 05:36:54

You don't need to order by the column you are also partitioning. Also -- why select the rowNum column. I doubt you will use it

So your:


select * from 
(
select resellerkey,salesordernumber,OrderDateKey, SalesAmount,
 row_number() over (partition by resellerkey order by ResellerKey,OrderDateKey desc) as 'RowNum' 
 from phsales
 ) as a
 where A.RowNum=1

Could become:


SELECT
	resellerkey
	, salesordernumber
	, OrderDateKey
	, SalesAmount
FROM 
	(
	SELECT
		resellerkey
		, salesordernumber
		, OrderDateKey
		, SalesAmount
		, ROW_NUMBER() OVER (
				PARTITION BY resellerkey
				ORDER BY OrderDateKey DESC
				)
				AS [rowNum]
	FROM
		phsales
	)
	AS a
WHERE
	a.RowNum = 1

Because you are ordering by only one column you may be able to turn this into a derived table using a GROUP BY and MAX (depending on whether the orderDateKey is guaranteed to be unique (which I'm guessing it is because the column has the word 'key' in it. I've found that a group by and MIN/MAX is slightly faster for me than the corresponding simple row_number solution

Example:



SELECT
	phs.[resellerKey]
	, phs.[salesOrderNumber]
	, phs.[orderDateKey]
	, phs.[salesAmount]
FROM

	(
	SELECT
		[resellerKey] AS [resellerKey]
		, MAX([orderDateKey]) AS [maxOrderDateKey]
	FROM
		phsales
	GROUP BY
		[resellerKey]
	)
	AS latestSales

	JOIN phSales AS phs ON
		phs.[resellerKey] = latestSales.[resellerKey]
		AND phs.[orderDateKey] = latestSales.[maxOrderDateKey]

Edit -- missed the DESC
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

phenreid
Starting Member

29 Posts

Posted - 2010-09-01 : 13:14:27

Thx. In my real-life example, the OrderDate is not necessarily unique within each partition, in which case there could be two that are the "most recent" -- but either would do or they would need to be Date/Time fields.

If you don't order by OrderDateKey Desc within the partition, then the latest invoice won't necessarily be RowNum=1, so I do need that right?

A year ago I posted to this forum asking for the most efficient/elegant way to handle a similar situation. It was given to me that rownumber()/Over/Partition (then pick the first rows) was faster than a self-join (your example).

That is the main reason for this post. Which is faster/better? Rownumber/Over/Partition or Self Join?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-01 : 14:46:49

You will have to test in your environment. The "fastest" solution depends on a number of factors, yet not revealed to us such as indexing, partitioning and other factors.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources