+ Reply to Thread
Results 1 to 9 of 9

Total row count when paging

  1. Total row count when paging

    Hi. I'm using SQL 2005 to page results for a web application. There will
    be 10 rows per results page. I'm using a CTE and the row_number() function.
    On the web page, I want to also show page numbers that can be navigated to
    and probably the total count. To get the total row count, I see that the
    COUNT(*) OVER() clause in the CTE seems to work.

    WITH SearchResults AS (
    SELECT COUNT(*) OVER() AS 'TotalCount',
    ROW_NUMBER() OVER (ORDER BY Col1) AS 'RowNum',
    Col1, Col2
    FROM Items )
    SELECT TotalCount, RowNum, Col1, Col2
    FROM SearchResults
    WHERE RowNum BETWEEN 1 AND 10

    Even though COUNT(*) OVER() returns the correct number of rows, it seems
    slightly inefficient since all 10 returned rows will include an extra
    integer column for the same TotalCount value. Has anyone done something
    similar with the same or different method?

    Thanks in advance,
    Ben


  2. Re: Total row count when paging

    Ben Amada (ben.nojunkplease.amada@gmail.com) writes:
    > Hi. I'm using SQL 2005 to page results for a web application. There will
    > be 10 rows per results page.


    Why only 10? I hate such pages. Why can't I get at least 100?

    > I'm using a CTE and the row_number() function.
    > On the web page, I want to also show page numbers that can be navigated to
    > and probably the total count. To get the total row count, I see that the
    > COUNT(*) OVER() clause in the CTE seems to work.
    >
    > WITH SearchResults AS (
    > SELECT COUNT(*) OVER() AS 'TotalCount',
    > ROW_NUMBER() OVER (ORDER BY Col1) AS 'RowNum',
    > Col1, Col2
    > FROM Items )
    > SELECT TotalCount, RowNum, Col1, Col2
    > FROM SearchResults
    > WHERE RowNum BETWEEN 1 AND 10
    >
    > Even though COUNT(*) OVER() returns the correct number of rows, it seems
    > slightly inefficient since all 10 returned rows will include an extra
    > integer column for the same TotalCount value. Has anyone done something
    > similar with the same or different method?


    The alternative would be send two results, which a data adapter should
    be able to pick up.


    --
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Books Online for SQL Server 2005 at
    http://www.microsoft.com/technet/pro...ads/books.mspx
    Books Online for SQL Server 2000 at
    http://www.microsoft.com/sql/prodinf...ons/books.mspx

  3. Re: Total row count when paging

    "Erland Sommarskog" wrote:

    > Ben Amada (ben.nojunkplease.amada@gmail.com) writes:
    >> Hi. I'm using SQL 2005 to page results for a web application. There
    >> will
    >> be 10 rows per results page.

    >
    > Why only 10? I hate such pages. Why can't I get at least 100?


    LOL ... actually I'm with you. Most likely I'll be increasing the row count
    per page, but it will depend on the amount of data in the DB and the average
    row count for a typical search. I'm not sure what that is yet.

    >> I'm using a CTE and the row_number() function.
    >> On the web page, I want to also show page numbers that can be navigated
    >> to
    >> and probably the total count. To get the total row count, I see that the
    >> COUNT(*) OVER() clause in the CTE seems to work.
    >>
    >> WITH SearchResults AS (
    >> SELECT COUNT(*) OVER() AS 'TotalCount',
    >> ROW_NUMBER() OVER (ORDER BY Col1) AS 'RowNum',
    >> Col1, Col2
    >> FROM Items )
    >> SELECT TotalCount, RowNum, Col1, Col2
    >> FROM SearchResults
    >> WHERE RowNum BETWEEN 1 AND 10
    >>
    >> Even though COUNT(*) OVER() returns the correct number of rows, it seems
    >> slightly inefficient since all 10 returned rows will include an extra
    >> integer column for the same TotalCount value. Has anyone done something
    >> similar with the same or different method?

    >
    > The alternative would be send two results, which a data adapter should
    > be able to pick up.


    That makes sense. The only thing is (and my example above didn't show
    this), is that I will have a handful of WHERE conditions on the SELECT
    statement in the CTE. So there might be 1000 rows in the Items table, and
    based on 4 conditions a user specifies, only 100 rows will be eligible to be
    on the results page. The CTE will select those 100 rows. But since I can
    only run one statement on the CTE, I'm not sure how easy it would be to
    return a separate result set for the TotalCount of 100. I know this would
    not be a big deal to do with a temp table or table variable, but just
    thought I'd give CTEs a shot. For the record, I'm constructing my SQL
    statement via a ad-hoc parameterized query.

    Thanks for the reply :-)
    Ben


  4. Re: Total row count when paging

    Ben Amada (ben.nojunkplease.amada@gmail.com) writes:
    > That makes sense. The only thing is (and my example above didn't show
    > this), is that I will have a handful of WHERE conditions on the SELECT
    > statement in the CTE. So there might be 1000 rows in the Items table,
    > and based on 4 conditions a user specifies, only 100 rows will be
    > eligible to be on the results page. The CTE will select those 100 rows.
    > But since I can only run one statement on the CTE, I'm not sure how
    > easy it would be to return a separate result set for the TotalCount of
    > 100. I know this would not be a big deal to do with a temp table or
    > table variable, but just thought I'd give CTEs a shot. For the record,
    > I'm constructing my SQL statement via a ad-hoc parameterized query.


    Producing a single result set as you do now, is probably the best option.

    Another alternative is to bounce the data over a temp table, but I think
    that costs more than what you win on the reduced network traffic.

    This looks palatable:

    WITH CTE () AS (...)
    SELECT RowNum = -TotalCount, NULL, NULL
    FROM CTE
    UNION ALL
    SELECT RowNum, Col1, Col2
    FROM CTE
    ORDER BY RowNum

    The application would have to know that the first row is the total
    number of rows, and that it's negative. It looks nice, but a CTE
    is just a macro, so this would run the query twice. And is thus
    likely to be even more expensive than the temp table.

    --
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Books Online for SQL Server 2005 at
    http://www.microsoft.com/technet/pro...ads/books.mspx
    Books Online for SQL Server 2000 at
    http://www.microsoft.com/sql/prodinf...ons/books.mspx

  5. Re: Total row count when paging

    Erland Sommarskog wrote:

    > Producing a single result set as you do now, is probably the best option.


    Yes, that's what I've begun to think.

    > This looks palatable:
    >
    > WITH CTE () AS (...)
    > SELECT RowNum = -TotalCount, NULL, NULL
    > FROM CTE
    > UNION ALL
    > SELECT RowNum, Col1, Col2
    > FROM CTE
    > ORDER BY RowNum
    >
    > The application would have to know that the first row is the total
    > number of rows, and that it's negative. It looks nice, but a CTE
    > is just a macro, so this would run the query twice. And is thus
    > likely to be even more expensive than the temp table.


    That's a nice idea. If I was returning more rows (over 100 or so), then the
    UNION ALL approach would probably save a good chunk of network bandwidth
    since I would be eliminating the 4-byte TotalCount int column from all those
    rows in the result set.

    If you don't mind, two quick questions just sprung to mind. Is my usage of
    COUNT(*) OVER() expression in the CTE valid and supported? The examples
    I've seen (and I haven't seen many), usually include a PARTITION BY argument
    inside of OVER(). In my case, an empty OVER() clause gives me exactly what
    I need. The 2nd question is whether a CTE is still considered a macro when
    the statement in the CTE is just a SELECT statement (as I'm doing) without a
    UNION ALL? I could easily rewrite my query to just use a derived table
    instead of a CTE. I just felt like giving CTEs a try. I wouldn't want to
    sacrifice any performance if the CTE doesn't perform as well. I'm going to
    compare execution plans for the CTE versus the derived table approach
    tomorrow when I get to work.

    Thanks again,
    Ben



  6. Re: Total row count when paging

    Ben Amada (ben.nojunkplease.amada@gmail.com) writes:
    > That's a nice idea. If I was returning more rows (over 100 or so), then
    > the UNION ALL approach would probably save a good chunk of network
    > bandwidth since I would be eliminating the 4-byte TotalCount int column
    > from all those rows in the result set.


    But as I said, you would probably lose more on the roundabouts than you
    gain on the swings.

    > If you don't mind, two quick questions just sprung to mind. Is my usage
    > of COUNT(*) OVER() expression in the CTE valid and supported?


    Yes, that's a fair game. Check out the syntax graph for OVER() in Books
    Online.

    > The 2nd question is whether a CTE is still considered a macro when the
    > statement in the CTE is just a SELECT statement (as I'm doing) without a
    > UNION ALL? I could easily rewrite my query to just use a derived table
    > instead of a CTE. I just felt like giving CTEs a try. I wouldn't want
    > to sacrifice any performance if the CTE doesn't perform as well. I'm
    > going to compare execution plans for the CTE versus the derived table
    > approach tomorrow when I get to work.


    "Considered a macro" is maybe not the right way of putting it. The question
    is more "how will the optimizer handle it?". And the optimizer handles a
    CTE as it handles derived tables, views and inline functions: it looks as
    the expanded query. In fact, I believe that the expansion happens before
    the optimizer sees it.

    Today, SQL Server does not play any neat tricks with CTEs that I know
    of (save for recursive CTEs), but tomorrow it could be different. But it's
    deceivable, because it looks nice to the human eye.

    When in doubt, always check the query plan.


    --
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Books Online for SQL Server 2005 at
    http://www.microsoft.com/technet/pro...ads/books.mspx
    Books Online for SQL Server 2000 at
    http://www.microsoft.com/sql/prodinf...ons/books.mspx

  7. Re: Total row count when paging

    "Erland Sommarskog" wrote:

    >> If you don't mind, two quick questions just sprung to mind. Is my usage
    >> of COUNT(*) OVER() expression in the CTE valid and supported?

    >
    > Yes, that's a fair game. Check out the syntax graph for OVER() in Books
    > Online.


    Okay, thanks for confirming.

    >> The 2nd question is whether a CTE is still considered a macro when the
    >> statement in the CTE is just a SELECT statement (as I'm doing) without a
    >> UNION ALL? I could easily rewrite my query to just use a derived table
    >> instead of a CTE. I just felt like giving CTEs a try. I wouldn't want
    >> to sacrifice any performance if the CTE doesn't perform as well. I'm
    >> going to compare execution plans for the CTE versus the derived table
    >> approach tomorrow when I get to work.

    >
    > "Considered a macro" is maybe not the right way of putting it. The
    > question
    > is more "how will the optimizer handle it?". And the optimizer handles a
    > CTE as it handles derived tables, views and inline functions: it looks as
    > the expanded query. In fact, I believe that the expansion happens before
    > the optimizer sees it.
    >
    > Today, SQL Server does not play any neat tricks with CTEs that I know
    > of (save for recursive CTEs), but tomorrow it could be different. But it's
    > deceivable, because it looks nice to the human eye.


    I see. Probably a CTE is most like a macro when it is a recursive CTE.

    > When in doubt, always check the query plan.


    For my queries, I'm getting what looks like the same execution plan for the
    CTE and derived table. That's good to see.

    I appreciate your time!
    Ben


  8. Re: Total row count when paging

    Ben Amada (ben.nojunkplease.amada@gmail.com) writes:
    > For my queries, I'm getting what looks like the same execution plan for
    > the CTE and derived table. That's good to see.


    Yes, a non-recursive CTE and a derived table is very much the same thing.
    The difference is you can refer to the CTE by its name, so you can using
    it in several places in the query without repeating the SELECT statement
    its made up of.


    --
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Books Online for SQL Server 2005 at
    http://www.microsoft.com/technet/pro...ads/books.mspx
    Books Online for SQL Server 2000 at
    http://www.microsoft.com/sql/prodinf...ons/books.mspx

  9. Re: Total row count when paging

    I know you can use multiple CTE's, shown below. It seemed to have the
    best execution plan compared to a handful of other stategies i've tried.
    Previously I was running the same query twice...once for the count, and
    once with cte for the pagination, which was obviously costly, but not as
    bad as it was when we were selecting everything and eliminating records
    in a temp table. This post is the first I've heard of "Count(*) Over",
    but if it works I like the sound of it!

    Declare @Page int, @PageSize int, @RowStart int, @RowEnd int;
    Select @Page = 1, @PageSize = 100;

    Select @RowStart = ((@Page * @PageSize)-PageSize)+1;
    Select @RowEnd = @Page * @PageSize;

    With CTE1 As(
    Select Ceiling(ROW_NUMBER() OVER (ORDER BY a.Column1 ASC)) as
    RowNumber,
    a.ID, a.Column1
    From SomeTable a
    Where a.SomeCondition = bla
    ),
    CTE2 As(
    Select Count(*) MaxCount From CTE1
    --now I dont have to duplicate the where criteria
    )
    Select CTE1.ID, CTE1.Column1, CTE2.MaxCount
    From CTE, CTE2
    Where CTE1.RowNumber Between @RowStart and @RowEnd
    Order By CTE1.Column1;

    *** Sent via Developersdex http://www.developersdex.com ***

+ Reply to Thread