Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 function + cte + filter  [new]
Hamber
Member

Откуда: Москва
Сообщений: 310
CREATE FUNCTION dbo.GetActiveUdf
(
    @date DATE -- Дата, на которую получаем информацию
)
RETURNS TABLE
RETURN WITH HistoryAll (ContractId, StartDate)
       AS (SELECT A.ContractId,
                  A.StartDate
           FROM dbo.table1 AS A WITH (NOLOCK)
           WHERE A.StartDate <= @date
           UNION ALL
           SELECT A.ContractId,
                  A.StartDate
           FROM dbo.table2 AS A WITH (NOLOCK)
           WHERE A.StartDate <= @date),
            HistoryActive
       AS (SELECT HA.ContractId,
                  HA.StartDate,
                  _rn = ROW_NUMBER() OVER (PARTITION BY HA.ContractId ORDER BY HA.StartDate DESC)
           FROM HistoryAll AS HA)
SELECT H.ContractId,
       H.StartDate
FROM HistoryActive AS H
WHERE H._rn = 1;


Возможно ли сделать так, чтобы подобная функция умела накладывать фильтр массово в разрезе договора, а не явно указывать параметр в функции?

SELECT
    LC.ContractId,
    OverdueActiveDays = LCOD.StartDate
FROM dbo.dogovors AS LC
	LEFT JOIN dbo.GetActiveUdf(@reportDate) AS LCOD
        ON LCOD.ContractId = LC.ContractId


По факту в плане запросов всегда идет scan всей таблицы, а только потом накладывается ограничение по договору
8 авг 18, 20:16    [21634939]     Ответить | Цитировать Сообщить модератору
 Re: function + cte + filter  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
вот так больше шансов на фильтр по @ContractId

CREATE FUNCTION dbo.GetActiveUdf
(
    @date DATE -- Дата, на которую получаем информацию
  , @ContractId int
)
RETURNS TABLE
RETURN WITH HistoryAll (ContractId, StartDate)
       AS (SELECT A.StartDate
           FROM dbo.table1 AS A WITH (NOLOCK)
           WHERE A.StartDate <= @date and a.ContractId = @ContractId
           UNION ALL
           SELECT A.StartDate
           FROM dbo.table2 AS A WITH (NOLOCK)
           WHERE A.StartDate <= @date and a.ContractId = @ContractId
		 ),
            HistoryActive
       AS (SELECT HA.StartDate,
                  _rn = ROW_NUMBER() OVER (ORDER BY HA.StartDate DESC)
           FROM HistoryAll AS HA)
SELECT H.StartDate
FROM HistoryActive AS H
WHERE H._rn = 1;
go

SELECT
    LC.ContractId,
    OverdueActiveDays = LCOD.StartDate
FROM dbo.dogovors AS LC
	outer apply dbo.GetActiveUdf(@reportDate, LC.ContractId) AS LCOD
8 авг 18, 20:49    [21634968]     Ответить | Цитировать Сообщить модератору
 Re: function + cte + filter  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Hamber,

Функцию перепешите так:
create function dbo.GetActiveUdf
(
 @date date, -- Дата, на которую получаем информацию
 @ContractId int
)
returns table
as
return
 with a as
 (
  select top (1) StartDate from dbo.table1 where ContractId = @ContractId and StartDate <= @date order by StartDate desc
  union all
  select top (1) StartDate from dbo.table2 where ContractId = @ContractId and StartDate <= @date order by StartDate desc
 )
 select max(StartDate) as StartDate from a;
Плюс индексы по (ContractId, StartDate) на table1 и table2.

Запрос перепешите через outer apply, как уже показали.
8 авг 18, 21:36    [21635023]     Ответить | Цитировать Сообщить модератору
 Re: function + cte + filter  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
invm,

+1, лучше и не сделаешь!
9 авг 18, 11:06    [21635584]     Ответить | Цитировать Сообщить модератору
 Re: function + cte + filter  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2344
invm,

в результате на каждую запись в договоре получаем вызов функции и 3 дополнительных select-а.
и при количестве договоров больше сотни тысяч сервер уходит в долгие раздумья
9 авг 18, 16:50    [21636468]     Ответить | Цитировать Сообщить модератору
 Re: function + cte + filter  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31364
ShIgor
invm,

в результате на каждую запись в договоре получаем вызов функции и 3 дополнительных select-а.
и при количестве договоров больше сотни тысяч сервер уходит в долгие раздумья
Это план показал???
9 авг 18, 16:52    [21636477]     Ответить | Цитировать Сообщить модератору
 Re: function + cte + filter  [new]
msLex
Member

Откуда:
Сообщений: 8091
ShIgor
invm,

в результате на каждую запись в договоре получаем вызов функции и 3 дополнительных select-а.
и при количестве договоров больше сотни тысяч сервер уходит в долгие раздумья


