Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a SQL table of data with the following structure :

CREATE TABLE Data(
    Id uniqueidentifier NOT NULL,
    Date datetime NOT NULL,
    Value decimal(20, 10) NULL,
    RV timestamp NOT NULL,
 CONSTRAINT PK_Data PRIMARY KEY CLUSTERED (Id, Date)
)

The number of distinct Ids ranges from 3000 to 50000.
The size of the table varies up to over a billion rows.
One Id can cover between a few rows up to 5% of the table.

The single most executed query on this table is :

SELECT Id, Date, Value, RV
FROM Data
WHERE Id = @Id
AND Date Between @StartDate AND @StopDate

I now have to implement incremental retrieval of data on a subset of Ids, including updates.
I then used a request scheme in which the caller provide a specific rowversion, retrieve a block of data and use the maximum rowversion value of the returned data for the subsequent call.

I have written this procedure :

CREATE TYPE guid_list_tbltype AS TABLE (Id uniqueidentifier not null primary key)
CREATE PROCEDURE GetData
    @Ids guid_list_tbltype READONLY,
    @Cursor rowversion,
    @MaxRows int
AS
BEGIN
    SELECT A.* 
    FROM (
        SELECT 
            Data.Id,
            Date,
            Value,
            RV,
            ROW_NUMBER() OVER (ORDER BY RV) AS RN
        FROM Data
             inner join (SELECT Id FROM @Ids) Ids ON Ids.Id = Data.Id
        WHERE RV > @Cursor
    ) A 
    WHERE RN <= @MaxRows
END

Where @MaxRows will range between 500,000 and 2,000,000 depending on how chunked the client will want his data.


I have tried different approaches :

  1. Indexing on (Id, RV) :
    CREATE NONCLUSTERED INDEX IDX_IDRV ON Data(Id, RV) INCLUDE(Date, Value);

Using the index, the query seek the rows where RV = @Cursor for each Id in @Ids, read the following rows then merge the result and sort.
Efficiency then depends on the relative position of @Cursor value.
If it is close to the end of data (ordered by RV) the query is instantaneous and if not the query can take up to minutes (never let it run to the end).

the problem with this approach is that @Cursor is either near the end of data and the sort isn't painful (not even needed if the query return less rows than @MaxRows) either it is further behind and the query has to sort @MaxRows * LEN(@Ids) rows.

  1. Indexing on RV :
    CREATE NONCLUSTERED INDEX IDX_RV ON Data(RV) INCLUDE(Id, Date, Value);

Using the index, the query seek the row where RV = @Cursor then read every row discarding the non-requested Ids until it reaches @MaxRows.
Efficiency then depends on the % of requested Ids (LEN(@Ids) / COUNT(DISTINCT Id)) and their distribution.
More requested Id % means less discarded rows which means more efficient reads, less requested Id % means more discarded rows which means more reads for the same amount of resulting rows.

The problem with this approach is that if the requested Ids contains only a few elements, it might have to read the whole index to get the desired rows.

  1. Using Filtered index or indexed views
    CREATE NONCLUSTERED INDEX IDX_RVClient1 ON Data(Id, RV) INCLUDE(Date, Value)
    WHERE Id IN (/* list of Ids for specific client*/);

Or

    CREATE VIEW vDataClient1 WITH SCHEMABINDING
    AS
    SELECT
        Id,
        Date,
        Value,
        RV
    FROM dbo.Data
    WHERE Id IN (/* list of Ids for specific client*/)
    CREATE UNIQUE CLUSTERED INDEX IDX_IDRV ON vDataClient1(Id, Rv);

This method allows for perfectly efficient indexing and query execution plans but come with disadvantages : 1. Practically, I will have to implement dynamic SQL to create the indexes or views and modify the requesting procedure to use the right index or view. 2. I will have to maintain one index or view by existing client, including storage. 3. Every time a client will have to modify his list of requested Ids, I will have to drop the index or view and recreate it.


I can't seem to find a method that will suit my needs.
I'm looking for better ideas to implement incremental data retrieval. Those ideas could imply reworking the requesting scheme or the database schema although I'd prefer a better indexing approach if there is one.

share|improve this question
    
Crosspost with stackoverflow.com/questions/11586004/…;. I've removed Oracle version for the moment because I've discovered that ORA_ROWSCN isn't indexable (and hardly through indexed materialized views). –  Paciv Aug 7 '12 at 17:48
    
How does the date field fit in? Can a row with a particular ID and Date be updated in the table? And if so, is the date also updated (like an additional timestamp?) –  8kb Aug 7 '12 at 19:34
    
Seems like for GetData() attempt, the order by should include the Id (order by RV, Id). Can you comment on using an index of (Rv, Id)? Also using ">" max rowversion from previous call seems like it will miss records between chunks if rows have the same rowversion (isn't that possible?). –  crokusek Aug 8 '12 at 6:14
    
@8kb : the update statements that run on the table only modify the Value column. @crokusek : Won't ordering by RV, ID instead of RV only increase sort workload without any benefice, I don't understand the reasoning behind your comment. From what I've read, RV should be unique unless inserting data specifically into that column, which the application does not. –  Paciv Aug 8 '12 at 10:17
    
Can the client accept results in (Id, Rv) order and provide a LastId argument in addition to LastRowVersion argument to eliminate RV sort across ids? My previous comments were all based on the assumption that RV had duplicates. The filtered index per client looked interesting. –  crokusek Aug 9 '12 at 6:42
show 2 more comments

2 Answers

One solution is for the client application to remember the maximum rowversion per ID. The user-defined table type would change to:

CREATE TYPE
    dbo.guid_list_tbltype
AS TABLE 
    (
    Id      uniqueidentifier PRIMARY KEY, 
    LastRV  rowversion NOT NULL
    );

The query in the procedure can then be rewritten to use the APPLY pattern (see my SQLServerCentral articles part 1 and part 2 - free login required). The key to good performance here is the ORDER BY - it avoids unordered pre-fetching on the nested loops join. The RECOMPILE is necessary to allow the optimizer to see the cardinality of the table variable at compilation time (probably resulting in a desirable parallel plan).

ALTER PROCEDURE dbo.GetData

    @IDs        guid_list_tbltype READONLY,
    @MaxRows    bigint

AS
BEGIN

    SELECT TOP (@MaxRows)
        d.Id,
        d.[Date],
        d.Value,
        d.RV
    FROM @Ids AS i
    CROSS APPLY
    (
        SELECT
            d.*
        FROM dbo.Data AS d
        WHERE
            d.Id = i.Id
            AND d.RV > i.LastRV
    ) AS d
    ORDER BY
        i.Id,
        d.RV
    OPTION (RECOMPILE);

END;

You should get a post-execution query plan like this (estimated plan will be serial):

query plan

share|improve this answer
    
I've read some on CROSS APPLY (partly thanks to your blog posts) and I see that this query does improve performance when applying the TOP operator as it will only apply the sub-query until @MaxRows rows are fetched from it, using the Ids list as seed. The problem is that it doesn't account for the constraint of "incremental chunked data retrieval", as I can't use the MAX(RV) from the result-set of this query to perform subsequent query and have no row leftover. –  Paciv Oct 31 '12 at 15:21
    
I'll try to be clearer but all in one, the query must be asserted by : SELECT COUNT(*) FROM Data d WHERE d.RV BETWEEN @Cursor AND (SELECT MAX(RV) FROM (QUERY)) AND d.Id IN (SELECT Id FROM @Ids) must return exactly @MaxRows. –  Paciv Oct 31 '12 at 15:27
    
Updated my answer, thanks for the clarification. –  Paul White Nov 1 '12 at 11:24
    
Right, one of the design change solution is to have the client remember the MAX(RV) per Id (or a subscription system where the internal application remembers all Id/RV pairs) and I use this patern for an other client. An other solution was to force the client to always retrieve all the Ids (which make the indexing problem trivial). It still doesn't cover the question particular need : Incremental retrieval of a subset of Ids with only one global counter provided by the client. –  Paciv Nov 2 '12 at 10:20
    
I see. Well I can't think of a good way to meet that requirement, so I'll leave my answer as it is. Good luck with this anyway :) –  Paul White Nov 2 '12 at 12:02
show 1 more comment

If possible, I would redesign the table. If we can have VersionNumber as an incremental integer with no gaps, that the task of retrieving the next chunk is a totally trivial range scan. All we need is the following index:

CREATE NONCLUSTERED INDEX IDX_IDRV ON Data(Id, VersionNumber) INCLUDE(Date, Value);

Of course, we need to make sure that VersionNumber starts with one and has no gaps. This is easy to do with constraints.

share|improve this answer
    
Do you mean a global or an Id local VersionNumber ? Either case, I'm unable to see how that will help with the question, could you elaborate further ? –  Paciv Nov 2 '12 at 10:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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