Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / function + cte + filter / 12 сообщений из 12, страница 1 из 1
08.08.2018, 20:16
    #39685044
Hamber
Hamber
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
function + cte + filter
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
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;



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

Код: sql
1.
2.
3.
4.
5.
6.
SELECT
    LC.ContractId,
    OverdueActiveDays = LCOD.StartDate
FROM dbo.dogovors AS LC
	LEFT JOIN dbo.GetActiveUdf(@reportDate) AS LCOD
        ON LCOD.ContractId = LC.ContractId



По факту в плане запросов всегда идет scan всей таблицы, а только потом накладывается ограничение по договору
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.08.2018, 20:49
    #39685054
Владимир Затуливетер
Владимир Затуливетер
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
function + cte + filter
вот так больше шансов на фильтр по @ContractId

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.08.2018, 21:36
    #39685082
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
function + cte + filter
Hamber,

Функцию перепешите так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
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, как уже показали.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.08.2018, 11:06
    #39685308
Владимир Затуливетер
Владимир Затуливетер
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
function + cte + filter
invm,

+1, лучше и не сделаешь!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.08.2018, 16:50
    #39685673
ShIgor
ShIgor
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
function + cte + filter
invm,

в результате на каждую запись в договоре получаем вызов функции и 3 дополнительных select-а.
и при количестве договоров больше сотни тысяч сервер уходит в долгие раздумья
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.08.2018, 16:52
    #39685676
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
function + cte + filter
ShIgorinvm,

в результате на каждую запись в договоре получаем вызов функции и 3 дополнительных select-а.
и при количестве договоров больше сотни тысяч сервер уходит в долгие раздумьяЭто план показал???
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.08.2018, 17:01
    #39685680
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
function + cte + filter
ShIgorinvm,

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

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


вот 100000 x2 Seek-а на не самых быстрых дисках могут заставить его задуматься.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.08.2018, 18:43
    #39685711
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
function + cte + filter
ShIgorв результате на каждую запись в договоре получаем вызов функции и 3 дополнительных select-а.
и при количестве договоров больше сотни тысяч сервер уходит в долгие раздумья
 
Анализируйте, размышляйте и т.п...
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.08.2018, 01:00
    #39685798
ShIgor
ShIgor
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
function + cte + filter
invm,

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

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

 
видоизменим скрипт, оставив быструю часть оригинала и сравним еще раз
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
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, то ситуация усугубляется еще больше
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.08.2018, 10:36
    #39685907
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
function + cte + filter
ShIgorзадача, я так понял, решена не много не та что поставленаРазве?
ShIgorпредположим надо найти начальную дату промежутка в который попадает "левая" дата из другой таблы, а не фиксированная

главное, закомментарим создание индексов, т.к. обычно в таких таблах их и не бывает.
задача часто встречается на данных 1С при работе с периодическими значениями, например "цена на дату", решают почти так как в примере invm и это работает очень быстро, но здесь и сейчас. a при работе с историей, в DWH, этот подход начинает проигрыватьДавайте оставаться в рамках задачи ТС, для которой и предложено решение, а не фантазировать и выдумывать свои.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.08.2018, 12:33
    #39686010
ShIgor
ShIgor
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
function + cte + filter
invm,

ну так давайте...
Hamber ...а не явно указывать параметр в функции
для Вашей же функции уже необходимо 2 параметра
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.08.2018, 13:23
    #39686055
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
function + cte + filter
ShIgor,

Не путайте задачу саму задачу и способ ее решения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / function + cte + filter / 12 сообщений из 12, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]