none
count(*) and select in the same WITH statement

    Question

  • Hi,

     

    I have a query:

     

    -- main select

    WITH Orders AS
    (
    SELECT
     ROW_Number() OVER(MyDate ASC) RowNo,
    ** rest o the query ***
    )
    SELECT *
    FROM Orders
    WHERE RowNo BETWEEN 100 AND 200

    ORDER BY RowNo

    --count of records

    DECLARE @COUNT INT
    SELECT @COUNT = COUNT(*)
    FROM ** the same query as above ***

    RETURN @COUNT


     

    In this case it can happen that when counting records there will be different number of records that it was at time of paging. Also server has to execute this query twice and the query is quite complicated means that takes time.

     

    Is there any better way to get number of rows in the same part of query with paging ?

     

    Thanks for help

    Przemo

     

    Tuesday, April 22, 2008 1:05 PM

Answers

  • If you need to grab the rows affected by the query, then you can use @@ROWCOUNT. If you want the number of rows conforming the CTE, then you can use:

     

    WITH Orders AS
    (
    SELECT

    ROW_Number() OVER(order by MyDate ASC) RowNo,

    count(*) over() as cnt

    ** rest o the query ***

    )
    SELECT *
    FROM Orders
    WHERE RowNo BETWEEN 100 AND 200

    ORDER BY RowNo;

     

     

    AMB

    Tuesday, April 22, 2008 1:10 PM
    Moderator
  • Have you tried to simplify it to not contain the CTE for the count(*) portion?  For Example:

     

     

    Code Snippet

    WITH Orders AS
    (
    SELECT
     ROW_Number() OVER(MyDate ASC) RowNo,
     tab1.col1, tab1.col2, tab2.col4, tab2.col6

    FROM tab1

    JOIN tab2 ON tab1.ID = tab2.tab1ID

    WHERE tab1.col1 = 'SomeValue'

    )
    SELECT *
    FROM Orders
    WHERE RowNo BETWEEN 100 AND 200

    ORDER BY RowNo

     

    --count of records

    DECLARE @COUNT INT

    SELECT @COUNT = COUNT(*)
    FROM tab1

    JOIN tab2 ON tab1.ID = tab2.tab1ID

    WHERE tab1.col1 = 'SomeValue'

     

    RETURN @COUNT

     

     

    This keeps the database engine from having to do the ordering of the data, and just does a basic row count of the 2 tables joined on the join criteria and the filter criteria?

     

    See if that helps out any?

     

    Tuesday, April 22, 2008 9:33 PM
    Moderator

