Форум программистов, компьютерный форум, киберфорум
Microsoft SQL Server
Войти
Регистрация
Восстановить пароль
 
Рейтинг 4.89/9: Рейтинг темы: голосов - 9, средняя оценка - 4.89
krolig
0 / 0 / 0
Регистрация: 15.11.2013
Сообщений: 14
1

Оптимизировать и распространить скрипты с OUTER APPLY и LEFT OUTER JOIN

28.02.2016, 18:38. Просмотров 1747. Ответов 14
Метки нет (Все метки)

Оптимизировать и распространить скрипты с OUTER APPLY и LEFT OUTER JOIN


Оптимизировать и распространить скрипты с OUTER APPLY и LEFT OUTER JOIN


Люди добрые, подскажите кто что сможет, пожалуйста!

На картинках (если я правильно сумел их прикрепить)
я отобразил результат своей работы, всё работает правильно, но...

С использованием LEFT OUTER JOIN:
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
WITH cte AS
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY Id_hour) AS rn,
        dTime
    FROM HourStat_2
    WHERE UVP001TP01_HWEnSum_kWh IS NOT NULL
)
 
SELECT
    Id_Hour,
    dTime,
    UVP001TP01_HWEnSum_kWh,
    asd.OPT_1
FROM HourStat_2
LEFT OUTER JOIN
(
    SELECT 
        T2.dTime AS Date2,
        OPT_1 =
        CASE
            WHEN DATEDIFF(hh,T1.dTime,T2.dTime) > 1 THEN
                '4'
            ELSE
                NULL
        END 
    FROM cte AS T1, cte AS T2
    WHERE T1.rn = (T2.rn - 1)
) AS asd
ON HourStat_2.dTime = asd.Date2
ORDER BY HourStat_2.Id_hour
С использованием OUTER APPLY:
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
ALTER FUNCTION MYFUNC(@DT AS datetime)
RETURNS @MT TABLE (OPT_1 INT) AS 
BEGIN
    WITH cte AS
    (
        SELECT
            ROW_NUMBER() OVER(ORDER BY Id_hour) AS rn,
            dTime
        FROM HourStat_2
        WHERE UVP001TP01_HWEnSum_kWh IS NOT NULL AND dTime <= @DT
    )
    INSERT @MT
    SELECT TOP 1
        OPT_1 =
            CASE
                WHEN DATEDIFF(hh,T1.dTime,T2.dTime) > 1 THEN
                    '4'
                ELSE
                    NULL
            END 
    FROM cte AS T1, cte AS T2
    WHERE T2.dTime = @DT AND T1.rn = (T2.rn - 1)
RETURN
END;
GO
 
SELECT Id_hour, dTime, UVP001TP01_HWEnSum_kWh, RES.OPT_1
FROM HourStat_2
OUTER APPLY 
MYFUNC(dTime) AS RES
Задача то, что сделано для столбца UVP001TP01_HWEnSum_kWh распространить на остальные столбцы.
При этом меня пугает перспектива разрастания скрипта (методом копипаса) до размеров=текущий*колво_столбцов,
а также то, что для одного указанного столбца скрипт уже выполняется минуту, что будет после распространения его на остальные столбцы - страшно представить!

Вопросы: 1) как можно скомпановать скрипт и применить к остальным столбцам с меньшим размером скрипта?
2) как можно повысить производительность (скорость выполнения скрипта, что можно оптимизировать, как)?

Алгоритм моих скриптов:
1) ДАНО: пишем ежечасно электроэнергию по объектам предприятия; записи могут быть пропущены при "перезагрузке" сервера (в соседних строках разность ID=1, разница Time > 1 часа), либо при потери связи с объектами (последовательность времени записи строк сохранена, но значения энергии в момент записи NULL).
Цель - пометить в СУБД поля после таких пробелов в учёте цветом (для этого ввожу столбец OPT_1 с номером цвета).
2) ДЕЛАЮ РАЗ: создаю "временную таблицу", где отсутствуют NULL для заданного объекта (столбца) и делаю вычисляемое поле RowNumber - чтобы восстановить последовательную нумерацию строк после перезагрузок сервера (для обеспечения условия выборки типа rn1=rn2-1 даже для пропущенных строк).
ДЕЛАЮ ДВА: соединяю полученную "временную таблицу" саму с собой со смещением типа rn1=rn2-1.
ДЕЛАЮ ТРИ: считаю разность дат-времён между записями, создавая вычисляемое поле OPT_1, только в него пишу не само количество пропущенных часов DTdiff, а метку "4", если DTdiff > 1.
ДЕЛАЮ ЧЕТЫРЕ: Добавляю к самой исходной таблице полученный вычисляемый столбец OPT_1, рассчитаный пока только для одного объекта предприятия (столбца таблицы).

Любая доп. инфа - по запросу.
0
QA
Эксперт
41792 / 34177 / 6122
Регистрация: 12.04.2006
Сообщений: 57,940
28.02.2016, 18:38
Ответы с готовыми решениями:

Left outer join возвращает null
SELECT .*, s.Id as SiteId, . as , ., . as , b. as BlogTitle, t.Cost FROM Sites as s INNER JOIN...

left outer join по паре полей
есть две таблицы, ключ двойной, как их склеить left outer join'ом чтобы множество не расширялось...

Запрос с двумя соединениями outer apply
Не нашел пример как применить два соединения Использования такого запроса не возвращает строки с...

Объединение id после outer join
Добрый день. Недавно начал изучать SQL и пытаюсь понять как оптимально реализовать следующее. ...

14
invm
2584 / 1652 / 563
Регистрация: 02.06.2013
Сообщений: 4,090
28.02.2016, 20:52 2
Цитата Сообщение от krolig Посмотреть сообщение
как можно скомпановать скрипт и применить к остальным столбцам с меньшим размером скрипта?
T-SQL
1
2
3
4
5
6
7
8
select
 ...
from
 MyTable a outer apply
 (select top (1) case when datediff(hour, a.dTime, dTime) > 1 then '4' end from MyTable where column1 is not null and dTime < a.dTime order by dTime desc) b(opt_column1) outer apply
 (select top (1) case when datediff(hour, a.dTime, dTime) > 1 then '4' end from MyTable where column2 is not null and dTime < a.dTime order by dTime desc) b(opt_column2) outer apply
 ...
 (select top (1) case when datediff(hour, a.dTime, dTime) > 1 then '4' end from MyTable where columnN is not null and dTime < a.dTime order by dTime desc) b(opt_columnN);
Цитата Сообщение от krolig Посмотреть сообщение
как можно повысить производительность (скорость выполнения скрипта, что можно оптимизировать, как)?
Никак. Вы стали жертвой ошибок проектирования.
Начните изучать нормальные формы и приведите вашу таблицу к 3НФ. Только после этого можно будет думать о производительности.
1
krolig
0 / 0 / 0
Регистрация: 15.11.2013
Сообщений: 14
28.02.2016, 23:02  [ТС] 3
Спасибо, завтра попробую, предложенный вариант сокращения текста скрипта.

А по поводу 3НФ:

1) правильно ли я понимаю, что согласно ей мне следует декомпазировать моё отношение на следующие сущности:
а) справочник записей (id_hour, date-time);
б) отдельный справочник для каждого объекта (record_id, id_hour, value_kWh), id_hour - внешний ключ?

2) правильно ли понимаю, что date-time - в моём случае неделим, согласно определению потенциального ключа (из википедии), к которому я приравниваю данный атрибут, для выполнения его требования минимальности я должен сохранить связку дата-время, иначе оба атрибута (и дата и время) друг без друга потеряют свою уникальность и такая декомпозиция привела бы к потерям?

Если всё вышеперечисленное правильно, то тогда возникает проблема вставки записей: до сих пор это был один общий INSERT и весь контроль консистентности (целостности) данных возлагался на сервер, а в случае декомпозиции - я сначала должен буду вставить дату-время в "справочник записей", чтобы сгенерировать autoincrement очередной id_hour, а потом выждав профилактическое время (на выполнение вставки), сделать выборку последнего Id_hour и только после этого вставлять по отдельности в каждую таблицу... А если в этот момент сервер глохнет - консистентность рассыпется (до сих пор всё делалось одним мгновенным INSERT под ответственность MS SQL Server-а)? Как быть с этим?
0
invm
2584 / 1652 / 563
Регистрация: 02.06.2013
Сообщений: 4,090
29.02.2016, 12:32 4
krolig, у вас таблица вида
T-SQL
1
2
3
4
5
6
7
create table ...
(
 id int,
 idTime datetime,
 column1 ...
 columnN ...
)
где column1 - columnN показания неких датчиков?

Тогда нормализация ни при чем. Преобразуйте таблицу к виду
T-SQL
1
2
3
4
5
6
7
create table ...
(
 id int,
 idTime datetime,
 idSensor int,
 Value ...
)
Плюс, для поиска предыдущих/следующих значений, индекс (idSensor, idTime) include (value)
1
pincet
1442 / 1017 / 140
Регистрация: 23.07.2010
Сообщений: 5,640
29.02.2016, 15:35 5
Цитата Сообщение от invm Посмотреть сообщение
индекс (idSensor, idTime) include (value)
сорри, что в той же теме. Давно хотел спросить более опытных товарищей - include() по полям, хранящим агрегатные состояния (температура, цена etc ) как улучшит работоспособность?
0
krolig
0 / 0 / 0
Регистрация: 15.11.2013
Сообщений: 14
29.02.2016, 15:40  [ТС] 6
То есть, например, 20000 строк по 80 столбцов превратить в 1600000 строк по 2 столбца, а потом для сводного отчёта JOIN-ить их селектами по WHERE idSensor =1,2...N? Я правильно понял идею?
0
pincet
1442 / 1017 / 140
Регистрация: 23.07.2010
Сообщений: 5,640
29.02.2016, 15:50 7
абсолютно. один датчик - одна запись в журнале регистрации
1
invm
2584 / 1652 / 563
Регистрация: 02.06.2013
Сообщений: 4,090
29.02.2016, 15:58 8
Цитата Сообщение от pincet Посмотреть сообщение
Давно хотел спросить более опытных товарищей - include() по полям, хранящим агрегатные состояния (температура, цена etc ) как улучшит работоспособность?
Неважно что в них хранится.
Include-столбцы придумали чтобы избавится от необходимости лезть за их значениями в основную таблицу. Т.е. вместо Index Seek + Key/RID Lookup теперь будет просто Index Seek.
Образно можете считать индекс с include-столбцами как отдельную таблицу с кластерным индексом или служебным индексированным представлением.
Цитата Сообщение от krolig Посмотреть сообщение
Я правильно понял идею?
Да.
Проектировать таблицу следует исходя из эффективности работы с ней, а не как ее данные должны выглядеть в отчетах.
2
krolig
0 / 0 / 0
Регистрация: 15.11.2013
Сообщений: 14
29.02.2016, 16:48  [ТС] 9
Да, действительно, пожалуй, вы правы: это позволит мне использовать функцию в APPLY для распространения обработки на все датчики (пока они по столбцам - имена столбцов нельзя в функцию передавать; а когда они id-шниками в столбце - я смогу передать id параметром, круто)! Но вот только для выборки сводной таблицы, где значения полей - разницы значений между соседними записями для каждого конкретного датчика - нужно будет сделать JOIN WHERE ids=1,..N, затем этот селект сждойнить с самим-собой по условию T1.(индекс (idSensor, idTime) include (value))=T2.(индекс (idSensor, idTime) include (value)) - 1, и уже из результата формировать таблицу разниц - будет ли тогда многострочный малостолбцовый вариант работать бысрее, чем малострочный многостолбцовый (мне пока, предварительно, без эксперимента видется, что многостолбцовый вариант в этом конкретном случае окажется уже более подготовленным для такой выборки и сэкономит время)? Ведь, насколько я понимаю, за время отвечают строки, а столбцы - только за место на диске, при условии, что обработка строк не умножается на количество столбцов, но тогда одинаково должно быть для указанного селекта?
0
invm
2584 / 1652 / 563
Регистрация: 02.06.2013
Сообщений: 4,090
29.02.2016, 17:07 10
Цитата Сообщение от krolig Посмотреть сообщение
это позволит мне использовать функцию в APPLY для распространения обработки на все датчики
Что за привязанность к многооператорным табличным функциям и к нумерации? Вам было показано как делать без функций и нумерации.
Цитата Сообщение от krolig Посмотреть сообщение
Ведь, насколько я понимаю, за время отвечают строки, а столбцы - только за место на диске, при условии, что обработка строк не умножается на количество столбцов, но тогда одинаково должно быть для указанного селекта?
У вас весьма приблизительное понимание о производительности запросов. Точнее, практически полное непонимание.
Чтобы сделать сводный отчет вам нужно отобрать во временную таблицу строки за требуемый диапазон вместе с вожделенным столбцом-цветом. Затем, на основе этой таблицы построить динамический PIVOT.
1
krolig
0 / 0 / 0
Регистрация: 15.11.2013
Сообщений: 14
29.02.2016, 22:10  [ТС] 11
Это что же получается, поскольку ежечасно в мою таблицу добавляются записи - мне придётся при каждом запросе на отчёт заново создавать индексацию? А это стоит того (не будет ли это дольше, чем запрос без индексации)? Если я правильно понял идею.

Добавлено через 20 минут
И что порекомендуете для моего случая в качестве временных таблиц (вариантов несколько и прокаждый столько минусов пишут, что я растерялся): @table, CREATE #table, или SELECT INTO #table? А может мне лучше представление создать (ведь поскольку запросы на отчёт будут происходить из СУБД-приложения автономно, по нажатии кнопки, без моего участия - наверное следует избегать DDL на этапе эксплуатации)?
0
invm
2584 / 1652 / 563
Регистрация: 02.06.2013
Сообщений: 4,090
29.02.2016, 23:19 12
Цитата Сообщение от krolig Посмотреть сообщение
мне придётся при каждом запросе на отчёт заново создавать индексацию?
Индексация в запросе не создается. Ну почитайте уже хоть что-нибудь по продукту, которым пользуетесь.
Цитата Сообщение от krolig Посмотреть сообщение
И что порекомендуете для моего случая в качестве временных таблиц
В качестве временных таблиц могут быть только временные таблицы. Остальное уже не временные таблицы.
Цитата Сообщение от krolig Посмотреть сообщение
вариантов несколько и прокаждый столько минусов пишут, что я растерялся
Да? И что же пишут?
Цитата Сообщение от krolig Посмотреть сообщение
А может мне лучше представление создать (ведь поскольку запросы на отчёт будут происходить из СУБД-приложения автономно, по нажатии кнопки, без моего участия - наверное следует избегать DDL на этапе эксплуатации)
Представление для чего?
1
krolig
0 / 0 / 0
Регистрация: 15.11.2013
Сообщений: 14
29.02.2016, 23:51  [ТС] 13
Цитата Сообщение от krolig Посмотреть сообщение
мне придётся при каждом запросе на отчёт заново создавать индексацию?
Индексация в запросе не создается. Ну почитайте уже хоть что-нибудь по продукту, которым пользуетесь.
- да, читать-то я читатю, это правильно и действительно необходимо. Но я имею ввиду CREATE INDEX и ALTER INDEX - если я их сделаю один раз и сдам прогу в эксплуатацию, то смысла от этого не будет - придётся периодически повторять эти операции (перед каждым запросом наотчёт, а то когда же, иначе какой от них толк?), т. к. данные постоянно добавляются, а индексирование происходит, как я понял, полностью прочитав http://professorweb.ru/my/sql-server/2012/level3/3_5.php, только выполнением CREATE INDEX или ALTER INDEX. Вот, собственно ЧТЕНИЕ и породило мой вопрос )))

Цитата Сообщение от krolig Посмотреть сообщение
И что порекомендуете для моего случая в качестве временных таблиц
В качестве временных таблиц могут быть только временные таблицы. Остальное уже не временные таблицы.
- этого мне достаточно на столько, что остальные мои реплики потеряли смысл, но ради ответа на вопрос:
Да? И что же пишут?
- пишут, вот:
Вообще говоря, использования временных таблиц следует по возможности избегать. Если вам все же необходимо создать временную таблицу, то следуйте вышеперечисленным правилам, чтобы оказать мимнимальное влияние на производительность сервера.
При создании временных таблиц, не используйте операторы select into. Вместо этого создавайте таблицу с помощью оператора ddl, а затем наполняйте ее данными, используя insert into.
- остальное критика в адрес типа данных table (обсуждение которого неактуально после Вашего ответа).

Представление для чего?
- возникла не проверенная идея попробовать использовать VIEW вместо временных таблиц - это всё из-за стремления ограничиться использованием ddl только для этапа разработки, а при эксплуатации - только DML.
Хотя это лишь предпочтения, ни как не ограничение (если, конечно ADO-connetction обеспечит DDL - ни разу ещё не приходилось через ADO выходить за рамки DML - я конечно выясню этот вопрос, но на момент написания ответа данными не владею).
0
invm
2584 / 1652 / 563
Регистрация: 02.06.2013
Сообщений: 4,090
01.03.2016, 00:53 14
Цитата Сообщение от krolig Посмотреть сообщение
т. к. данные постоянно добавляются, а индексирование происходит, как я понял, полностью прочитав http://professorweb.ru/my/sql-server...level3/3_5.php, только выполнением CREATE INDEX или ALTER INDEX.
И где в статье написано, что модификации данных не отражаются в соответствующих индексах?
Цитата Сообщение от krolig Посмотреть сообщение
пишут, вот
Оригинал статьи опубликован в 2004 г. и описывает проблемы, характерные для версий SQL Server младше 2000-го, т.е. почти 20-летней давности.
Цитата Сообщение от krolig Посмотреть сообщение
возникла не проверенная идея попробовать использовать VIEW вместо временных таблиц
VIEW - нематериальны. Как вы их собрались использовать вместо временных таблиц совершенно не понятно.
Цитата Сообщение от krolig Посмотреть сообщение
это всё из-за стремления ограничиться использованием ddl только для этапа разработки
Очередная идея фикс? Любое средство в T-SQL - всего лишь инструмент, не более и не менее. А инструментом нужно уметь пользоваться и понимать как он работает.

А вместо чтения статей сомнительного содержания, лучше приобретите себе книги вот этого автора - http://www.ozon.ru/person/4510513/
1
krolig
0 / 0 / 0
Регистрация: 15.11.2013
Сообщений: 14
01.03.2016, 12:48  [ТС] 15
И где в статье написано, что модификации данных не отражаются в соответствующих индексах?
- в разделе ИЗМЕНЕНИЕ ИНДЕКСОВ в подразделе ПЕРЕСОЗДАНИЕ ИНДЕКСОВ:
При любом изменении данных, используя инструкции INSERT, UPDATE или DELETE, возможна фрагментация данных. Если эти данные проиндексированы, то также возможна фрагментация индекса, когда информация индекса оказывается разбросанной по разным физическим страницам. В результате фрагментации данных индекса компонент Database Engine может быть вынужден выполнять дополнительные операции чтения данных, что понижает общую производительность системы. В таком случае требуется пересоздать (REBUILD) все фрагментированные индексы.
- но теперь (благодаря диалогу) доходит до меня, что это также часто требуется как и дефрагментация магнитного диска (редко, когда уже невмоготу).

20-летней давности.
- здорово, тогда долой мой опасения.

VIEW - нематериальны.
рассуждал так: раз они создаются как результат запроса так сделать сразу запрос на нужный мне результат, чтобы для отчёта только фильтр по дате осталось накладывать... но это проверять надо (есть опасения, что create view не примет сложный скрипт или тормозить будет жутко). 15 лет VIEW не использовал (с института) забывать начал, помню только, что удобно было ими из нескольких справочников сводную таблицу отображать.

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

книги вот этого автора
-спасибо, полезно. А то, действительно, в инете пишут кто во что горазд - попробуй новичком разбери что актуально что нет... А тут книга, всё по порядку (не просто справочник-мануал без которого тоже нельзя, но в книге, думаю, важна последовательность и доходчивость).
0
01.03.2016, 12:48
Answers
Эксперт
37091 / 29110 / 5898
Регистрация: 17.06.2006
Сообщений: 43,301
01.03.2016, 12:48

Заказываю контрольные, курсовые, дипломные и любые другие студенческие работы здесь.

INNER и OUTER
Может кто простым языком на пальцах объяснить в чем отличия INNER JOIN от FULL OUTER JOIN, LEFT...

LEFT JOIN ???
После выполнения запроса с использованием LEFT JOIN в конце результирующего набора данных...

СТЕ и left join
Суть вопроса в том что левое соединение рузультирует таблицу, которую можно получить только...

Запрос по left join
Здравствуйте. Подскажите логику запроса. Имеется стандартный select A.name1, A.name2, count...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
15
Ответ Создать тему
Опции темы

КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2020, vBulletin Solutions, Inc.