Вызов функции и селекты как таковые на сотнях тысячах и даже миллионах записей не должны уводить нормальный сервер в долгие раздумья


вот 100000 x2 Seek-а на не самых быстрых дисках могут заставить его задуматься.
9 авг 18, 17:01    [21636487]     Ответить | Цитировать Сообщить модератору
 Re: function + cte + filter  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
ShIgor
в результате на каждую запись в договоре получаем вызов функции и 3 дополнительных select-а.
и при количестве договоров больше сотни тысяч сервер уходит в долгие раздумья
+ Анализируйте, размышляйте и т.п...
use tempdb;
go

create table dbo.c (contract_id int primary key);
create table dbo.t (contract_id int primary key);
create table dbo.ca1 (id int identity primary key, contract_id int, StartDate date);
create table dbo.ca2 (id int identity primary key, contract_id int, StartDate date);
go

create function dbo.fn_ca__1
(
 @d date
)
returns table
as
return (
 with a as
 (
  select contract_id, StartDate, row_number() over (partition by contract_id order by StartDate desc) as rn from dbo.ca1 where StartDate <= @d
  union all
  select contract_id, StartDate, row_number() over (partition by contract_id order by StartDate desc) as rn from dbo.ca2 where StartDate <= @d
 )
 select contract_id, max(StartDate) as StartDate from a where rn = 1 group by contract_id
);
go

create function dbo.fn_ca__2
(
 @contract_id int,
 @d date
)
returns table
as
return (
 with a as
 (
  select top (1) StartDate from dbo.ca1 where contract_id = @contract_id and StartDate <= @d order by StartDate desc
  union all
  select top (1) StartDate from dbo.ca2 where contract_id = @contract_id and StartDate <= @d order by StartDate desc
 )
 select max(StartDate) as StartDate from a
);
go

declare @c int = 100000, @a int = 10;

insert into dbo.c
 (contract_id)
 select top (@c)
  row_number() over (order by (select 1))
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;

insert into dbo.ca1
 (contract_id, StartDate)
 select
  c.contract_id, ca.StartDate
 from
  dbo.c c cross apply
  (
   select top (cast(rand(checksum(newid(), c.contract_id)) * @a + @a as bigint))
    dateadd(day, rand(checksum(newid())) * datediff(day, '2000', getdate()), '2000')
   from
    master.dbo.spt_values
  ) ca(StartDate);

insert into dbo.ca2
 (contract_id, StartDate)
 select
  c.contract_id, ca.StartDate
 from
  dbo.c c cross apply
  (
   select top (cast(rand(checksum(newid(), c.contract_id)) * @a + @a as bigint))
    dateadd(day, rand(checksum(newid())) * datediff(day, '2000', getdate()), '2000')
   from
    master.dbo.spt_values
  ) ca(StartDate);

insert into dbo.t
 (contract_id)
 select
  contract_id
 from
  dbo.c tablesample (20 percent);

--create index IX_ca1__contract_id__StartDate on dbo.ca1 (contract_id, StartDate desc);
--create index IX_ca2__contract_id__StartDate on dbo.ca2 (contract_id, StartDate desc);
create index IX_ca1__contract_id__StartDate on dbo.ca1 (contract_id, StartDate);
create index IX_ca2__contract_id__StartDate on dbo.ca2 (contract_id, StartDate);
go

declare @contract_id int, @StratDate date, @d date = getdate();

set statistics xml on;
set statistics time, io on;

select
 @contract_id = t.contract_id, @StratDate = d.StartDate
from
 dbo.t t left join
 dbo.fn_ca__1(@d) d on d.contract_id = t.contract_id
option
 (maxdop 1);

select
 @contract_id = t.contract_id, @StratDate = d.StartDate
from
 dbo.t t outer apply
 dbo.fn_ca__2(t.contract_id, @d) d
option
 (maxdop 1);

set statistics time, io off;
set statistics xml off;
go

drop function dbo.fn_ca__1, dbo.fn_ca__2;
drop table dbo.t, dbo.c, dbo.ca1, dbo.ca2;
go

ЗЫ: Особо рекомедую поразмышлять на разницей при индексах со StartDate asc и desc
9 авг 18, 18:43    [21636584]     Ответить | Цитировать Сообщить модератору
 Re: function + cte + filter  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2344
invm,

ага..
задача, я так понял, решена не много не та что поставлена, ну да ладно.. не суть.
предположим надо найти начальную дату промежутка в который попадает "левая" дата из другой таблы, а не фиксированная

главное, закомментарим создание индексов, т.к. обычно в таких таблах их и не бывает.
задача часто встречается на данных 1С при работе с периодическими значениями, например "цена на дату", решают почти так как в примере invm и это работает очень быстро, но здесь и сейчас. a при работе с историей, в DWH, этот подход начинает проигрывать

