Alex_Toms [игнорируется]
!!!круто, да чуть быстрее и план чистый (без повторов) как с pivot СПАСИБО.
сейчас с этим типом запроса объектов одного типа и их атрибутами - проблем практически не осталось
(ps
1. from attrib_value av inner join value_string v менял на from value_string v left join attrib_value av поскольку attrib_value очень большая ~9 000 000 строк а таблицы дат чисел и строк значительно короче.. типа гонки за микросекундами
2. >>это без внешних функций - с ними наверно минут на 50 - это в результатах итоговой select выборки - читаю вспомогательные данные из udf - подзапросы короче - в mssql теже тормоза - мои проблемы
)
сейчас основная засада когда делаю запрос по нескольким объектам через left join с подгонкой атрибутов к ним
в итоге в двух словах
1. если сначала собрать все id разных типов объектов в одном cte и дальше при его чтении бомбить таблицы атрибутов = более менее быстро
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.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
with
_base as not MATERIALIZED (
Select *
From
(
Select
rw.id as prod_id,
coalesce(vv5,0) AS prod_del_id,
vv2 as prod_dvl_id,
vv6 as prod_clt_id,
vv8 as prod_cnt_id,
rw.description as prod_description
From
lsdbo.Ric_Get_Select(1,0,'PSft','%%','',0,'') rw
left join lateral
(
select
min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081600000) vv0
,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100000648000000) vv5
,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081200000) vv2
,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004060300000) vv6
,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081100000) vv8
from
LSDBO.attrib_value av left join
LSDBO.value_numeric nv on av.Value_ID = nv.id
where
rw.id = av.Object_ID
) nv
on true
where vv0>0
) as table_prod
left join
(
SELECT
rw.id as dvl_id,
rw.description AS dvl_description
FROM
LSDBO.object_reference AS rw left join LSDBO.object_type AS tw ON rw.type_id = tw.id
WHERE
tw.mnemo ='Dvl'
) as table_dvl
on (table_prod.prod_dvl_id = table_dvl.dvl_id)
left join
(
Select
rw.id AS del_id,
rw.description AS del_description
From
LSDBO.object_reference AS rw left join LSDBO.object_type AS tw ON rw.type_id = tw.id
Where
tw.mnemo='Del'
) as table_del
on (table_prod.prod_del_id =table_del.del_id)
left join
(
Select
rw.id AS clt_id,
rw.description AS clt_description
From
LSDBO.object_reference AS rw left join LSDBO.object_type AS tw ON rw.type_id = tw.id
Where
tw.mnemo=any(values ('Dvl'),('Clt'),('Cnt'))
) as table_clt
on (table_prod.prod_clt_id =table_clt.clt_id)
left join
(
Select
rw.id AS cnt_Id,
rw.description AS cnt_description
From
LSDBO.object_reference AS rw left join LSDBO.object_type AS tw ON rw.type_id = tw.id
Where
tw.mnemo = 'Cnt'
)as table_cnt
on (table_prod.prod_cnt_id =table_cnt.cnt_Id)
),
_clt_ext as MATERIALIZED (
select
DISTINCT ON (prod_clt_id) prod_clt_id as table_obj_id
,(Select lsdbo.Ric_Get_MarketingActionsAndSales(prod_clt_id::numeric,12)) as Clt_Block
,(select LSDBO.Ric_Get_DealsFirstSaleDate(prod_clt_id::numeric)) as Clt_dFirstSale
,(select LSDBO.Ric_Get_OccurrenceSource(prod_clt_id::numeric)) as Req_ist_CRM
,(select LSDBO.Ric_Get_DealsAllSaleCount(prod_clt_id::numeric)) as Clt_cAllSale
from _base
where prod_clt_id>0
)
select
b.*
,vv,vv5,vv6,vv8,vv2,vv0
,vv12,vv3,vv13,vv4,vv9
,vv1,vv11,vv7
,vv_,vv3_,vv6_,vv8_
,vv0_,vv1_,vv2_
,vv5__
,vv0__,vv1__,vv3__,vv2__,vv6__,vv7__
,vv___,vv1___,vv2___,vv4___,vv5___,vv6___,vv7___,vv8___,vv9___,vv10___,vv11___,vv14___,vv15___,vv16___,vv17___
from
_base b
cross join lateral (
select
max(case when av.Attrib_ID = 100000019000005 then v.value end) as vv
,max(case when av.Attrib_ID = 100000648000000 then v.value end) as vv5
,max(case when av.Attrib_ID = 100004060300000 then v.value end) as vv6
,max(case when av.Attrib_ID = 100004081100000 then v.value end) as vv8
,max(case when av.Attrib_ID = 100004081200000 then v.value end) as vv2
,max(case when av.Attrib_ID = 100004081600000 then v.value end) as vv0
from attrib_value av
inner join value_numeric v on av.Value_ID = v.id
where b.prod_id = av.Object_ID
and av.Attrib_ID =any (values (100000019000005),(100000648000000),(100004060300000),(100004081100000),(100004081200000),(100004081600000))
)vn
cross join lateral (
select
max(case when av.Attrib_ID = 3000000000101 then v.value end) as vv12
,max(case when av.Attrib_ID = 3000000000122 then v.value end) as vv3
,max(case when av.Attrib_ID = 100004091100000 then v.value end) as vv13
,max(case when av.Attrib_ID = 100007986500005 then v.value end) as vv4
,max(case when av.Attrib_ID = 100037488300000 then v.value end) as vv9
from attrib_value av
inner join value_string v on av.Value_ID = v.id
where b.prod_id = av.Object_ID
and av.Attrib_ID =any (values (3000000000101),(3000000000122),(100004091100000),(100007986500005),(100037488300000))
)vs
cross join lateral (
select
max(case when av.Attrib_ID = 8 then v.value end) as vv1
,max(case when av.Attrib_ID = 9 then v.value end) as vv11
,max(case when av.Attrib_ID = 3000000000108 then v.value end) as vv7
from attrib_value av
inner join value_datetime v on av.Value_ID = v.id
where b.prod_id = av.Object_ID
and av.Attrib_ID =any (values (8),(9),(3000000000108))
)vd
cross join lateral (
select
max(case when av.Attrib_ID = 100000019000005 then v.value end) as vv_
,max(case when av.Attrib_ID = 100004060200000 then v.value end) as vv3_
,max(case when av.Attrib_ID = 100004087100000 then v.value end) as vv6_
,max(case when av.Attrib_ID = 100004087200000 then v.value end) as vv8_
from attrib_value av
inner join value_numeric v on av.Value_ID = v.id
where b.del_id = av.Object_ID
and av.Attrib_ID =any (values (100000019000005),(100004060200000),(100004087100000),(100004087200000))
)vn_
cross join lateral (
select
max(case when av.Attrib_ID = 3000000000105 then v.value end) as vv0_
,max(case when av.Attrib_ID = 100004086600000 then v.value end) as vv1_
from attrib_value av
inner join value_string v on av.Value_ID = v.id
where b.del_id = av.Object_ID
and av.Attrib_ID =any (values (3000000000105),(100004086600000))
)vs_
cross join lateral (
select
max(case when av.Attrib_ID = 3000000000106 then v.value end) as vv2_
from attrib_value av
inner join value_datetime v on av.Value_ID = v.id
where b.del_id = av.Object_ID
and av.Attrib_ID =any (values (3000000000106))
)vd_
cross join lateral (
select
max(case when av.Attrib_ID = 100004087100000 then v.value end) as vv5__
from attrib_value av
inner join value_numeric v on av.Value_ID = v.id
where b.clt_id = av.Object_ID
and av.Attrib_ID =any (values (100004087100000))
)vn__
cross join lateral (
select
max(case when av.Attrib_ID = 100000016000000 then v.value end) as vv0__
,max(case when av.Attrib_ID = 3000000000002 then v.value end) as vv1__
,max(case when av.Attrib_ID = 100004974000000 then v.value end) as vv3__
,max(case when av.Attrib_ID=100003121500000 then v.value end) as vv2__
,max(case when av.Attrib_ID = 3000000000004 then v.value end) as vv6__
,max(case when av.Attrib_ID = 100004086100000 then v.value end) as vv7__
from attrib_value av
inner join value_string v on av.Value_ID = v.id
where b.clt_id = av.Object_ID
and av.Attrib_ID =any (values (100000016000000),(3000000000002),(100004974000000),(100003121500000),(3000000000004),(100004086100000))
)vs__
cross join lateral (
select
max(case when av.Attrib_ID = 3000000000086 then v.value end) as vv___
,max(case when av.Attrib_ID = 3000000000085 then v.value end) as vv1___
,max(case when av.Attrib_ID = 100004086400000 then v.value end) as vv2___
,max(case when av.Attrib_ID = 3000000000021 then v.value end) as vv4___
,max(case when av.Attrib_ID = 3000000000024 then v.value end) as vv5___
,max(case when av.Attrib_ID = 3000000000023 then v.value end) as vv6___
,max(case when av.Attrib_ID = 3000000000025 then v.value end) as vv7___
,max(case when av.Attrib_ID = 3000000000041 then v.value end) as vv8___
,max(case when av.Attrib_ID = 3000000000042 then v.value end) as vv9___
,max(case when av.Attrib_ID = 3000000000043 then v.value end) as vv10___
,max(case when av.Attrib_ID = 3000000000062 then v.value end) as vv11___
,max(case when av.Attrib_ID = 3000000000022 then v.value end) as vv14___
,max(case when av.Attrib_ID = 3000000000087 then v.value end) as vv15___
,max(case when av.Attrib_ID = 100004975500000 then v.value end) as vv16___
,max(case when av.Attrib_ID = 3000000000061 then v.value end) as vv17___
from attrib_value av
inner join value_string v on av.Value_ID = v.id
where b.cnt_id = av.Object_ID
and av.Attrib_ID =any (values
(3000000000086),(3000000000085),(100004086400000),(3000000000021),(3000000000024),(3000000000023),(3000000000025),
(3000000000041),(3000000000042),( 3000000000043),(3000000000062),(3000000000022),(3000000000087),(100004975500000),(3000000000061)
)
)vs___
2. если сделать несколько cte запросов в каждом из которых запрос по одному типу объекта (с уникальностью и т.д.) + его атрибуты и потом при чтении в итоге сложить их через left join все очень медленно (хотя каждый из них по отдельности быстрый) - вот с этим ничего пока не могу сделать (ели бы их параллельно запускать эти cte по каждому типу... )
имел в виду вот этот тормоз - просто общий смысл показать
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.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
with
_base as MATERIALIZED (
Select
rw.id as prod_id,
coalesce(vv5,0) AS prod_del_id,
vv2 as prod_dvl_id,
vv6 as prod_clt_id,
vv8 as prod_cnt_id,
rw.description as prod_description
From
lsdbo.Ric_Get_Select(1,0,'PSft','%%','',0,'') rw
left join lateral
(
select
min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081600000) vv0
,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100000648000000) vv5
,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081200000) vv2
,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004060300000) vv6
,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081100000) vv8
from
LSDBO.attrib_value av left join
LSDBO.value_numeric nv on av.Value_ID = nv.id
where
rw.id = av.Object_ID
) nv
on true
where vv0>0
),
_prod as not MATERIALIZED (
select
rw.id as isobject_id,
rw.description as Prod_Name,
rw.author_id as Prod_idAuthor,
vv3 AS Prod_Manager,
vv1 AS Prod_dBegin,
Case abs(vv) when 11 then 'Программы' when 21 then 'Оборудование'
when 31 then 'Прочие продукты' when 41 then 'Услуги'
when 50 then 'Заявки на курсы' when 51 then 'Заявки на курсы'
else '...' end AS Prod_Type,
vv12 as Prod_aName,
vv0 as Prod_Count,
vv4 AS Prod_Status,
vv7 AS Prod_dStatus,
vv11 AS Prod_dEnd,
vv9 AS Prod_xml,
vv13 as Prod_sNumber
from LSDBO.object_reference AS rw
left join lateral
(
select
min(nv.value) FILTER(WHERE av.Attrib_ID = 100000019000005) vv
,min(nv.value) FILTER(WHERE av.Attrib_ID = 100004081600000) vv0
,min(dv.value) FILTER(WHERE av.Attrib_ID = 8) vv1
,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000122) vv3
,min(sv.value) FILTER(WHERE av.Attrib_ID = 100007986500005) vv4
,min(dv.value) FILTER(WHERE av.Attrib_ID = 3000000000108) vv7
,min(sv.value || coalesce(sv.value1,'')) FILTER(WHERE av.Attrib_ID = 100037488300000) vv9
,min(dv.value) FILTER(WHERE av.Attrib_ID = 9) vv11
,min(sv.value || coalesce(sv.value1,'')) FILTER(WHERE av.Attrib_ID = 3000000000101) vv12
,min(sv.value) FILTER(WHERE av.Attrib_ID = 100004091100000) vv13
from
LSDBO.attrib_value av left join
LSDBO.value_numeric nv on av.Value_ID = nv.id left join
LSDBO.value_string sv on av.Value_ID = sv.id left join
LSDBO.value_datetime dv on av.Value_ID = dv.id
where
rw.id = av.Object_ID
) nv
on true
where rw.id in (select DISTINCT prod_id from _base where prod_id is not null)
),
_dev as not MATERIALIZED (
select
rw.id as Dvl_ID,
rw.description as Dvl_Name
from LSDBO.object_reference AS rw
where rw.id in (select DISTINCT prod_dvl_id from _base where prod_dvl_id is not null)
),
_del as not MATERIALIZED (
select
rw.id AS Del_ID,
rw.description as Del_Name,
vv1 as Del_Account,
vv2 AS Del_dAccount,
vv0 AS Del_Status,
vv3,
Case abs(vv) when 10 then 'Клиент' when 20 then 'Дилер'
when 30 then 'Клиент Дилера' when 40 then 'Частное лицо'
when 50 then 'Агент' else '...' end AS Del_Channel
from LSDBO.object_reference AS rw
left join lateral
(
select
min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000105) vv0
,min(sv.value) FILTER(WHERE av.Attrib_ID = 100004086600000) vv1
,min(dv.value) FILTER(WHERE av.Attrib_ID = 3000000000106) vv2
,min(nv.value) FILTER(WHERE av.Attrib_ID = 100000019000005) vv
,min(nv.value) FILTER(WHERE av.Attrib_ID = 100004060200000) vv3
from
LSDBO.attrib_value av left join
LSDBO.value_numeric nv on av.Value_ID = nv.id left join
LSDBO.value_string sv on av.Value_ID = sv.id left join
LSDBO.value_datetime dv on av.Value_ID = dv.id
where
rw.id = av.Object_ID
) nv
on true
where rw.id in (select DISTINCT prod_del_id from _base where prod_del_id is not null)
),
_clt as not MATERIALIZED (
select
rw.id AS Clt_id,
coalesce(vv0,'Частное лицо') AS Clt_Status,
rw.description || case rtrim(Coalesce(vv1,'')) when '' then '' else ', ' || rtrim(Coalesce(vv1,'')) end as Clt_Name,
coalesce(vv5,0) as Clt_ExtStatus,
rw.id,
vv3 as Clt_Manager,
vv6 || coalesce(' (' || vv7 || ')','') as Clt_Ist
from LSDBO.object_reference AS rw
left join lateral
(
select
min(sv.value) FILTER(WHERE av.Attrib_ID = 100000016000000) vv0
,min(nv.value) FILTER(WHERE av.Attrib_ID = 100004087100000) vv5
,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000002) vv1
,min(sv.value || coalesce(sv.value1,'')) FILTER(WHERE av.Attrib_ID = 100004974000000) vv3
,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000004) vv6
,min(sv.value) FILTER(WHERE av.Attrib_ID = 100004086100000) vv7
from
LSDBO.attrib_value av left join
LSDBO.value_numeric nv on av.Value_ID = nv.id left join
LSDBO.value_string sv on av.Value_ID = sv.id
where
rw.id = av.Object_ID
) nv
on true
where rw.id in (select DISTINCT prod_clt_id from _base where prod_clt_id is not null)
),
_cnt as not MATERIALIZED (
select
rw.id as cId,
coalesce(vv || ' ','') || ltrim(coalesce(vv15,'')) as cStatus,
rw.description as cName,
vv1 as cAction,
vv14 as cIndex,
vv4 as cStrana,
vv5 as cOkrug,
vv6 as cSity,
vv7 as cStreet,
vv8 as cKod,
vv9 as cFax,
vv10 as cTel,
vv11 as cEmail,
vv2 as cMessanger,
vv16 as cmTel,
vv17 as cWWW
from LSDBO.object_reference AS rw
left join lateral
(
select
min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000086) vv
,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000085) vv1
,min(sv.value) FILTER(WHERE av.Attrib_ID = 100004086400000) vv2
,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000021) vv4
,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000024) vv5
,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000023) vv6
,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000025) vv7
,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000041) vv8
,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000042) vv9
,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000043) vv10
,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000062) vv11
,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000022) vv14
,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000087) vv15
,min(sv.value) FILTER(WHERE av.Attrib_ID = 100004975500000) vv16
,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000061) vv17
from
LSDBO.attrib_value av left join
LSDBO.value_string sv on av.Value_ID = sv.id
where
rw.id = av.Object_ID
) nv
on true
where rw.id in (select DISTINCT prod_cnt_id from _base where prod_cnt_id is not null)
)
select
prod.isobject_id,
prod.Prod_idAuthor,
prod.Prod_Manager,
prod.Prod_dBegin,
prod.Prod_Type,
clt.Clt_id,
clt.Clt_Status,
clt.Clt_Name,
clt .Clt_ExtStatus,
clt.Clt_Manager,
cnt.cName,
cnt.cStatus,
cnt.cAction,
cnt.cIndex,
cnt.cStrana,
cnt.cOkrug,
cnt.cSity,
cnt.cStreet,
cnt.cKod,
cnt.cFax,
cnt.cTel,
cnt.cEmail,
cnt.cMessanger,
cnt.cmTel,
cnt.cWWW,
prod.Prod_Name,
prod.Prod_aName,
prod.Prod_Status,
prod.Prod_dStatus,
prod.Prod_Count,
prod.Prod_dEnd,
dev.Dvl_Name,
del.Del_Channel,
Coalesce(del.Del_Account,'') as Del_Account,
del.Del_dAccount,
del.Del_Name,
del.Del_Status,
prod.Prod_xml,
clt.Clt_Ist,
prod.Prod_sNumber
from _base b left join
_prod prod on b.prod_id = prod.isobject_id left join
_dev dev on b.prod_dvl_id= dev.Dvl_ID left join
_del del on b.prod_del_id= del.Del_ID left join
_clt clt on b.prod_clt_id= clt.Clt_id left join
_cnt cnt on b.prod_cnt_id= cnt.cId
поэтому пошел по первому варианту, добавил в него новый подход от вас типа так (потеряв конечно красоту плана - поскольку анализирую уже разные id от разных типов объектов в одной выборке)
|
