Where clause filter view before dense rank

  • I have a view that has a dense rank function on a cte, sorta like this:

    drop view if exists drview;
    go
    create view drview
    AS
    with cte as (
    select *
    from (values ('1', '1', 'N'), ('1', '1', 'S'), ('1', '2', 'N' )) v(field1, field2, field3)
    )
    select *
    , dense_rank() over ( partition by field1 order by field2 ) field2_seq_no
    from cte;
    go

    select *
    from drview
    where field3 = 'S'

    Notice the filter on the table field3 happens after the dense_rank window.  If there are a lot of records before that filter is applied, the query is super slow.

    Screenshot 2023-01-03 165350

    Is there a way to have the query filter the table records before the dense_rank window?

    • This topic was modified 1 year, 10 months ago by  Jackie Lowery.
    • This topic was modified 1 year, 10 months ago by  Jackie Lowery.
    • This topic was modified 1 year, 10 months ago by  Jackie Lowery.
  • Works.  Not sure what the issue is

    drop view if exists drview;
    go
    create view drview
    AS
    with cte as (
    select *
    from (values ('x', 'x'), ('x', 'x'), ('x', 'x')) v(field1, field2)
    )
    select *
    , dense_rank() over ( partition by field1 order by field2 ) field3
    from cte;
    go

    select *
    from drview
    where field3 = 1;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Sorry, field 3 is a field in the table, it's not the dense rank value.  I've updated the original question to include your code with a small update.

    • This reply was modified 1 year, 10 months ago by  Jackie Lowery.
  • Still works

    select *
    from drview
    where field2 = 'x';

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • As an example, if I move the where clause to the query, the filter is applied before the dense_rank window.

    drop view if exists drview;
    go
    create view drview
    AS
    with cte as (
    select *
    from (values ('1', '1', 'N'), ('1', '1', 'S'), ('1', '2', 'N' )) v(field1, field2, field3)
    )
    select *
    , dense_rank() over ( partition by field1 order by field2 ) field2_seq_no
    from cte
    where field3 = 'S';
    go

    select *
    from drview

    Screenshot 2023-01-03 165350

    But I need to filter the query  by calling it with a where clause on the query itself b/c the filtered field could change.

    • This reply was modified 1 year, 10 months ago by  Jackie Lowery.
  • Except for the occasional Indexed View, this is one of the many reasons why I hate views.

    Instead, try a "parameterized inline view" better known as an "Inline Table Valued Function" (iTVF for short).

     CREATE FUNCTION dbo.drview
    (@field3 VARCHAR(10))
    RETURNS TABLE AS
    RETURN
    SELECT *
    ,DENSE_RANK() OVER ( PARTITION BY field1 ORDER BY field2 ) field2_seq_no
    FROM (VALUES ('1', '1', 'N'), ('1', '1', 'S'), ('1', '2', 'N' ))v(field1, field2, field3)
    WHERE (Field3 = @field3 OR @field3 IS NULL)
    ;
    GO
    SELECT *
    FROM dbo.drview ('S')
    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Having performance issues while filtering a view is not uncommon.   Is the view indexed?  How much latitude is there for making changes?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Jeff Moden wrote:

    Except for the occasional Indexed View, this is one of the many reasons why I hate views.

    Ha my exception is for VIEWS with INSTEAD OF triggers

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Steve Collins wrote:

    Jeff Moden wrote:

    Except for the occasional Indexed View, this is one of the many reasons why I hate views.

    Ha my exception is for VIEWS with INSTEAD OF triggers

    Cool and timely both.  Do you have a good example of an INSTEAD OF trigger for use with UPDATEs?  I've not seen a good one yet.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Do you have a good example of an INSTEAD OF trigger for use with UPDATEs?  I've not seen a good one yet.

    It hasn't been necessary for my work so far.  A little while ago on SSC I posted an example of an INSTEAD OF INSERT which was just a denormalization.  INSTEAD OF UPDATE could be necessary if a data client sent denormalized data for update but I would consider that a design problem.  Yes, I'd work with the client to, uh, confirm the necessity and it hasn't happened yet 🙂

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Jeff Moden wrote:

    Except for the occasional Indexed View, this is one of the many reasons why I hate views.

    Instead, try a "parameterized inline view" better known as an "Inline Table Valued Function" (iTVF for short).

     CREATE FUNCTION dbo.drview
    (@field3 VARCHAR(10))
    RETURNS TABLE AS
    RETURN
    SELECT *
    ,DENSE_RANK() OVER ( PARTITION BY field1 ORDER BY field2 ) field2_seq_no
    FROM (VALUES ('1', '1', 'N'), ('1', '1', 'S'), ('1', '2', 'N' ))v(field1, field2, field3)
    WHERE (Field3 = @field3 OR @field3 IS NULL)
    ;
    GO
    SELECT *
    FROM dbo.drview ('S')
    ;

    That tvf gives different results to the original query if you use different data:

    drop function if exists dbo.tvf_drview
    go
    CREATE FUNCTION dbo.tvf_drview
    (@field3 VARCHAR(10))
    RETURNS TABLE AS
    RETURN
    SELECT *
    ,DENSE_RANK() OVER ( PARTITION BY field1 ORDER BY field2 ) field2_seq_no
    from (values ('1', '1', 'N'),
    ('1', '1', 'S'),
    ('1', '2', 'S'),
    ('1', '2', 'S'),
    ('1', '3', 'N'),
    ('1', '4', 'S')) v(field1, field2, field3)
    WHERE (Field3 = @field3 OR @field3 IS NULL)
    ;
    GO
    SELECT *
    FROM dbo.tvf_drview ('S')
    ;
    go
    drop view if exists drview
    go
    create view drview
    AS
    with cte as (
    select *
    from (values ('1', '1', 'N'),
    ('1', '1', 'S'),
    ('1', '2', 'S'),
    ('1', '2', 'S'),
    ('1', '3', 'N'),
    ('1', '4', 'S')) v(field1, field2, field3)
    )
    select *
    , dense_rank() over ( partition by field1 order by field2 ) field2_seq_no
    from cte
    ;
    go
    select *
    from drview
    where field3 = 'S'

    I think you would need to write it like this:

    CREATE FUNCTION dbo.tvf_drview2
    (@field3 VARCHAR(10))
    RETURNS TABLE AS
    RETURN
    WITH CTE AS
    (
    SELECT *
    from (values ('1', '1', 'N'),
    ('1', '1', 'S'),
    ('1', '2', 'S'),
    ('1', '2', 'S'),
    ('1', '3', 'N'),
    ('1', '4', 'S')) v(field1, field2, field3)
    )
    SELECT * ,DENSE_RANK() OVER ( PARTITION BY field1 ORDER BY field2 ) field2_seq_no
    FROM CTE
    WHERE (Field3 = @field3 OR @field3 IS NULL)
    ;
    GO
    SELECT *
    FROM dbo.tvf_drview2 ('S')
  • The problem here is how SQL Server generates the execution plan for the query using the view.

    If SQL determines that filtering earlier in the plan is the better option then that is the plan that will be generated.  If later in the plan is better, that is what will be generated.

    If the column being filtered is indexed, that could change the plan to use an index scan or seek and key lookup...maybe.

    If the column isn't indexed, then filtering must be done later in the plan.

    I think your best option is going to be a stored procedure and dynamic SQL,

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply