Мы на новом форуме здесь
SQL
Страница: 1
Сообщений 1 страница 13 из 13
Поделиться12016-11-16 10:42:00
- Администратор
- Зарегистрирован: 2016-10-17
- Сообщений: 1710
- Уважение: [+171/-8]
- Пол: Мужской
Давай тут ...
Может кому-то тоже интересно будет!
Поделиться22016-11-16 16:18:56
Задача такая: есть таблица: тэги, значения и время, обновляется раз в 20 мин. Надо из нее брать раз в 20 мин значения, кидать в другую базу. Это элементарно, их штук 500. Но, кроме этого есть расчетные тэги, значения которых надо считать по разным простым и не очень простым формулам в зависимости от значений обычных тэгов. Таких больше сотни. Все наименования тэгов хранятся еще в одной базе справочников, у них есть признак - расчетный и не расчетный. + еще ко всему нужно налету считать всякие агрегатные функции - средние, макс. отклонения и т.п.
Сделано сейчас так:
берется из справочника курсор, по курсору бежим
- если тэг не расчетный - тупо записываем и считаем всякие функции.
- если расчетный - в зависимости от наименования тэга у каждого своя формула (многие похожие, но есть куча оригинальных)
Итого получилась самая большая развилка которую я когда либо делал. 
Т.к. гуглил - в t-sql нет обычного case, есть только внутри запросов. Его можно применить по-хитрому через
SET @STATEMENT=CASE @Tag
WHEN 'FI2303/FI2301' THEN ...
...
EXEC( @STATEMENT)
Но это не работает со сложными запросами внутри выражения. В общем, как это по-быстрому оптимизировать кроме GOTO на конец развилки в конце каждого IF - я не знаю. Но это как-то тупо и меня еще в школе отучили GOTO использовать 
Отредактировано privet (2016-11-16 16:35:04)
Поделиться32016-11-16 16:33:18
Кроме этого еще пару интересных моментов обнаружил. Если ты хорошо знаешь работу планировщика, может сможешь пояснить.
Если писать 1 запрос с join на саму себя:
SELECT '(TI4107A+07B+07C+07D)/4', (v1.Value+v2.Value+v3.Value+v4.Value)/4, v1.[Date], v1.SaveTime
FROM v_TagValuesFromAssb v1
JOIN v_TagValuesFromAssb v2 ON v1.[Date]=v2.[Date]
JOIN v_TagValuesFromAssb v3 ON v1.[Date]=v3.[Date]
JOIN v_TagValuesFromAssb v4 ON v1.[Date]=v4.[Date]
WHERE v1.Tag='U12_TI4107A.PV' And v2.Tag='U12_TI4107B.PV' AND v3.Tag='U12_TI4107C.PV' And v4.Tag='U12_TI4107D.PV'
And v1.[Date]>@DateStart And v2.[Date]>@DateStart And v3.[Date]>@DateStart And v4.[Date]>@DateStart
То эта штука работает гораздо дольше курсора с последовательным чтением в переменные:
DECLARE cur Cursor
FOR
SELECT
Value, [Date], SaveTime
FROM v_TagValuesFromAssb
WHERE Tag='U12_TI4107A.PV' And [Date]>@DateStart
OPEN cur
FETCH NEXT FROM cur INTO @v1, @dt, @savedt
WHILE (@@FETCH_STATUS =0)
BEGIN
SELECT @v2=v.Value FROM v_TagValuesFromAssb v WHERE v.Tag='U12_TI4107B.PV' AND v.[Date]=@dt
SELECT @v3=v.Value FROM v_TagValuesFromAssb v WHERE v.Tag='U12_TI4107C.PV' AND v.[Date]=@dt
SELECT @v4=v.Value FROM v_TagValuesFromAssb v WHERE v.Tag='U12_TI4107D.PV' AND v.[Date]=@dt
SET @sum=(@v1+@v2+@v3+@v4)/4
INSERT INTO TagValues (Tag, Value, [Date], SaveTime)
VALUES('(TI4107A+07B+07C+07D)/4', @sum, @dt, @savedt)
SELECT @v5=v.Value FROM v_TagValuesFromAssb v WHERE v.Tag='U12_TI4106.PV' AND v.[Date]=@dt
INSERT INTO TagValues (Tag, Value, [Date], SaveTime)
VALUES('(TI4107A+07B+07C+07D)/4-TI4106', @sum-@v5, @dt, @savedt)
SELECT @v6=v.Value FROM v_TagValuesFromAssb v WHERE v.Tag='U12_TI2127.PV' AND v.[Date]=@dt
INSERT INTO TagValues (Tag, Value, [Date], SaveTime)
VALUES('TIC2127-(TI4107A+07B+07C+07D)/4', @v6-@sum, @dt, @savedt)
FETCH NEXT FROM cur INTO @v1, @dt, @savedt
END
CLOSE cur
DEALLOCATE cur
И получается, что и вместо join один раз:
SELECT 'TI2126A-TI2125', v1.Value-v2.Value, v1.[Date], v1.SaveTime
FROM v_TagValuesFromAssb v1
JOIN v_TagValuesFromAssb v2 ON v1.[Date]=v2.[Date]
WHERE v1.Tag='U12_TI2126A.PV' And v2.Tag='U12_TI2125.PV' And v1.[Date]>@DateStart And v2.[Date]>@DateStart
будет быстрее работать последовательный выбор каждого значения в переменную.
Я как-нибудь протестирую.
Да, дело осложняется тем, что вьюха v_TagValuesFromAssb тащит данные из таблицы с 52 млн записей (и она растет) через linked сервер
Но эту часть мы переделываем, и будет один сервер, и индекс по наименованию тэга сделаем.
Поделиться42016-11-16 16:45:07
- Администратор
- Зарегистрирован: 2016-10-17
- Сообщений: 1710
- Уважение: [+171/-8]
- Пол: Мужской
СУБД MS SQL?
Поделиться52016-11-16 17:09:14
- Администратор
- Зарегистрирован: 2016-10-17
- Сообщений: 1710
- Уважение: [+171/-8]
- Пол: Мужской
#p11726,privet написал(а):
Кроме этого еще пару интересных моментов обнаружил. Если ты хорошо знаешь работу планировщика, может сможешь пояснить.
Если писать 1 запрос с join на саму себя:
SELECT '(TI4107A+07B+07C+07D)/4', (v1.Value+v2.Value+v3.Value+v4.Value)/4, v1.[Date], v1.SaveTime
FROM v_TagValuesFromAssb v1
JOIN v_TagValuesFromAssb v2 ON v1.[Date]=v2.[Date]
JOIN v_TagValuesFromAssb v3 ON v1.[Date]=v3.[Date]
JOIN v_TagValuesFromAssb v4 ON v1.[Date]=v4.[Date]
WHERE v1.Tag='U12_TI4107A.PV' And v2.Tag='U12_TI4107B.PV' AND v3.Tag='U12_TI4107C.PV' And v4.Tag='U12_TI4107D.PV'
And v1.[Date]>@DateStart And v2.[Date]>@DateStart And v3.[Date]>@DateStart And v4.[Date]>@DateStart
1. зачем тебе вообще джойны? У тебя одна таблица и связи идут по одному полю [Date], тем более оно в SARG?
2. Попробуй так...
SELECT
'(TI4107A+07B+07C+07D)/4',
sum(v.Value)/4,
v.[Date],
v.SaveTime
FROM v_TagValuesFromAssb v
WHERE v.Tag in ('U12_TI4107A.PV','U12_TI4107B.PV','U12_TI4107C.PV''U12_TI4107D.PV')
and v.[Date]>@DateStart
group by
v.[Date],
v.SaveTime
Попробуй так... сравни именно результат первого запроса и моего... если результат и скорость будут в норме, тогда допишу запрос до уровня курсора (с @v5 и @v6).
Потом пойдем дальше.
По GoTo пока не вникал
Поделиться62016-11-16 17:27:14
- Администратор
- Зарегистрирован: 2016-10-17
- Сообщений: 1710
- Уважение: [+171/-8]
- Пол: Мужской
#p11726,privet написал(а):
Да, дело осложняется тем, что вьюха v_TagValuesFromAssb тащит данные из таблицы с 52 млн записей (и она растет) через linked сервер
Но эту часть мы переделываем, и будет один сервер, и индекс по наименованию тэга сделаем.
Дело в том, что MS SQL не может нормально работать с прилинкованным сервером...
Почему у тебя тормоза. Он тянет таблицу 4 раза... Одну и туже 4 раза по 52 млн строк.... Это очень плохо!
В курсоре он читает ее 1 раз. поэтому и быстрей. Но курсор можно переделать под один запрос....
или схитрить с времянкой.
Я вечером тебе все распишу...
Вот SAP ASE, Бывший Sybase, они с MS на одной платформе были рождены, имеет такую чтуку как CIS. Так вот она посылает на удаленный сервер уже оптимизированный запрос. Там таких косяков не бывает. За это я и люблю Sybase. В MS, как обычно: "мы подумали за вас и решили что так лучше!"
Поделиться72016-11-16 17:52:14
#p11732,Dev написал(а):
СУБД MS SQL?
Да.
#p11750,Dev написал(а):
Вот SAP ASE, Бывший Sybase, они с MS на одной платформе были рождены, имеет такую чтуку как CIS. Так вот она посылает на удаленный сервер уже оптимизированный запрос. Там таких косяков не бывает. За это я и люблю Sybase. В MS, как обычно: "мы подумали за вас и решили что так лучше!"
Это точно.
С Sybase не работал, но с Oracle чуток было - заценил. Некоторых вещей из древних Oracle 8 или 9 до сих пор нет в MS SQL.
Твой вариант попробую как-нибудь. Сейчас у меня вместо join курсоры, но только там где 3 -4 раза одна таблица джоиниться. Где -2 пока так и осталось. Лень, там многабукав. Хотел новичку дать поупражняться, да он сбег. Платят маловато...
Поделиться82016-11-16 19:43:35
#p11750,Dev написал(а):
В MS, как обычно: "мы подумали за вас и решили что так лучше!"
Я, когда писал join, решил, что MS сделает стандартную задачу лучше тупого перебора курсором. Т.е. я думал, что при пересечении одной и той же таблицы SQL и не подумает тащить ее 4 раза подряд, а протащит один раз и сделает ну как минимум прямой перебор. План запроса не смотрел, сразу переписал, т.к. процедура тормознулась примерно с 5 минут до 15 только из-за одного условия с таким запросом.
Отредактировано privet (2016-11-16 19:43:45)
Поделиться92016-11-16 19:50:35
- Администратор
- Зарегистрирован: 2016-10-17
- Сообщений: 1710
- Уважение: [+171/-8]
- Пол: Мужской
#p11811,privet написал(а):
Я, когда писал join, решил, что MS сделает стандартную задачу лучше тупого перебора курсором. Т.е. я думал, что при пересечении одной и той же таблицы SQL и не подумает тащить ее 4 раза подряд, а протащит один раз и сделает ну как минимум прямой перебор. План запроса не смотрел, сразу переписал, т.к. процедура тормознулась примерно с 5 минут до 15 только из-за одного условия с таким запросом.
ДА, он бы это бы сделал, если бы таблица была бы локальной!!!!
А у тебя как я понял, она удаленная (через линкед сервер) работает. MS не знает не статистику не индексы не другие параметры для построения плана запроса... Тупой он. Поэтому выбирает стратегию выкачки сколько джойнов столько и выкачиваю!
Поделиться102016-11-16 20:06:40
- Администратор
- Зарегистрирован: 2016-10-17
- Сообщений: 1710
- Уважение: [+171/-8]
- Пол: Мужской
а у тебя ключ (ПК) есть в TagValues ???
Поделиться112016-11-16 22:50:19
#p11814,Dev написал(а):
ДА, он бы это бы сделал, если бы таблица была бы локальной!!!!
А у тебя как я понял, она удаленная (через линкед сервер) работает. MS не знает не статистику не индексы не другие параметры для построения плана запроса... Тупой он. Поэтому выбирает стратегию выкачки сколько джойнов столько и выкачиваю!
Понятно. Буду знать. Не приходилось через linked большие объемы таскать. Получаеться, что раз мы механизм получения этих значений все-равно переносим на тот же сервак, то проц-ка ускориться сама по себе. Да еще хранить все эти миллионы не будем, ограничим периодом небольшим.
#p11820,Dev написал(а):
а у тебя ключ (ПК) есть в TagValues ???
Да, конечно, кажись Id автоинкрементный.
Поделиться122016-12-05 17:22:14
Продолжение истории с этой процедуриной 
Пришлось еще до переноса всего механизма на один сервер по-быстрому оптимизировать эту usp. Для начала я уменьшил выборку данных через linked-server насколько смог - с 54млн до 7млн - ускорение всего на 1-2 мин (было 6-8 - стало около 5). Ожидал большего. Тестированием понял, что тормоза больше не в тех выборках, что я приводил, а в расчете средних значений за день/месяц и на выборке и вставке в итоговую таблицу (там тоже уже больше 4млн записей).
В итоге проиндексировали таблицу-приемник TagValues (дата и tag), и вся usp стала выполняться за 20-30 сек. Что уже меня устраивает на данный момент.
Поделиться132016-12-06 12:45:24
#p11745,Dev написал(а):
1. зачем тебе вообще джойны? У тебя одна таблица и связи идут по одному полю [Date], тем более оно в SARG?
Да, кстати, с grop by, как ты написал, есть нюанс. Для некоторых параметров мне пришлось исправлять v1.[Date]=v2.[Date] на Date Between... По логике дата-время у параметров в формулах должны совпадать, но исходные данные для всей этой штуки берутся с физических приборов и время может немного отличатся. Поэтому на grop by смысла переписывать нет.
Страница: 1
