-1

I have designed an sql database to keep historical data of changes. I am tracking either full copy of my object or the change since the last version (very similar to incremental changes). I need to query the data based on date filters, I need to return all the versions in the specified date range to be able to recover all the versions in the specific range. Here is the structure example.

CREATE TABLE _Chenges(
dates datetime, 
IsFull BIT, 
ObjectID INT, 
changes XML
)
GO

INSERT INTO _Chenges(dates, IsFull, objectID, changes)
VALUES 
('2010-01-01', 1, 1, '<FullChange></FullChange>'),
('2010-01-02', 1, 1, '<FullChange></FullChange>'),
('2010-01-03', 0, 1, '<IncrementalChange></IncrementalChange>'),
('2010-01-04', 0, 1, '<IncrementalChange></IncrementalChange>'),
('2010-01-05', 0, 1, '<IncrementalChange></IncrementalChange>'),
('2010-01-01', 1, 2, '<FullChange></FullChange>'),
('2010-01-02', 0, 2, '<IncrementalChange></IncrementalChange>'),
('2010-01-03', 1, 2, '<FullChange></FullChange>'),
('2010-01-04', 0, 2, '<IncrementalChange></IncrementalChange>'),
('2010-01-05', 0, 2, '<IncrementalChange></IncrementalChange>')

Now if I want to retrieve the data in between 2010-01-03 and 2010-01-04 I need to get following results.

    dates                  IsFull ObjectID    changes
-----------------------    ------ ----------- ---------
2010-01-02 00:00:00.000    1      1           <FullChange />
2010-01-03 00:00:00.000    0      1           <IncrementalChange />
2010-01-04 00:00:00.000    0      1           <IncrementalChange />
2010-01-03 00:00:00.000    1      2           <FullChange />
2010-01-04 00:00:00.000    0      2           <IncrementalChange />

For objectID = 1 I need 2, 3, 4 rows to be able to recover all the version in the specified range (the last full version and all the changes till the end of range), and for ObjectID = 2 I need only 3, 4 rows (the last Full is already included in the 3rd version).

Is it possible to write a query that will access the table only once (without self-joins and self-referencing sub-queries)?

I have tried following query, but I am looking for more optimized one

; WITH cte AS (
SELECT 
ROW_NUMBER() OVER(PARTITION BY ObjectID ORDER BY dates DESC) AS rownum, *
FROM dbo._Chenges
WHERE IsFull = 1
AND dates <= '2010-01-03'
)

SELECT _Chenges.*
FROM cte
JOIN dbo._Chenges ON _Chenges.ObjectID = cte.ObjectID
AND _Chenges.dates >= cte.dates
AND _Chenges.dates <= '2010-01-04 '
WHERE cte.rownum = 1
  • Are there any indexes on the table currently? – Erik Darling Nov 20 '17 at 13:18
  • @sp_BlitzErik No, I want first to optimize the query, and only after that create indexes. – Artashes Khachatryan Nov 20 '17 at 13:47
  • 2
    Crazy question: How will you know which query is optimal without indexing? – Erik Darling Nov 20 '17 at 13:49
  • @sp_BlitzErik if the query is accessing the table only once it is optimal query and you can start indexing your table. – Artashes Khachatryan Nov 20 '17 at 13:52
  • Oh, interesting. Best of luck. – Erik Darling Nov 20 '17 at 13:52
0

Not sure there is going to be anything other than a Temp table version which would eliminate a second lookup against the base table but even that version is likely to need a second lookup to potentially find any FULL changes made that occurred prior to your Date range, there are some Very clever people on here though so they may come up with something better than I have.

I wrote a Cross apply version that you may find helpful it will need some testing , for the test data supplied this was more efficient in terms of Scans and Reads but it still accesses the table more than once.

   SELECT 
    [_Chenges].[Dates],
    [_Chenges].[IsFull],
    [ObjectID],
    [_Chenges].[Changes]
    FROM [_Chenges] 
    CROSS APPLY (SELECT 
               MAX([Dates]) AS LastFullChange, --Get the most recent FULL before the specified range for ObjectID
               ObjectID AS FULLOBJECTID
               FROM [_Chenges] Fulls
               WHERE [_Chenges].ObjectID = Fulls.ObjectID
               AND [Fulls].[Dates] <= '2010-01-03' --This is the lower date range (looking for a FULL before this date)
               AND [Fulls].[IsFull] = 1
               GROUP BY ObjectID
               ) AS LastFulls
    WHERE ([Dates] >= '2010-01-03' AND [Dates] <= '2010-01-04') OR 
    ([Dates] = [LastFulls].[LastFullChange] AND [_Chenges].[ObjectID] = [LastFulls].[FULLOBJECTID])
    ORDER BY 
    LastFullChange ASC,
    Dates ASC

here are the stats:

OP version: 
Table 'Worktable'. Scan count 8, logical reads 21
Table '_Chenges'. Scan count 2, logical reads 2

Cross apply version: 
Table 'Worktable'. Scan count 0, logical reads 0
Table '_Chenges'. Scan count 2, logical reads 2

It may be worth while testing with a larger data set as these could scale completely differently , may also help in deciding which indexes would work best for once the data has grown out.

0

Here is another try which accesses the table once. But not sure about the performance!! (Two sort operators)

Change the dates according to your need.

BEGIN TRAN 
CREATE TABLE #Chenges(
dates DATETIME, 
IsFull BIT, 
ObjectID INT, 
changes XML
)
GO

INSERT INTO #Chenges(dates, IsFull, objectID, changes)
VALUES 
('2010-01-01', 1, 1, '<FullChange></FullChange>'),
('2010-01-02', 1, 1, '<FullChange></FullChange>'),
('2010-01-03', 0, 1, '<IncrementalChange></IncrementalChange>'),
('2010-01-04', 0, 1, '<IncrementalChange></IncrementalChange>'),
('2010-01-05', 0, 1, '<IncrementalChange></IncrementalChange>'),
('2010-01-01', 1, 2, '<FullChange></FullChange>'),
('2010-01-02', 0, 2, '<IncrementalChange></IncrementalChange>'),
('2010-01-03', 1, 2, '<FullChange></FullChange>'),
('2010-01-04', 0, 2, '<IncrementalChange></IncrementalChange>'),
('2010-01-05', 0, 2, '<IncrementalChange></IncrementalChange>')

; WITH cte AS (
SELECT CASE WHEN ISfull=1 THEN 
ROW_NUMBER() OVER(PARTITION BY IsFull,ObjectID ORDER BY dates DESC) ELSE 0 END AS rownum, *
FROM #Chenges
)
SELECT * FROM cte
WHERE dates>='20100102'
AND dates<='20100104'
AND rownum<2
ORDER BY objectid,cte.rownum desc


ROLLBACK

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.