All replies

  • If you need to grab the rows affected by the query, then you can use @@ROWCOUNT. If you want the number of rows conforming the CTE, then you can use:

     

    WITH Orders AS
    (
    SELECT

    ROW_Number() OVER(order by MyDate ASC) RowNo,

    count(*) over() as cnt

    ** rest o the query ***

    )
    SELECT *
    FROM Orders
    WHERE RowNo BETWEEN 100 AND 200

    ORDER BY RowNo;

     

     

    AMB

    Tuesday, April 22, 2008 1:10 PM
    Moderator
  • If you are pulling the data back in to a datatable or dataset in the application why not use the Rows.Count of the datatable in the application tier and save the second operation in SQL?

     

    Tuesday, April 22, 2008 1:10 PM
    Moderator
  • Maybe I misunderstood but shouldn't this give you what you want?

     

    SELECT @COUNT = @@rowcount


     

    Denis The SQL Menace

    http://sqlservercode.blogspot.com

    http://sqlblog.com/blogs/denis_gobo/default.aspx

    Tuesday, April 22, 2008 1:11 PM
    Moderator
  •  SQL_Menace wrote:

    Maybe I misunderstood but shouldn't this give you what you want?

     

    SELECT @COUNT = @@rowcount


     

    Denis The SQL Menace

    http://sqlservercode.blogspot.com

    http://sqlblog.com/blogs/denis_gobo/default.aspx

    I agree with SQL_Menace and the other posters who mention @@rowcount.  This should be exactly what you need to get the number of rows selected.

    Tuesday, April 22, 2008 2:01 PM
    Moderator
  • I think hunchback nailed this one.  The solution that I provided and that Denis provided will only provide counts for the actual selected rows back, while hunchbacks solution will count the entire CTE Rowcount.

     

    Tuesday, April 22, 2008 2:04 PM
    Moderator
  • Hunchback,

     

    This is what I was looking for. To know how many record were in total from which I have selected 100.

     

    But as I see the performance is about 20 times worse than in my solution with doubled code.

    My query before paging returns about 80.000 records and have 7 joins.

     

    Any hint how to improve it? I like it, cause there isno need to copy same code twice.

     

    Thanks,

    Przemek

     

    Tuesday, April 22, 2008 9:25 PM
  • Have you tried to simplify it to not contain the CTE for the count(*) portion?  For Example:

     

     

    Code Snippet

    WITH Orders AS
    (
    SELECT
     ROW_Number() OVER(MyDate ASC) RowNo,
     tab1.col1, tab1.col2, tab2.col4, tab2.col6

    FROM tab1

    JOIN tab2 ON tab1.ID = tab2.tab1ID

    WHERE tab1.col1 = 'SomeValue'

    )
    SELECT *
    FROM Orders
    WHERE RowNo BETWEEN 100 AND 200

    ORDER BY RowNo

     

    --count of records

    DECLARE @COUNT INT

    SELECT @COUNT = COUNT(*)
    FROM tab1

    JOIN tab2 ON tab1.ID = tab2.tab1ID

    WHERE tab1.col1 = 'SomeValue'

     

    RETURN @COUNT

     

     

    This keeps the database engine from having to do the ordering of the data, and just does a basic row count of the 2 tables joined on the join criteria and the filter criteria?

     

    See if that helps out any?

     

    Tuesday, April 22, 2008 9:33 PM
    Moderator
  • Yes, this is what I had in the beginnig. But just wanted to know if there is anythiong simpler (no query code duplication) but also not slower.

     

    Looks, like the first version, althought duplicates code is the best one.

     

     

    Przemek

     

    Tuesday, April 22, 2008 9:42 PM
  • Really hard to suggest something without knowing table structure, including indexes and constraints, and also the query.

     

     

    AMB

    Tuesday, April 22, 2008 11:06 PM
    Moderator
  • I played around with this and came to the same conclusion as the OP.  Namely that the duplicated code is the most efficient, performance wise, when it comes to selecting a count and a resultset.

    Here are my results (estimated subtree cost) from a table that I created:

    No count: .0146455
    Inline count: .0260349
    Duplicate Select: .0146455 + .0032842 = .0179297
    Table variable: .0246457 + .0032831 + .0032842 = .031212

    My dataset is as follows:

    Code Snippet

    DECLARE @Step TABLE (
        Id int,
        RouteId int,
        Title varchar(100),
        Sequence int
    )

    --// Route 1 Steps
    INSERT INTO @Step VALUES (1, 1, 'Step 1.1', 1)
    INSERT INTO @Step VALUES (2, 1, 'Step 1.2', 2)
    INSERT INTO @Step VALUES (3, 1, 'Step 1.3', 3)
    INSERT INTO @Step VALUES (4, 1, 'Step 1.4', 4)
    INSERT INTO @Step VALUES (5, 1, 'Step 1.5', 5)

    --// Route 2 Steps
    INSERT INTO @Step VALUES (6, 2, 'Step 2.1', 1)
    INSERT INTO @Step VALUES (7, 2, 'Step 2.2', 2)
    INSERT INTO @Step VALUES (8, 2, 'Step 2.3', 3)

    --// Route 3 Steps
    INSERT INTO @Step VALUES (9, 3, 'Step 3.1', 1)

    --// Route 4 Steps
    INSERT INTO @Step VALUES (10, 4, 'Step 4.1', 1)

    --// Route 5 Steps
    INSERT INTO @Step VALUES (11, 5, 'Step 5.1', 1)
    INSERT INTO @Step VALUES (12, 5, 'Step 5.2', 2)
    INSERT INTO @Step VALUES (13, 5, 'Step 5.3', 3)
    INSERT INTO @Step VALUES (14, 5, 'Step 5.4', 4)


    So it seems that while duplicating the SQL is not a very elegant solution, it is the most performant (at least for this small dataset).  With a complex JOIN, I could see the case for a temporary table or for the inline count on the CTE.  The inline count also doesn't work very well for OUTPUT parameters, unfortunately.

    It seems like this should have been something that Microsoft would have thought about (you know, ROW_NUMBER() being most useful for paging after all); I find it really odd that there is no simpler way of expressing the row count from the CTE SELECT given that the main usage scenario requires that bit of information.
    Monday, May 05, 2008 11:47 PM