InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 06:35:36 GMT20http://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxFirstly apologies, Mohammad, for the obscenely long reply.Before we look at the results, let me just say that the test data, and code that I used are those that I posted previously. So you are free to perform exactly the same test in your own environment.I compared your method with the other methods previously discussed, and unfortunately the results were rather disappointing. Indeed it's generally the worst performer.For the full resultset I used your code exactly as is. For the "Price rises" and the "One Item" queries, I amended your query to put the results in a further CTE and then filtered the results. (extract below)[code="sql"]C3 AS(SELECT MAX(CASE WHEN k = 0 THEN item END) AS Item, MAX(CASE WHEN k = 1 THEN price END) AS OldPrice, MAX(CASE WHEN k = 0 THEN price END) AS RangePrice, MAX(CASE WHEN k = 0 THEN PriceStartDate END) AS StartDate, MAX(CASE WHEN k = -1 THEN PriceStartDate END) AS EndDate FROM C1 GROUP BY itemid, grp_fct HAVING MIN(PriceStartDate) < MAX(PriceStartDate))SELECT * FROM C3 where Item='Item 512'[/code]Full Resultset Method Execution Time CTE View 13 secs Temp Table with rownumber 33 secs Table Variable with rownumber 47 secs Temp Table with identity 16 secs Table Variable with identity Cancelled after 1 hour Cross Apply 12 secs Mohammad Salimabadi Solution 131 secsOne Item CTE View 2 secs Temp Table with rownumber 5 secs Table Variable with rownumber 4 secs Temp Table with identity 6 secs Table Variable with identity 83 secs Cross Apply 0 sesc Mohammad Salimabadi Solution 85 secsPrice Rises CTE View 8 secs Temp Table with rownumber 19 secs Table Variable with rownumber 17 secs Temp Table with identity 12 secs Table Variable with identity Not Run Cross Apply 8 secs Mohammad Salimabadi Solution 114 secsPlease try to reproduce this in your own environment, and let me know if you manage to bring improvements to the results.Best regards,David McKinney.Fri, 27 May 2011 07:21:26 GMTDavid McKinneyhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspx[code="sql"]/*Matching Adjacent Rows based on a consecutive value*/CREATE TABLE Nums(nbr INTEGER NOT NULL PRIMARY KEY, val INTEGER NOT NULL); INSERT INTO Nums (nbr, val)VALUES (1, 0), (5, 7), (9, 4);--===========Mohammad Salimabadi Solution #2SELECT T2.*, T1.*, T3.* FROM Nums AS T1 LEFT JOIN Nums AS T2 ON T2.nbr = (SELECT MAX(nbr) FROM Nums WHERE nbr < T1.nbr) LEFT JOIN Nums AS T3 ON T3.nbr = (SELECT MIN(nbr) FROM Nums WHERE nbr > T1.nbr); --==========Mohammad Salimabadi Solution #3SELECT pre_nbr, N1.val AS pre_val, C.nbr AS cur_nbr, C.val AS cur_val, nxt_nbr, N2.val AS nxt_val FROM ( SELECT MAX(CASE WHEN N1.nbr > N2.nbr THEN N2.nbr ELSE NULL END) AS pre_nbr, N1.nbr, N1.val, MIN(CASE WHEN N1.nbr < N2.nbr THEN N2.nbr ELSE NULL END) AS nxt_nbr FROM Nums AS N1, Nums AS N2 GROUP BY N1.nbr, N1.val ) AS C LEFT JOIN Nums AS N1 ON C.pre_nbr = N1.nbr LEFT JOIN Nums AS N2 ON C.nxt_nbr = N2.nbr; --=========Mohammad Salimabadi Solution #4SELECT CAST(SUBSTRING(concat_pre, 1, 4) AS integer) AS pre_nbr, CAST(SUBSTRING(concat_pre, 5, 8) AS integer) AS pre_val, nbr, val, CAST(SUBSTRING(concat_nxt, 1, 4) AS integer) AS pre_nbr, CAST(SUBSTRING(concat_nxt, 5, 8) AS integer) AS pre_val FROM ( SELECT ( SELECT MAX(CAST(nbr AS BINARY(4)) + CAST(val AS BINARY(4))) FROM Nums WHERE nbr < T.nbr ) AS concat_pre, nbr, val, ( SELECT MIN(CAST(nbr AS BINARY(4)) + CAST(val AS BINARY(4))) FROM Nums WHERE nbr > T.nbr ) AS concat_nxt FROM Nums AS T ) AS D[/code]Wed, 27 Apr 2011 07:50:51 GMT_ms65g_http://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxSorry for late reply.Here you are:[code="sql"]SET STATISTICS IO ON;SET STATISTICS TIME ON;--Mohammad Salimabadi SolutionWITH C0 AS( SELECT Ph.*, I.item, ROW_NUMBER() OVER (PARTITION BY Ph.ItemId ORDER BY Ph.PriceStartDate) AS rownum FROM PriceHistory AS Ph JOIN Items AS I ON Ph.itemid = I.itemid), C1 AS( SELECT itemid, item, PriceStartDate, price, rownum + k AS grp_fct, k FROM C0 CROSS JOIN ( VALUES (-1), ( 0), ( 1) ) AS D(k))SELECT MAX(CASE WHEN k = 0 THEN item END) AS Item, MAX(CASE WHEN k = 1 THEN price END) AS OldPrice, MAX(CASE WHEN k = 0 THEN price END) AS RangePrice, MAX(CASE WHEN k = 0 THEN PriceStartDate END) AS StartDate, MAX(CASE WHEN k = -1 THEN PriceStartDate END) AS EndDate FROM C1 GROUP BY itemid, grp_fct HAVING MIN(PriceStartDate) < MAX(PriceStartDate);--A Common SolutionWITH PriceCompare AS ( SELECT i.Item, ph.ItemId, ph.PriceStartDate, ph.Price, ROW_NUMBER() OVER (PARTITION BY ph.ItemId ORDER BY PriceStartDate) AS rownum FROM Items i JOIN PriceHistory ph ON i.ItemId = ph.ItemId)SELECT currow.Item, prevrow.Price AS OldPrice, currow.Price AS RangePrice, currow.PriceStartDate AS StartDate, nextrow.PriceStartDate AS EndDate FROM PriceCompare currow LEFT JOIN PriceCompare nextrow ON currow.rownum = nextrow.rownum - 1 AND currow.ItemId = nextrow.ItemId LEFT JOIN PriceCompare prevrow ON currow.rownum = prevrow.rownum + 1 AND currow.ItemId = prevrow.ItemId; SET STATISTICS IO ON;SET STATISTICS TIME ON;/*--Mohammad Salimabadi Solutio:Table 'PriceHistory'. Scan count 3, logical reads 6Table 'Items'. Scan count 1, logical reads 2--A Common SolutionTable 'PriceHistory'. Scan count 5, logical reads 48Table 'Items'. Scan count 1, logical reads 2*/[/code]Thu, 14 Apr 2011 08:38:08 GMT_ms65g_http://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxLooks like a very innovative and interesting approach.[quote][b]_ms65g_ (4/12/2011)[/b][hr]Can you demonstrate your technique for matching current rows with previous and next values is faster and more efficient as my best solution?[/quote]Can you propose a version of your script compatible with the benchmark tests? At which point I'll be glad to test your script and include the results.Best regards,David.Wed, 13 Apr 2011 00:30:37 GMTDavid McKinneyhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxYes, I cut down the other table to -1 and 0. If there's anything else I could try, very happy to :)I partitioned the rank function by a SeriesId as well, but otherwise ran Mohammad's solution. Any advantage in replacing the CTE with an indexed temp table?Tue, 12 Apr 2011 15:40:30 GMTAlistairNYhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspx[quote][b]AlistairNY (4/12/2011)[/b][hr]Well, in my application it seems about the same as the Outer Apply method. I only need the previous (not the next) day's value, for 5000 series of about 1000 elements. Finding those 5 million values and doing a brief calculation on them takes 3-4 mins on my laptop, with either method...[/quote]That's probably because the cross-join triples the set first. Did you use a modified cross-join with only -1, 0? I still expect that at large data sizes the cross-join will start to level out with the other methods - cross-joins are expensive. Still, the cross-join/pivot is a cool trick.Tue, 12 Apr 2011 15:36:20 GMTCade Rouxhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxWell, in my application it seems about the same as the Outer Apply method. I only need the previous (not the next) day's value, for 5000 series of about 1000 elements. Finding those 5 million values and doing a brief calculation on them takes 3-4 mins on my laptop, with either method...Tue, 12 Apr 2011 15:28:00 GMTAlistairNYhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspx[quote][b]_ms65g_ (4/12/2011)[/b]No, if we change the DENSE_RANK with ROW_NUMBER the result will not be correct. You can rum my first script at my first post with row_number. So you will see an empty result set.[/quote]I see what you are doing now, it is a re-grouping - basically like a pivot.Tue, 12 Apr 2011 13:39:04 GMTCade Rouxhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspx[quote][b]Cade Roux (4/12/2011)[/b][hr][quote][code="sql"];WITH C AS(SELECT seq_nbr, k, DENSE_RANK() OVER(ORDER BY seq_nbr ASC) + k AS grp_fct FROM [Sample] CROSS JOIN (VALUES (-1), (0), (1) ) AS D(k))[/code][/quote]I'm curious about your choice to use DENSE_RANK (which is identical to ROW_NUMBER and RANK when all rows are unique, but which I believe won't work when there are duplicates).Is DENSE_RANK any faster? And if so, why? I believe your technique should still work fine with ROW_NUMBER.I supposed that the real speed up of this version comes from the up front CROSS JOIN which mitigates the tendency of the engine to treat multiple uses of CTE which is self-joined independently resulting in multiple reads.[/quote]No, if we change the DENSE_RANK with ROW_NUMBER the result will not be correct. You can rum my first script at my first post with row_number. So you will see an empty result set.Tue, 12 Apr 2011 13:28:10 GMT_ms65g_http://www.sqlservercentral.com/Forums/Topic468501-362-1.aspx>>Hi Mohammad. I can run this on my data - right now it looks like it's picking out the current, previous and next 'date' or seq-nbr? What I'd need is the current, next and previous 'value', from those dates. Any chance of a tweak?A quick run of the code as is suggests it is much faster than the CTE and faster than the Outer Apply.<<No problem,Try this on:[code="sql"]DECLARE @Sample TABLE( seq_nbr INTEGER NOT NULL PRIMARY KEY, value INTEGER);INSERT @SampleVALUES (1, 5), (4, 9), (5, 8), (8, 20);;WITH C AS(SELECT seq_nbr, value, k, DENSE_RANK() OVER(ORDER BY seq_nbr ASC) + k AS grp_fct FROM @Sample CROSS JOIN (VALUES (-1), (0), (1) ) AS D(k))SELECT MAX(CASE WHEN k = 1 THEN value END) AS pre_value, MIN(CASE WHEN k = 1 THEN seq_nbr END) AS pre_seq, MAX(CASE WHEN k = 0 THEN value END) AS current_value, MAX(CASE WHEN k= 0 THEN seq_nbr END) AS current_seq, MAX(CASE WHEN k = -1 THEN value END) AS next_value, MAX(CASE WHEN k = -1 THEN seq_nbr END) AS next_seq FROM C GROUP BY grp_fctHAVING min(seq_nbr) < max(seq_nbr);/*pre_value pre_seq current_value current_seq next_value next_seq----------- ----------- ------------- ----------- ----------- -----------NULL NULL 5 1 9 45 1 9 4 8 59 4 8 5 20 88 5 20 8 NULL NULL*/[/code]Tue, 12 Apr 2011 13:22:23 GMT_ms65g_http://www.sqlservercentral.com/Forums/Topic468501-362-1.aspx[quote][code="sql"];WITH C AS(SELECT seq_nbr, k, DENSE_RANK() OVER(ORDER BY seq_nbr ASC) + k AS grp_fct FROM [Sample] CROSS JOIN (VALUES (-1), (0), (1) ) AS D(k))[/code][/quote]I'm curious about your choice to use DENSE_RANK (which is identical to ROW_NUMBER and RANK when all rows are unique, but which I believe won't work when there are duplicates).Is DENSE_RANK any faster? And if so, why? I believe your technique should still work fine with ROW_NUMBER.I supposed that the real speed up of this version comes from the up front CROSS JOIN which mitigates the tendency of the engine to treat multiple uses of CTE which is self-joined independently resulting in multiple reads.Tue, 12 Apr 2011 12:40:38 GMTCade Rouxhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxHi Mohammad. I can run this on my data - right now it looks like it's picking out the current, previous and next 'date' or seq-nbr? What I'd need is the current, next and previous 'value', from those dates. Any chance of a tweak?A quick run of the code as is suggests it is much faster than the CTE and faster than the Outer Apply.Tue, 12 Apr 2011 12:20:22 GMTAlistairNYhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxCan you demonstrate your technique for matching current rows with previous and next values is faster and more efficient as my best solution?See:[code="sql"]CREATE TABLE [Sample]( seq_nbr INTEGER NOT NULL PRIMARY KEY);INSERT [Sample]VALUES (1), (4), (5), (8);/* Wanted Resultseq_nbr seq_nbr seq_nbr----------- ----------- -----------NULL 1 41 4 54 5 85 8 NULL*/[b]--Mohammad Salimabadi Solution[/b]SET STATISTICS IO ONSET STATISTICS TIME ON;WITH C AS(SELECT seq_nbr, k, DENSE_RANK() OVER(ORDER BY seq_nbr ASC) + k AS grp_fct FROM [Sample] CROSS JOIN (VALUES (-1), (0), (1) ) AS D(k))SELECT MIN(seq_nbr) AS pre_value, MAX(CASE WHEN k = 0 THEN seq_nbr END) AS current_value, MAX(seq_nbr) AS next_value FROM C GROUP BY grp_fctHAVING min(seq_nbr) < max(seq_nbr);--Common Solution #1WITH C AS(SELECT seq_nbr, ROW_NUMBER() OVER(ORDER BY seq_nbr) AS rnk FROM [Sample])SELECT B.seq_nbr, A.seq_nbr, C.seq_nbr FROM C AS A LEFT JOIN C AS B ON A.rnk - 1 = B.rnk LEFT JOIN C ON A.rnk = C.rnk - 1;--Common Solution #2SELECT D1.seq_nbr, S.seq_nbr, D2.seq_nbr FROM [Sample] AS S OUTER APPLY ( SELECT TOP 1 seq_nbr FROM [Sample] WHERE seq_nbr < S.seq_nbr ORDER BY seq_nbr DESC ) AS D1 OUTER APPLY ( SELECT TOP 1 seq_nbr FROM [Sample] WHERE seq_nbr > S.seq_nbr ORDER BY seq_nbr ) AS D2; SET STATISTICS IO OFFSET STATISTICS TIME OFF/*[b]Mohammad Salimabadi Solution:[/b]Table 'Sample'. Scan count 1, logical reads 2Common Solution #1Table 'Sample'. Scan count 3, logical reads 20Common Solution #2Table 'Sample'. Scan count 9, logical reads 18*/[/code]Tue, 12 Apr 2011 10:04:16 GMT_ms65g_http://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxJust wanted to return to say I tried using Outer Apply as described and it was indeed faster for me too. Thanks all for your help.Mon, 03 Jan 2011 13:51:49 GMTAlistairNYhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxThat's very interesting David - thank you for investigating. Now to try and understand... :)Thu, 18 Nov 2010 06:27:16 GMTAlistairNYhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxok...so here are my results in csv format (sorry!) Conclusions follow.[code="other"]Full Resultset,Method,Execution Time,Client Processing Time,Total execution Time,Wait Time,Notes,CTE View,13 secs,3261,5759,2498,93% in Hash Match,Temp Table with rownumber,33 secs,21524,25422,3898,Index Scans,Table Variable with rownumber,47 secs,36798,39196,2398,,Temp Table with identity,16 secs,3269,8753,5484,Insert 81% Select 19%,Table Variable with identity,Cancelled after 1 hour,,,,,Cross Apply,12 secs,3795,4066,271,,,,,,,One Item,,,,,,,CTE View,2 secs,376,2179,1803,,Temp Table with rownumber,5 secs,170,5039,4869,Insert 95%,Table Variable with rownumber,4 secs,68,3380,3312,,Temp Table with identity,6 secs,62,6585,6523,I 86% / S 14%,Table Variable with identity,83 secs,78230,82767,4537,Insert = 100%!,Cross Apply,0 sesc,14,139,125,Wow!,,,,,,Price Rises,,,,,,,CTE View,8 secs,1586,4357,2771,,Temp Table with rownumber,19 secs,11213,15927,4714,,Table Variable with rownumber,17 secs,10231,12995,2764,Insert 100%,Temp Table with identity,12 secs,2138,8066,5928,Insert 83% Select 17%,Table Variable with identity,Not Run,,,,,Cross Apply,8 secs,3857,4111,254,[/code](The cross apply code was exactly as previously posted by Charles Gildawie.)I ran the code on Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Developer Edition on Windows NT 6.1 <X86> (Build 7600: ) Before running each code block I executed the following, aiming to ensure that no caching was taking place.[code="sql"]checkpointdbcc freeproccachedbcc dropcleanbuffers[/code]My conclusions are 1) that the Cross Apply solution comes out a clear winner when searching for an individual Item (although the CTE has very acceptable performance relative to the other solutions.)2) that any solutions using a table variable (as opposed to a temp table) do badly when using a identity column. Are they very slow to create identity values? In any case the equivalent code with row_number does much better.Final Conclusion (for now!)The Cross Apply and the CTE solutions stand clearly out from the bunch with the cup going to cross apply for it's performance on an individual item, and a special mention for the CTE 'cos it's much prettier ;-)Next Steps?Perf testing isn't my speciality; perhaps you can find fault with my approach. Or perhaps the code used to illustrate each method could be improved e.g. by indexing.I haven't talked about the execution plans. I'll leave that perhaps to one better versed.I hope this enlightens more than one, and goes someway towards ending speculation on which approach would scale better etc..Regards,David McKinney.Thu, 18 Nov 2010 04:16:20 GMTDavid McKinneyhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxExcellent...thanks, Jeff, for getting the ball rolling.I adapted your code to make it as per the example i.e. so that the article code would run without modif.Specifically, I created the PriceHistory table upfront with PK as per the article, and also created an Items table with a foreign key constraint between the two. (Also I created these as non-temporary tables - for no good reason.) At the end of the code block below is the CREATE VIEW exactly as in the article. There are subsequent code blocks to show other methods.I'm not finished yet...I just wanted to post some code before someone beat me to it :-DThe next steps are the following -1) post an example of the cross apply method.2) benchmark each method for different use cases.I propose the following use casesa) Entire table (as per select * FROM PriceCompare in the article.)b) History for a particular item e.g.WHERE Item='Item 512')c) Identify all increases in price. e.g. WHERE RangePrice>OldPrice.While I haven't done any benchmarking in earnest, I reckon for now that the CTE is holding its own on the million row dataset. But maybe with some appropriate indexing etc. the others may prove better.Regards,David McKinney.[code="sql"]IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PriceHistory_Items]') AND parent_object_id = OBJECT_ID(N'[dbo].[PriceHistory]'))ALTER TABLE [dbo].[PriceHistory] DROP CONSTRAINT [FK_PriceHistory_Items]GO IF OBJECT_ID('PriceHistory','U') IS NOT NULL DROP TABLE PriceHistory;GO IF OBJECT_ID('Items','U') IS NOT NULL DROP TABLE Items;GOCREATE TABLE [dbo].[Items]( [ItemId] [int] NOT NULL, [Item] [varchar](100) NOT NULL, CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED ( [ItemId] ASC))GOCREATE TABLE [dbo].[PriceHistory]( [ItemId] [int] NOT NULL, [PriceStartDate] [datetime] NOT NULL, [Price] [decimal](10, 2) NOT NULL, CONSTRAINT [PK_PriceHistory] PRIMARY KEY CLUSTERED ( [ItemId] ASC, [PriceStartDate] ASC))GO;WITHcteCreateData AS( SELECT TOP 1050000 ItemID = CAST(ABS(CHECKSUM(NEWID()))%5000+1 AS INT), PriceStartDate = DATEADD(dd,ABS(CHECKSUM(NEWID()))%(DATEDIFF(dd,'2010','2020')),'2010'), Price = CAST((ABS(CHECKSUM(NEWID()))%1000)/100.0 +1 AS DECIMAL(9,2)) FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2),cteDeleteDupes AS( SELECT Occurance = ROW_NUMBER() OVER (PARTITION BY ItemID, PriceStartDate ORDER BY ItemID, PriceStartDate), ItemID, PriceStartDate, Price FROM cteCreateData) INSERT INTO PriceHistory ( ItemID, PriceStartDate, Price) SELECT TOP 1000000 ItemID, PriceStartDate, Price FROM cteDeleteDupes WHERE Occurance = 1;GOINSERT INTO dbo.Items(ItemId,Item)select ItemID, 'Item ' + CAST(ItemID as varchar) FROM PriceHistoryGROUP BY ItemIDGOALTER TABLE [dbo].[PriceHistory] WITH CHECK ADD CONSTRAINT [FK_PriceHistory_Items] FOREIGN KEY([ItemId])REFERENCES [dbo].[Items] ([ItemId])GO/****** Object: View [dbo].[PriceCompare] Script Date: 11/16/2010 14:21:14 ******/IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[PriceCompare]'))DROP VIEW [dbo].[PriceCompare]GOCREATE VIEW [dbo].[PriceCompare] ASWITH PriceCompare AS (SELECT i.Item, ph.ItemId, ph.PriceStartDate, ph.Price, ROW_NUMBER() OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum FROM Items i INNER JOIN PriceHistory ph ON i.ItemId = ph.ItemId) SELECT currow.Item, prevrow.Price AS OldPrice, currow.Price AS RangePrice, currow.PriceStartDate AS StartDate, nextrow.PriceStartDate AS EndDate FROM PriceCompare currow LEFT JOIN PriceCompare nextrow ON currow.rownum = nextrow.rownum - 1 AND currow.ItemId = nextrow.ItemId LEFT JOIN PriceCompare prevrow ON currow.rownum = prevrow.rownum + 1 AND currow.ItemId = prevrow.ItemId[/code]The next code block shows the creation of a temporary table exactly equivalent to PriceCompare CTE in the View. The temporary table is populated once, and then joined with itself.[code="sql"]-- Replacing the CTE with an entirely equivalent temporary table.IF OBJECT_ID('tempdb.dbo.#NumberedPriceHistory') is not nulldrop table #NumberedPriceHistoryGOCREATE TABLE #NumberedPriceHistory( PRIMARY KEY (ItemId,rownum) ,Item varchar(100),ItemId int, PriceStartDate datetime, Price decimal(9,2), rownum int)GO--CREATE NONCLUSTERED INDEX idxNumberedPriceHistory_Item--ON [dbo].[#NumberedPriceHistory] ([Item])--INCLUDE ([ItemId],[PriceStartDate],[Price],[rownum])--GOINSERT INTO #NumberedPriceHistorySELECT i.Item, ph.ItemId, ph.PriceStartDate, ph.Price,ROW_NUMBER() OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum FROM Items i INNER JOIN PriceHistory ph ON i.ItemId = ph.ItemIdGOSELECT currow.Item, prevrow.Price AS OldPrice, currow.Price AS RangePrice, currow.PriceStartDate AS StartDate, nextrow.PriceStartDate AS EndDate FROM #NumberedPriceHistory currow LEFT JOIN #NumberedPriceHistory nextrow ON currow.rownum = nextrow.rownum - 1 AND currow.ItemId = nextrow.ItemIdLEFT JOIN #NumberedPriceHistory prevrow ON currow.rownum = prevrow.rownum + 1 AND currow.ItemId = prevrow.ItemId where currow.Price>prevrow.Price--where currow.Item='Item 512'[/code]The third code block shows a temp table but this time with an identity column to replace the row number. The insert into this table is sorted - is that OK?[code="sql"]-- Replacing the CTE with a table with an identity columnIF OBJECT_ID('tempdb.dbo.#PriceHistoryWithIdentity') is not nulldrop table #PriceHistoryWithIdentityGOCREATE TABLE #PriceHistoryWithIdentity(PRIMARY KEY (rownum),rownum int identity(1,1),Item varchar(100),ItemId int, PriceStartDate datetime, Price decimal(9,2))GOINSERT INTO #PriceHistoryWithIdentity(Item,ItemId, PriceStartDate, Price)SELECT i.Item, ph.ItemId, ph.PriceStartDate, ph.PriceFROM Items i INNER JOIN PriceHistory ph ON i.ItemId = ph.ItemIdorder by i.Item, ph.PriceStartDateGOSELECT currow.Item, prevrow.Price AS OldPrice, currow.Price AS RangePrice, currow.PriceStartDate AS StartDate, nextrow.PriceStartDate AS EndDate FROM #PriceHistoryWithIdentity currow LEFT JOIN #PriceHistoryWithIdentity nextrow ON currow.rownum = nextrow.rownum - 1 AND currow.ItemId = nextrow.ItemIdLEFT JOIN #PriceHistoryWithIdentity prevrow ON currow.rownum = prevrow.rownum + 1 AND currow.ItemId = prevrow.ItemIdwhere currow.Price>prevrow.Price[/code]Tue, 16 Nov 2010 06:39:26 GMTDavid McKinneyhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspx[quote][b]David McKinney (11/13/2010)[/b][hr]I meant that specifically the different approaches to the "previous / next row" problem haven't been compared side by side. [/quote]Exactly. Here's code to build a million row table with 5000 ItemID's and around 200 price changes for each item over a ten year period. Let the races begin. In the mean time, no claims of performance should be made because it hasn't been proven on THIS thread.[code="sql"]--===== Conditionally drop and rebuild a test table to make reruns easier. -- This whole thing takes about 35 seconds to run. IF OBJECT_ID('tempdb..#PriceHistory','U') IS NOT NULL DROP TABLE #PriceHistory;GOWITHcteCreateData AS( SELECT TOP 1050000 ItemID = CAST(ABS(CHECKSUM(NEWID()))%5000+1 AS INT), PriceStartDate = DATEADD(dd,ABS(CHECKSUM(NEWID()))%(DATEDIFF(dd,'2010','2020')),'2010'), Price = CAST((ABS(CHECKSUM(NEWID()))%1000)/100.0 +1 AS DECIMAL(9,2)) FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2),cteDeleteDupes AS( SELECT Occurance = ROW_NUMBER() OVER (PARTITION BY ItemID, PriceStartDate ORDER BY ItemID, PriceStartDate), ItemID, PriceStartDate, Price FROM cteCreateData) SELECT TOP 1000000 ItemID, PriceStartDate, Price INTO #PriceHistory FROM cteDeleteDupes WHERE Occurance = 1;[/code]Sat, 13 Nov 2010 18:36:06 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxSorry 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.Sat, 13 Nov 2010 09:39:42 GMTDavid McKinneyhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspx[quote][b]David McKinney (11/12/2010)[/b][hr]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.[/quote]Paul puts APPLY though some rigorous proofs in his articles. Don't trust me, I'm only a doctor :cool:Fri, 12 Nov 2010 09:39:47 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxThanks - 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,AlistairFri, 12 Nov 2010 09:38:57 GMTAlistairNYhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxThanks 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.Fri, 12 Nov 2010 09:37:41 GMTDavid McKinneyhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspx[quote][b]David McKinney (11/12/2010)[/b][hr]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.[/quote]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.Fri, 12 Nov 2010 09:17:04 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxAlistair,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.Fri, 12 Nov 2010 09:10:38 GMTDavid McKinneyhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxThanks - Mr Charlie says Outer Apply might allow more efficient index use:[quote][b]charles.gildawie (8/20/2010)[/b][hr]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 APPLYExample:[code]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[/code]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.[/quote]Fri, 12 Nov 2010 06:04:04 GMTAlistairNYhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspx[quote][b]alistairgthomas (11/11/2010)[/b][hr]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?[/quote]Hi Alistair, would you mind providing a page number or link to Charles's Outer Apply suggestion (or quote it.)Thanks,David.Fri, 12 Nov 2010 00:16:57 GMTDavid McKinneyhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxOK, 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?Thu, 11 Nov 2010 16:14:07 GMTAlistairNYhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxI 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.Tue, 09 Nov 2010 14:39:36 GMTAlistairNYhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspx[quote][b]Robert Carretta (9/9/2010)[/b][hr]David,I have used Table Variables to accomplish the same thing. Are there a drawbacks to using table variables instead of CTEs? Are there advantages to using CTEs?[/quote]Table variables do not and cannot be made to generate any statistics. They are always evaluated like any other variable... one row... no matter how many they have. Table variables are NOT memory only like a lot of folks think. When they get too big, the flip over to Temp DB (same as a Temp Table). The only advantage a table variable may have is providing an interim result set to split larger queries. I'd rather use a Temp table (which starts in memory just like a table variable) just because they persist for troubleshooting in SSMS and can be a LOT faster if SELECT/INTO is used.Non recursive CTE's are no different than derived tables or views except they're more like a view in that they can be self joined and joined more than once (with the same performance problems when that happens).There's ton's of other information about the differences. Is there something specific you wanted to know about Table Variables and "other" options?Thu, 09 Sep 2010 15:32:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxDavid,I have used Table Variables to accomplish the same thing. Are there a drawbacks to using table variables instead of CTEs? Are there advantages to using CTEs?Thu, 09 Sep 2010 08:41:18 GMTRobert Carrettahttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspx[quote][b]LIYA (9/1/2010)[/b][hr]Hi Peter,At present the testing table have 1628430 records. its' only a 5 month data. but in production it will be 5 time higher. It's a access control and attendance database, the application is having it's own interface and reports. the client wants to generate some custmised reports. the existing application is not directly accessing sql server. it's using BDE (Bolrland Database Engine] to access the database. so i can't modify anything on existing database. it will affect the existing application. i just use a view with above specified fileds to generate the report. :-)[/quote]It seems that this type of punch in/out report is something that would only be segmented on a daily or weekly basis. Since you only need the 3 columns, for performance reasons you may want to create a compound index on (transacttiondate, empid, transactiontype), so you can easily filter on a specific date range while covering the query. Actually, I'd expect that column combination to be the primary key.Wed, 01 Sep 2010 07:58:02 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxHi Liya (aka anudata!),Given the volumes of data involved, I suggest you get familiar with the other approaches available in tackling this problem. If you leaf through the discussion pages, you can see that there are many. Temp tables with identity fields come particularly to mind (instead of the rownumber.) The advantage of the CTE approach is its elegance and its ease of use in building up the layers of a solution. However it will not be the fastest solution over large datasets. (Except maybe if you're always reporting on one employee at a time.)I'm glad the solution I provided is working for you, but make sure it performs well with the 5* data load, and if it does make sure it works with 50* data load to give it some future proofing.Regards,David.Wed, 01 Sep 2010 06:04:42 GMTDavid McKinneyhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxHi Peter,At present the testing table have 1628430 records. its' only a 5 month data. but in production it will be 5 time higher. It's a access control and attendance database, the application is having it's own interface and reports. the client wants to generate some custmised reports. the existing application is not directly accessing sql server. it's using BDE (Bolrland Database Engine] to access the database. so i can't modify anything on existing database. it will affect the existing application. i just use a view with above specified fileds to generate the report. :-)Wed, 01 Sep 2010 05:41:58 GMTLIYAhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspx[quote][b]LIYA (9/1/2010)[/b][hr]Hi Peter, The query is working fine; the only disadvantage is its execution time. It took long time to execute.David's solution is much faster as compared to this. Thank you very much both of you. [/quote]Odd, it should not work that slow given you added the index I wrote about. The index should speed up any well written query for this particular task as it indexes the precise fields being searched on.I will try out both solutions myself, maybe I do learn something new myself ;)Can you have any indication on how large your dataset is (in records)?Wed, 01 Sep 2010 05:07:27 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxHi Peter, The query is working fine; the only disadvantage is its execution time. It took long time to execute.David's solution is much faster as compared to this. Thank you very much both of you. Wed, 01 Sep 2010 04:52:48 GMTLIYAhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspx[quote][b]LIYA (9/1/2010)[/b][hr]Thank u for your Help. This query helps me lot. but still have some problem.for example i have an output with above query[u]EMPID[/u] [u]InTime[/u] [u]OutTime[/u]21851 2010-04-20 07:52:07.000 2010-04-20 16:32:13.00021851 2010-04-21 19:20:37.000 2010-04-28 16:41:10.00021851 2010-04-25 18:24:56.000 2010-04-28 16:41:10.00021851 2010-04-26 06:31:03.000 2010-04-28 16:41:10.00021851 2010-04-28 08:03:28.000 2010-04-28 16:41:10.00021851 2010-04-28 16:40:52.000 2010-04-28 16:41:10.00021851 2010-05-02 08:19:48.000 2010-05-02 15:34:10.000Here the employee have IN transaction on 21,25,26 but no OUT on these dates. So it's taking the OUT Time for those IN as 2010-04-28 16:41:10.000. how to avoid this. i need blank or null for these out.:w00t:[/quote]Look at the version I posted, that should not have this problem as I anticipated it from the data you presented.Wed, 01 Sep 2010 03:48:55 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxThank you David for your quick replay. Your query solved my problem.:-)Wed, 01 Sep 2010 02:18:16 GMTLIYAhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspx@ peter SSC EnthusiasticI apologise for the upper case letters in my last post,I was multitasking it wasn't meant to be rudeWed, 01 Sep 2010 02:07:14 GMTniyinkshttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxanudata...first point is that a lot of people won't answer your post because you haven't made the effort to script the data. Look at how Eric did it above. If you do that once, then we don't have to. If you don't do it, then each of us has to. Get it?So thanks Eric for scripting a table - if he hadn't done so I wouldn't be answering your post!Below is a CTE approach, akin to that shown in the article. I'll pass no comment in this post on performance pros and cons, that's been done more than adequately in the previous pages. I've included a second employee, as it's I suppose essential to any solution that you don't mix up INs and OUTs for different employees. This being so, its essential also to include the second employee in your test data.[code]if object_id('tempdb.dbo.#timecard') is not null drop table #timecardgocreate table #timecard ( primary key ( empid, Transactiondate, transactionType ) , empid int not null , TransactionDate smalldatetime not null , transactionType char(3) not null ) ;insert into #timecard select 1001 , '2010-06-18 07:33:17' , 'IN' union all select 1001 , '2010-06-18 07:40:18' , 'OUT' union all select 1001 , '2010-06-18 10:40:10' , 'IN' union all select 1001 , '2010-06-18 10:45:17' , 'IN' union all select 1001 , '2010-06-18 12:45:17' , 'OUT' union all select 9999 , '2010-06-18 10:14:10' , 'IN' union all select 9999 , '2010-06-18 12:40:10' , 'OUT' ;with numberedset as ( select row_number() OVER ( partition by empid order by TransactionDate asc ) as rownum , empid , TransactionDate , transactionType FROM #timecard t ), outs as ( select * FROM numberedset where transactionType = 'OUT' ), pairs as ( SELECT therow.rownum , therow.empid , therow.TransactionDate as TransactionDate , therow.transactionType as TransactionType , thenextrow.TransactionDate as NextTransactionDate , thenextrow.transactionType as NextTransactionType FROM numberedset therow left join outs thenextrow on therow.empid = thenextrow.empid and therow.rownum + 1 = thenextrow.rownum ) SELECT * FROM pairs where TransactionType = 'IN'[/code]Wed, 01 Sep 2010 00:59:01 GMTDavid McKinneyhttp://www.sqlservercentral.com/Forums/Topic468501-362-1.aspxThank u for your Help. This query helps me lot. but still have some problem.for example i have an output with above query[u]EMPID[/u] [u]InTime[/u] [u]OutTime[/u]21851 2010-04-20 07:52:07.000 2010-04-20 16:32:13.00021851 2010-04-21 19:20:37.000 2010-04-28 16:41:10.00021851 2010-04-25 18:24:56.000 2010-04-28 16:41:10.00021851 2010-04-26 06:31:03.000 2010-04-28 16:41:10.00021851 2010-04-28 08:03:28.000 2010-04-28 16:41:10.00021851 2010-04-28 16:40:52.000 2010-04-28 16:41:10.00021851 2010-05-02 08:19:48.000 2010-05-02 15:34:10.000Here the employee have IN transaction on 21,25,26 but no OUT on these dates. So it's taking the OUT Time for those IN as 2010-04-28 16:41:10.000. how to avoid this. i need blank or null for these out.:w00t:Wed, 01 Sep 2010 00:44:19 GMTLIYA