1

My goal is to return X rows from a table, where the value of X is some value in the low 100's. For the purposes of testing this query, I am experimenting with different X values.

This is a take on pagination for populating a grid in a user interface. I tried using Offset / Fetch in this query, but I could not get it to function with my @Partial variable.

@Partial is a string that will identify the starting row number and all row numbers that follow the first match. This is pretty simple until @Partial has returned the last row. In that instance, I want to return the last row plus the previous X - 1 rows.

My query appears to be functioning, but I feel that I'm over thinking the problem. I have done hours of testing and online research. I have provided a full working query that will execute.

When @Partial is null, then the query abides by the @StartRow and @NumWindowRows variables. Otherwise, @NumWindowRows will be returned, starting or ending with the matched string.

Thanks for any assistance on this query. To recap, I am getting my intended results, but I am looking for guidance on a simpler approach or method to improve efficiency.

/***
Dynamic variables
******/
DECLARE 
@StartRowNum INT = 1
,@NumWindowRows INT = 3
,@Partial NVARCHAR(10) = 'M';

/*****
Test table
*******/
DECLARE @Customers TABLE (
    CustomerID VARCHAR(10)
    ,[Name] VARCHAR(155)
    );
/***
Generate test data
*****/
INSERT INTO @Customers(CustomerID, [Name])
SELECT Char(number+65), 'Customer ' +Char(number+65) 
  FROM master.dbo.spt_values
 WHERE name IS NULL AND 
       number < 26

/***
Return @NumWindowRows
******/

;WITH FullResult AS (
        SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
        ,COUNT(*) OVER () AS FullCount
        ,CustomerID
        ,[Name]
        FROM @Customers),
PartialResult AS (SELECT *, COUNT(*) OVER () AS FullCount
        FROM 
        (SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
            ,CustomerID
            ,[Name]
         FROM @Customers) c
         WHERE CustomerID >= @Partial)

SELECT f.*
FROM FullResult f
LEFT OUTER JOIN PartialResult p ON f.RowNum = p.RowNum
WHERE (@Partial IS NULL AND f.RowNum >= @StartRowNum
        AND f.RowNum <= @NumWindowRows + @StartRowNum -1)
OR (f.RowNum >= CASE 
                WHEN (SELECT MAX(FullCount) FROM PartialResult) < @NumWindowRows
                THEN ((SELECT MIN(RowNum) FROM PartialResult) - (@NumWindowRows - (SELECT MAX(FullCount) FROM PartialResult)))
                ELSE (SELECT MIN(RowNum) FROM PartialResult)    END
        AND f.RowNum <= CASE 
                WHEN (SELECT MAX(FullCount) FROM PartialResult) > @NumWindowRows
                THEN ((SELECT MAX(RowNum) FROM PartialResult) - ((SELECT MAX(FullCount) FROM PartialResult) - @NumWindowRows))
                WHEN (SELECT MAX(FullCount) FROM PartialResult) + @StartRowNum < @NumWindowRows
                THEN f.FullCount
                ELSE (SELECT MAX(RowNum) FROM PartialResult) END
            )
  • Thank you for providing test data, but the test data is almost certainly so far from your real data that any conclusions about performance using it won't be applicable to your real table. Table variables don't have statistics and you haven't defined any indexes. About how many rows does your real table have? What is the table structure along with indexes? Do you really need to return the full table count every time in every row? How often does the table count change? – Joe Obbish Oct 28 '17 at 23:16
  • @JoeObbish The test data is very similar to the production table. There are some extra fields in the table, but CustomerID is the primary key. The purpose of the procedure is to fetch pages of data for the lookup control. When there are hundreds/thousands of rows, then I will retrieve in chunks. When the user wants to jump to "P", for instance, then Partial will be = "P". Yes, the client side will need to know how many rows are in the table so that it knows when the end has been reached. Aaron has provided help in simplifying my query. Thank you for looking into this. – Gary Oct 29 '17 at 16:12
1

Okay, so we need to find out how many rows are in the table, and how many rows are left once we've moved to the marker defined by @Partial.

-- simple variable to hold the offset:
DECLARE @offset int;

;WITH x(o,c) AS 
(
  SELECT 
    -- how many rows are at or past the @Partial marker?
    SUM(CASE WHEN CustomerID >= @Partial THEN 1 END),
    -- how many rows total?
    COUNT(*) 
  FROM @Customers
 ) 
 -- the magic: a conditional that determines if we're 
 -- too close to the end, and subtracts the difference
 -- between how close we are and how close we want to be
 SELECT @offset = (c-o) - CASE 
   WHEN @NumWindowRows > (c - (c-o)) THEN
       (@NumWindowRows - (c - (c-o))) 
   ELSE 0 END
FROM x;

SELECT 
  ROW_NUMBER() OVER (ORDER BY CustomerID),
  CustomerID, 
  Name
FROM @Customers
ORDER BY CustomerID
OFFSET @offset ROWS
FETCH NEXT @NumWindowRows ROWS ONLY;

You could probably do this all inline instead of using the @offset variable, but it gets messy very fast, and I don't think it's possible to avoid the additional scan required to derive those counts anyway.

This is far simpler than your logic. But it assumes that you will always order by CustomerID and never add additional filters. If there are less than @NumWindowRows rows in the table, well...

  • Thanks, Aaron. That is some cool syntax on getting the initial counts into x. I knew that I should be able to eliminate all of the repeating select max() subqueries, but I was struggling with that concept. Yes, I will always order by CustomerID and not do any filtering at the server level. Having fewer rows that @NumWindowRows is a valid edge case. My original query handled that. I think I just need to ensure that offset is never less than zero in your solution. Thanks again for the help on this! – Gary Oct 29 '17 at 16:06

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.