+ видоизменим скрипт, оставив быструю часть оригинала и сравним еще раз
use tempdb;
go

create table dbo.c (contract_id int primary key);
create table dbo.t (contract_id int primary key, CheckDate date);
create table dbo.ca1 (id int identity primary key, contract_id int, StartDate date);
create table dbo.ca2 (id int identity primary key, contract_id int, StartDate date);
go

create function dbo.fn_ca__2
(
 @contract_id int,
 @d date
)
returns table
as
return (
 with a as
 (
  select top (1) StartDate from dbo.ca1 where contract_id = @contract_id and StartDate <= @d order by StartDate desc
  union all
  select top (1) StartDate from dbo.ca2 where contract_id = @contract_id and StartDate <= @d order by StartDate desc
 )
 select max(StartDate) as StartDate from a
);
go

declare @c int = 100000, @a int = 10;

insert into dbo.c
 (contract_id)
 select top (@c)
  row_number() over (order by (select 1))
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;

insert into dbo.ca1
 (contract_id, StartDate)
 select
  c.contract_id, ca.StartDate
 from
  dbo.c c cross apply
  (
   select top (cast(rand(checksum(newid(), c.contract_id)) * @a + @a as bigint))
    dateadd(day, rand(checksum(newid())) * datediff(day, '2000', getdate()), '2000')
   from
    master.dbo.spt_values
  ) ca(StartDate);

insert into dbo.ca2
 (contract_id, StartDate)
 select
  c.contract_id, ca.StartDate
 from
  dbo.c c cross apply
  (
   select top (cast(rand(checksum(newid(), c.contract_id)) * @a + @a as bigint))
    dateadd(day, rand(checksum(newid())) * datediff(day, '2000', getdate()), '2000')
   from
    master.dbo.spt_values
  ) ca(StartDate);

insert into dbo.t
 (contract_id, CheckDate)
 select
  contract_id, dateadd(day, rand(checksum(newid())) * datediff(day, '2000', getdate()), '2000')
 from
  dbo.c tablesample (20 percent);

--create index IX_ca1__contract_id__StartDate on dbo.ca1 (contract_id, StartDate desc);
--create index IX_ca2__contract_id__StartDate on dbo.ca2 (contract_id, StartDate desc);
--create index IX_ca1__contract_id__StartDate on dbo.ca1 (contract_id, StartDate);
--create index IX_ca2__contract_id__StartDate on dbo.ca2 (contract_id, StartDate);
go

declare @contract_id int, @StratDate date;

set statistics xml on;
set statistics time, io on;

 with a as
 (
  select contract_id, StartDate, max(StartDate) over (partition by contract_id order by StartDate rows between 1 following and 1 following ) EndDate
  from ( select contract_id, StartDate from dbo.ca1 
         union all
         select contract_id, StartDate from dbo.ca2 
	   ) aa
 ) 
 select 
   @contract_id = t.contract_id, @StratDate = a.StartDate 
 from 
   dbo.t t left join a on a.contract_id = t.contract_id and t.CheckDate >= a.StartDate and t.CheckDate < a.EndDate 
 option
 (maxdop 1);

 select
 @contract_id = t.contract_id,  @StratDate = d.StartDate
from
 dbo.t t outer apply
 dbo.fn_ca__2(t.contract_id, t.CheckDate) d
option
 (maxdop 1);

set statistics time, io off;
set statistics xml off;
go

drop function dbo.fn_ca__2;
drop table dbo.t, dbo.c, dbo.ca1, dbo.ca2;
go 


а если убрать maxdop, то ситуация усугубляется еще больше
10 авг 18, 01:00    [21637032]     Ответить | Цитировать Сообщить модератору
 Re: function + cte + filter  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
ShIgor
задача, я так понял, решена не много не та что поставлена
Разве?
ShIgor
предположим надо найти начальную дату промежутка в который попадает "левая" дата из другой таблы, а не фиксированная

главное, закомментарим создание индексов, т.к. обычно в таких таблах их и не бывает.
задача часто встречается на данных 1С при работе с периодическими значениями, например "цена на дату", решают почти так как в примере invm и это работает очень быстро, но здесь и сейчас. a при работе с историей, в DWH, этот подход начинает проигрывать
Давайте оставаться в рамках задачи ТС, для которой и предложено решение, а не фантазировать и выдумывать свои.
10 авг 18, 10:36    [21637322]     Ответить | Цитировать Сообщить модератору
 Re: function + cte + filter  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2344
invm,

ну так давайте...
Hamber
...а не явно указывать параметр в функции

для Вашей же функции уже необходимо 2 параметра
10 авг 18, 12:33    [21637594]     Ответить | Цитировать Сообщить модератору
 Re: function + cte + filter  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
ShIgor,

Не путайте задачу саму задачу и способ ее решения.
10 авг 18, 13:23    [21637725]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить