alistairgthomas (11/11/2010)

---

OK, confession time. Reading Charles' Outer Apply suggestion I can't see why this isn't a RBAR solution. Doesn't the Outer Apply do exactly what a subquery of the type 'Top 1 where Date < Today' would do... slowly?

charles.gildawie (8/20/2010)

---

It's a good introduction for ROW_NUMBER but the query plan it generates is a little horrible. I wouldn't recommend ROW_NUMBER() for this task.

With the ROW_NUMBER() approach you can't use any INDEX when doing your LEFT JOINS back to your CTE.

So what happens is that you end up performing at least 2 CI SCANS on priceHistory table, regardless of how selective the rest of your query is. The example you gave actually uses 3 CI scans -- one because you are using the whole of priceHistory and 2 from the LEFT JOINS back to the CTE.

Perhaps a better approach would be to use another 2005+ construct -- OUTER APPLY

Example:

SELECT
	i.[item] AS [Item]
	, previousPrice.[Price] AS [Old Price]
	, ph.[price] AS [RangePrice]
	, ph.[priceStartDate] AS [Startdate]
	, nextPriceStart.[nextPriceStartDate] AS [EndDate]
FROM
	items AS i
	JOIN priceHistory ph ON ph.[itemId] = i.[itemID]

	OUTER APPLY (
		SELECT TOP 1
			phNext.[priceStartDate] AS [nextPriceStartDate]
		FROM
			priceHistory AS phNext
		WHERE
			phNext.[itemId] = ph.[itemID]
			AND phNext.[priceStartDate] > ph.[priceStartDate]
		ORDER BY
			phNext.[priceStartDate] ASC
		)
		AS nextPriceStart

	OUTER APPLY (
		SELECT TOP 1
			phPrev.[price] AS [Price]
		FROM
			priceHistory phPrev
		WHERE
			phPrev.[itemId] = ph.[ItemID]
			AND phPrev.[priceStartDate] < ph.[priceStartDate]
		ORDER BY
			phPrev.[priceStartDate] DESC
		)
		AS previousPrice

Which generates the same results but only does 1 CI scan (because we are using every row in priceHistory. If we were more selective (for example only doing this for vacuum cleaner's then there would be no scans and all seeks)

Give it a whirl and compare the execution plans. I bet OUTER APPLY would be a lot faster on a large dataSet (and where you are being selective on the products / dates you want to bring back).

Regards,

Transact_Charlie.

David McKinney (11/12/2010)

---

Alistair,

Just looking at it, I'd tend to agree with you. While I agree with the assertion that the CTE approach can't benefit from indexes (there are none), I'd be surprised if the APPLY approach broke many records. The nature of CROSS APPLY / OUTER APPLY is that the "applied" is applied for every row - and this fits my understanding of RBAR.

Regards.

David McKinney (11/12/2010)

---

Thanks Chris...I guess it could be analogous to a non-equi join or a cross join with a where clause, but I remain somehow unconvinced and I'd still like to see some figures.

Indeed what strikes me about all the discussion so far around this article is that no-one has actually put these methods seriously to test. (Not even the author!)

I have thought about doing a follow up article based solely around the different methods proposed in the discussions, and putting each to the test against real volumes of data, but also against likely use cases. It's in my list of articles I really must get around to writing!

Quizás, Quizás, Quizás.