Есть запрос
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
select --PlanAnalizeMarker
objset.rs_rgobj_id
, regobj.rgob_name
, (
select
sum(dbo.IntersectPeriodsLength(:BeginDayTech, getdate(), sts.rs_bperiod, sts.rs_eperiod))
from
mre_regobj_states sts
where
sts.rs_regobj_id = regobj.rgob_id
and sts.rs_state_id = :EmergencyStateId
and sts.rs_bperiod <= getdate()
and (sts.rs_eperiod > :BeginDayTech or sts.rs_eperiod is null)
) EmergencyDay
from
mre_regobj_setobj objset
left join mre_regobjects_lst regobj on regobj.rgob_id = objset.rs_rgobj_id
where
objset.rs_setobj_id = :rs_setobj_id
Выполняться он может двумя способами (планы прилагаю). Зависит это от опции FetchOptions.CursorKind: ckAutomatic = GoodShort, ckDynamic = BadShort (Delphi компонент доступа к БД FireDAC TFDQuery). Вопрос что за дикий план BadShort, где почитать про CWT_PrimaryKey, и каким хинтом прибить план к хорошему варианту?
mre_regobj_setobj Наборы объектов, 2 записи
rs_idrs_rgobj_idrs_setobj_id31433102433
mre_regobjects_lst Каталог объектов, 2 записи
rgob_idrgob_typergob_name10Объект 120Объект 2
mre_regobj_states Состояние объектов, 2 записи (с 2021-03-29 01:00:00.000 по 2021-03-30 06:00:00.000 в ремонте, после в работе по текущий момент)
rs_idrs_regobj_idrs_bperiodrs_eperiodrs_state_id112021-03-29 01:00:00.0002021-03-30 06:00:00.000435212021-03-30 06:00:00.000NULL437
Скалярная функция IntersectPeriodsLength просто считает пересечения интервалов, к таблицам не обращается.
ddl
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.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[mre_regobj_setobj](
[rs_id] [bigint] IDENTITY(1,1) NOT NULL,
[rs_rgobj_id] [int] NOT NULL,
[rs_setobj_id] [int] NOT NULL,
[jrn_create_date] [datetime] NOT NULL,
[jrn_create_user_id] [int] NOT NULL,
[jrn_modif_date] [datetime] NOT NULL,
[jrn_modif_user_id] [int] NOT NULL,
CONSTRAINT [PK_mre_regobj_setobj] PRIMARY KEY CLUSTERED
(
[rs_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[mre_regobj_setobj] WITH CHECK ADD CONSTRAINT [FK_mre_regobj_setobj_regobj] FOREIGN KEY([rs_rgobj_id])
REFERENCES [dbo].[mre_regobjects_lst] ([rgob_id])
GO
ALTER TABLE [dbo].[mre_regobj_setobj] CHECK CONSTRAINT [FK_mre_regobj_setobj_regobj]
GO
ALTER TABLE [dbo].[mre_regobj_setobj] WITH CHECK ADD CONSTRAINT [FK_mre_regobj_setobj_setobj] FOREIGN KEY([rs_setobj_id])
REFERENCES [dbo].[com_analitics_sp] ([ansp_id])
GO
ALTER TABLE [dbo].[mre_regobj_setobj] CHECK CONSTRAINT [FK_mre_regobj_setobj_setobj]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ИД' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mre_regobj_setobj', @level2type=N'COLUMN',@level2name=N'rs_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Объект' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mre_regobj_setobj', @level2type=N'COLUMN',@level2name=N'rs_rgobj_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Набор объектов' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mre_regobj_setobj', @level2type=N'COLUMN',@level2name=N'rs_setobj_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Вхождение объектов в наборы' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mre_regobj_setobj'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_mre_regobj_setobj_rs] ON [dbo].[mre_regobj_setobj]
(
[rs_rgobj_id] ASC,
[rs_setobj_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_mre_regobj_setobj_setobj] ON [dbo].[mre_regobj_setobj]
(
[rs_setobj_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE [dbo].[mre_regobjects_lst](
[rgob_id] [int] IDENTITY(1,1) NOT NULL,
[rgob_type] [tinyint] NOT NULL,
[rgob_object_id] [int] NULL,
[rgob_image_id] [bigint] NULL,
[rgob_name] [nvarchar](255) NULL,
[rgob_shortname] [nvarchar](25) NULL,
[jrn_create_date] [datetime] NOT NULL,
[jrn_create_user_id] [int] NOT NULL,
[jrn_modif_date] [datetime] NOT NULL,
[jrn_modif_user_id] [int] NOT NULL,
CONSTRAINT [PK_mre_regobjects_lst] PRIMARY KEY CLUSTERED
(
[rgob_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Тип карточки: 0 - ручное заведение объекта, 1 - по ссылке на mre_objects_lst' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mre_regobjects_lst', @level2type=N'COLUMN',@level2name=N'rgob_type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Объект' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mre_regobjects_lst', @level2type=N'COLUMN',@level2name=N'rgob_object_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Изображение объекта' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mre_regobjects_lst', @level2type=N'COLUMN',@level2name=N'rgob_image_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Наименование (используется если тип ручной)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mre_regobjects_lst', @level2type=N'COLUMN',@level2name=N'rgob_name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Краткое наименование' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mre_regobjects_lst', @level2type=N'COLUMN',@level2name=N'rgob_shortname'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Объекты для учёта состояния' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mre_regobjects_lst'
GO
CREATE TABLE [dbo].[mre_regobj_states](
[rs_id] [bigint] IDENTITY(1,1) NOT NULL,
[rs_regobj_id] [int] NOT NULL,
[rs_bperiod] [datetime] NOT NULL,
[rs_eperiod] [datetime] NULL,
[rs_state_id] [int] NOT NULL,
[jrn_create_date] [datetime] NOT NULL,
[jrn_create_user_id] [int] NOT NULL,
[jrn_modif_date] [datetime] NOT NULL,
[jrn_modif_user_id] [int] NOT NULL,
CONSTRAINT [PK_mre_regobj_states] PRIMARY KEY CLUSTERED
(
[rs_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[mre_regobj_states] WITH CHECK ADD CONSTRAINT [FK_mre_regobj_states_regobj] FOREIGN KEY([rs_regobj_id])
REFERENCES [dbo].[mre_regobjects_lst] ([rgob_id])
GO
ALTER TABLE [dbo].[mre_regobj_states] CHECK CONSTRAINT [FK_mre_regobj_states_regobj]
GO
ALTER TABLE [dbo].[mre_regobj_states] WITH CHECK ADD CONSTRAINT [FK_mre_regobj_states_state] FOREIGN KEY([rs_state_id])
REFERENCES [dbo].[com_analitics_sp] ([ansp_id])
GO
ALTER TABLE [dbo].[mre_regobj_states] CHECK CONSTRAINT [FK_mre_regobj_states_state]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Объект' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mre_regobj_states', @level2type=N'COLUMN',@level2name=N'rs_regobj_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Действует с' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mre_regobj_states', @level2type=N'COLUMN',@level2name=N'rs_bperiod'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Действует по' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mre_regobj_states', @level2type=N'COLUMN',@level2name=N'rs_eperiod'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Состояние' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mre_regobj_states', @level2type=N'COLUMN',@level2name=N'rs_state_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Состояние объектов' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mre_regobj_states'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_mre_regobj_states_obj_bper_eper_u] ON [dbo].[mre_regobj_states]
(
[rs_regobj_id] ASC,
[rs_bperiod] ASC,
[rs_eperiod] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_mre_regobj_states_state_obj_per] ON [dbo].[mre_regobj_states]
(
[rs_state_id] ASC,
[rs_regobj_id] ASC,
[rs_bperiod] ASC,
[rs_eperiod] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE FUNCTION [dbo].[IntersectPeriodsLength](@Period1Begin datetime, @Period1End datetime, @Period2Begin datetime, @Period2End datetime)
RETURNS bigint
AS
BEGIN
if @Period1End < @Period1Begin or @Period2End < @Period2Begin
return null;
declare
@PeriodBeginMaximum datetime, @PeriodEndMinimum datetime, @Result bigint;
if @Period1Begin is null
set @PeriodBeginMaximum = @Period2Begin
else begin
if @Period2Begin is null
set @PeriodBeginMaximum = @Period1Begin
else begin
if @Period1Begin > @Period2Begin
set @PeriodBeginMaximum = @Period1Begin
else
set @PeriodBeginMaximum = @Period2Begin;
end;
end;
if @Period1End is null
set @PeriodEndMinimum = @Period2End
else begin
if @Period2End is null
set @PeriodEndMinimum = @Period1End
else begin
if @Period1End < @Period2End
set @PeriodEndMinimum = @Period1End
else
set @PeriodEndMinimum = @Period2End;
end;
end;
if @PeriodBeginMaximum is null or @PeriodEndMinimum is null
set @Result = null
else
set @Result = datediff_big(second, @PeriodBeginMaximum, @PeriodEndMinimum);
return @Result;
END
GO
|
