Алгоритм работы инструментов с пресетами (на примере Histogram Generator)

[BlackWrangler]

К сегодняшнему обсуждению с @ybinzu На входе Система получает от Пользователя выборку, и набор пресетов для Х и Y. На выходе - дает гистограмму исходя из внутреннего алгоритма.

https://8r4g9u.axshare.com/#g=1&p=algorithm_histogram_generator

[BlackWrangler]

Вот такой запрос за 3 секунды строит гистограмму по 97000 записям.

with Hierachy(ID, ParentID, TitleRus, Level)
as
(
select id, ParentID, TitleRus, 0 as Level
    from Protocols c
    where c.Id in (460424, 465325) -- insert parameter here
    union all
    select c.Id, c.ParentID, c.TitleRus, ch.Level + 1
    from Protocols c
    inner join Hierachy ch
    on c.ParentId = ch.ID
)

SELECT sum(ln207.Value) as list,
min(1+floor((1.00*(ln209.Value+ln210.Value+ln211.Value)/ln207.Value-0)/0.05)) as bin

From Hierachy 

JOIN 
(SELECT LineNumbers.ProtocolId, LineNumbers.Value FROM LineNumbers
WHERE LineNumbers.LineDescriptionId=207  ) ln207
ON (ln207.ProtocolId = Hierachy.ID)
 JOIN 
(SELECT LineNumbers.ProtocolId, LineNumbers.Value FROM LineNumbers
WHERE LineNumbers.LineDescriptionId=209  ) ln209
ON (ln209.ProtocolId = Hierachy.ID)
 JOIN 
(SELECT LineNumbers.ProtocolId, LineNumbers.Value FROM LineNumbers
WHERE LineNumbers.LineDescriptionId=210  ) ln210
ON (ln210.ProtocolId = Hierachy.ID)
 JOIN 
(SELECT LineNumbers.ProtocolId, LineNumbers.Value FROM LineNumbers
WHERE LineNumbers.LineDescriptionId=211  ) ln211
ON (ln211.ProtocolId = Hierachy.ID)

Group by 1+floor((1.00*(ln209.Value+ln210.Value+ln211.Value)/ln207.Value-0)/0.05)
order by bin

[artembaikuzin]

Вот такой запрос за 3 секунды строит гистограмму по 97000 записям.

with Hierachy(ID, ParentID, TitleRus, Level)
as
(
select id, ParentID, TitleRus, 0 as Level
    from Protocols c
    where c.Id in (460424, 465325) -- insert parameter here
    union all
    select c.Id, c.ParentID, c.TitleRus, ch.Level + 1
    from Protocols c
    inner join Hierachy ch
    on c.ParentId = ch.ID
)

select count(*) from Hierachy

(No column name)
2240

Не совсем по 97000 записям, там получается в запросе иерархии всего 2240 записей.

На примерно таких же запросах я тестировал - работает быстро, пока дело не доходит до больших выборок (90т протоколов), если данных много, то запрос начинает занимать минуты. Без временных/постоянных таблиц не обойтись похоже.

[BlackWrangler]

Согласен. Имелось ввиду отфильтровать из 97000 нужные регионы (я опасался торможения на этой стадии) По общей: если подгрузить временную, то экономия 40 секунд. И за 40 секунд строит по 97000. Я думаю, 40 секунд - удовлетворительно.

--подготавливаю временную таблицу

DECLARE @Timer datetime;
SET @Timer = GETDATE()

Select  LineNumbers.* 
INTO #tempLN09
from LineNumbers 
join 
LineDescriptions on LineDescriptions.id=LineNumbers.LineDescriptionId 
WHERE LineDescriptions.ProtocolSetId=9

SELECT DATEDIFF(ms, @Timer, GETDATE())/1000.00 as Seconds_to_run
GO 

DECLARE @Timer datetime;
SET @Timer = GETDATE();

with Hierachy(ID, ParentID, TitleRus, Level)
as
(
select id, ParentID, TitleRus, 0 as Level
    from Protocols c
    where c.ProtocolSetId=9 and c.ParentId is null

     -- insert parameter here
    union all
    select c.Id, c.ParentID, c.TitleRus, ch.Level + 1
    from Protocols c
    inner join Hierachy ch
    on c.ParentId = ch.ID
)

SELECT sum(ln207.Value) as list ,
min(1+floor((1.00*(ln209.Value+ln210.Value+ln211.Value)/ln207.Value-0)/0.05)) as bin

From Hierachy 

JOIN 
(SELECT #tempLN09.ProtocolId, #tempLN09.Value FROM #tempLN09
WHERE #tempLN09.LineDescriptionId=207  ) ln207
ON (ln207.ProtocolId = Hierachy.ID)
 JOIN 
(SELECT #tempLN09.ProtocolId, #tempLN09.Value FROM #tempLN09
WHERE #tempLN09.LineDescriptionId=209  ) ln209
ON (ln209.ProtocolId = Hierachy.ID)
 JOIN 
(SELECT #tempLN09.ProtocolId, #tempLN09.Value FROM #tempLN09
WHERE #tempLN09.LineDescriptionId=210  ) ln210
ON (ln210.ProtocolId = Hierachy.ID)
 JOIN 
(SELECT #tempLN09.ProtocolId, #tempLN09.Value FROM #tempLN09
WHERE #tempLN09.LineDescriptionId=211  ) ln211
ON (ln211.ProtocolId = Hierachy.ID)

Group by 1+floor((1.00*(ln209.Value+ln210.Value+ln211.Value)/ln207.Value-0)/0.05)
order by bin

SELECT DATEDIFF(ms, @Timer, GETDATE())/1000.00 as Seconds_to_run
GO 

Drop Table #tempLN09
--Drop Table #tempProtocols
GO

[BlackWrangler]

Вот есть ещё такой приём. Через Pivot можно и за 20 секунд строить и очень простыми запросами.

https://github.com/GolosMovement/StatElectGenerator/issues