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.
Is there a way to have the query filter the table records before the dense_rank window?
- This topic was modified 1 year, 11 months ago by Jackie Lowery.
- This topic was modified 1 year, 11 months ago by Jackie Lowery.
- This topic was modified 1 year, 11 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.
-
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 drviewBut I need to filter the query by calling it with a where clause on the query itself b/c the filtered field could change.
-
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