Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1112131415»»»

Linking to the Previous Row Expand / Collapse
Author
Message
Posted Tuesday, November 9, 2010 2:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 27, 2011 8:16 AM
Points: 13, Visits: 64
I got this CTE approach working for a related case - my first not-entirely-trivial query! Thanks David and all who have contributed via these comments. Next stage is to try and understand Outer Apply and a temp table approach.

Actually it was my second query, but my first was the RBAR approach :) You live and learn.
Post #1018273
Posted Thursday, November 11, 2010 4:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 27, 2011 8:16 AM
Points: 13, Visits: 64
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?
Post #1019633
Posted Friday, November 12, 2010 12:16 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:02 AM
Points: 648, Visits: 1,874
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?


Hi Alistair, would you mind providing a page number or link to Charles's Outer Apply suggestion (or quote it.)

Thanks,

David.
Post #1019736
Posted Friday, November 12, 2010 6:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 27, 2011 8:16 AM
Points: 13, Visits: 64
Thanks - Mr Charlie says Outer Apply might allow more efficient index use:

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.
Post #1019862
Posted Friday, November 12, 2010 9:10 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:02 AM
Points: 648, Visits: 1,874
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.
Post #1020026
Posted Friday, November 12, 2010 9:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,750, Visits: 13,896
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.


Not really. The optimiser may determine that an APPLY fits the criteria for a JOIN and construct a plan which does exactly that. Paul White's excellent articles referred to in my sig are well worth a read.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1020035
Posted Friday, November 12, 2010 9:37 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:02 AM
Points: 648, Visits: 1,874
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.
Post #1020049
Posted Friday, November 12, 2010 9:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 27, 2011 8:16 AM
Points: 13, Visits: 64
Thanks - I read Paul's articles (skimmed the trickier bits) and it seems that the optimiser recognises a nested loop when it sees it... but I don't see that it then does something clever with an expression like select top 1 of a sorted column? I am new to this! I have simpler version of the example problem, and I tried the CTE, a temp table and an Outer Apply and the former two were (equally) fast while the Apply was a lot slower. I may have mangled the syntax - I'll refrain from posting it to avoid wasting your time and my own embarassment. I was just wondering if I'd understood Charlie's concept correctly.

Many thanks,
Alistair
Post #1020053
Posted Friday, November 12, 2010 9:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,750, Visits: 13,896
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.


Paul puts APPLY though some rigorous proofs in his articles. Don't trust me, I'm only a doctor


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1020056
Posted Saturday, November 13, 2010 9:39 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:02 AM
Points: 648, Visits: 1,874
Sorry Doc...I meant that specifically the different approaches to the "previous / next row" problem haven't been compared side by side.

As for Paul's Apply articles, I have read them; indeed it was there where I learned about using this method for xml shredding.
Post #1020347
« Prev Topic | Next Topic »

Add to briefcase «««1112131415»»»

Permissions Expand / Collapse