У Вас отключён javascript.
В данном режиме, отображение ресурса
браузером не поддерживается

Политический форум России.

Объявление

Внимание!
Мы на новом форуме.

https://www.polituserforum.ru/

Информация о пользователе

Привет, Гость! Войдите или зарегистрируйтесь.



SQL

Сообщений 1 страница 13 из 13

1

Давай тут ...
Может кому-то тоже интересно будет!

0

2

Задача такая: есть таблица: тэги, значения и время, обновляется раз в 20 мин. Надо из нее брать раз в 20 мин значения, кидать в другую базу. Это элементарно, их штук 500. Но, кроме этого есть расчетные тэги, значения которых надо считать по разным простым и не очень простым формулам в зависимости от значений обычных тэгов. Таких больше сотни. Все наименования тэгов хранятся еще в одной базе справочников, у них есть признак - расчетный и не расчетный. + еще ко всему нужно налету считать всякие агрегатные функции - средние, макс. отклонения и т.п.
Сделано сейчас так:
берется из справочника курсор, по курсору бежим
- если тэг не расчетный - тупо записываем и считаем всякие функции.
- если расчетный - в зависимости от наименования тэга у каждого своя формула (многие похожие, но есть куча оригинальных)

Итого получилась самая большая развилка которую я когда либо делал. http://forumstatic.ru/files/0017/d4/9a/43635.gif  Т.к. гуглил - в t-sql нет обычного case, есть только внутри запросов. Его можно применить по-хитрому через
SET @STATEMENT=CASE @Tag
    WHEN 'FI2303/FI2301' THEN ...
    ...
    EXEC( @STATEMENT)
Но это не работает со сложными запросами внутри выражения. В общем, как это по-быстрому оптимизировать кроме GOTO на конец развилки в конце каждого IF - я не знаю. Но это как-то тупо и меня еще в школе отучили GOTO использовать  http://forumstatic.ru/files/0017/d4/9a/82722.gif

Отредактировано privet (2016-11-16 16:35:04)

0

3

Кроме этого еще пару интересных моментов обнаружил. Если ты хорошо знаешь работу планировщика, может сможешь пояснить.
Если писать 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 сервер  http://forumstatic.ru/files/0017/d4/9a/82722.gif
Но эту часть мы переделываем, и будет один сервер, и индекс по наименованию тэга сделаем.

0

4

СУБД MS SQL?

0

5

#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

http://forumstatic.ru/files/0017/d4/9a/12493.gif

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 пока не вникал

0

6

#p11726,privet написал(а):

Да, дело осложняется тем, что вьюха v_TagValuesFromAssb тащит данные из таблицы с 52 млн записей (и она растет) через linked сервер  
Но эту часть мы переделываем, и будет один сервер, и индекс по наименованию тэга сделаем.

Дело в том, что MS SQL не может нормально работать с прилинкованным сервером...
Почему у тебя тормоза. Он тянет таблицу 4 раза... Одну и туже 4 раза по 52 млн строк.... Это очень плохо!
В курсоре он читает ее 1 раз. поэтому и быстрей. Но курсор можно переделать под один запрос....
или схитрить с времянкой.
Я вечером тебе все распишу...

Вот SAP ASE, Бывший Sybase, они с MS на одной платформе были рождены, имеет такую чтуку как CIS. Так вот она посылает на удаленный сервер уже оптимизированный запрос. Там таких косяков не бывает. За это я и люблю Sybase. В MS, как обычно: "мы подумали за вас и решили что так лучше!"

0

7

#p11732,Dev написал(а):

СУБД MS SQL?

Да.

#p11750,Dev написал(а):

Вот SAP ASE, Бывший Sybase, они с MS на одной платформе были рождены, имеет такую чтуку как CIS. Так вот она посылает на удаленный сервер уже оптимизированный запрос. Там таких косяков не бывает. За это я и люблю Sybase. В MS, как обычно: "мы подумали за вас и решили что так лучше!"

Это точно.
С Sybase не работал, но с Oracle чуток было - заценил. Некоторых вещей из древних Oracle 8 или 9 до сих пор нет в MS SQL.

Твой вариант попробую как-нибудь. Сейчас у меня вместо join курсоры, но только там где 3 -4 раза одна таблица джоиниться. Где -2 пока так и осталось. Лень, там многабукав. Хотел новичку дать поупражняться, да он сбег. Платят маловато...

0

8

#p11750,Dev написал(а):

В MS, как обычно: "мы подумали за вас и решили что так лучше!"

Я, когда писал join, решил, что MS сделает стандартную задачу лучше тупого перебора курсором. Т.е. я думал, что при пересечении одной и той же таблицы SQL и не подумает тащить ее 4 раза подряд, а протащит один раз и сделает ну как минимум прямой перебор. План запроса не смотрел, сразу переписал, т.к. процедура тормознулась примерно с 5 минут до 15 только из-за одного условия с таким запросом.

Отредактировано privet (2016-11-16 19:43:45)

0

9

#p11811,privet написал(а):

Я, когда писал join, решил, что MS сделает стандартную задачу лучше тупого перебора курсором. Т.е. я думал, что при пересечении одной и той же таблицы SQL и не подумает тащить ее 4 раза подряд, а протащит один раз и сделает ну как минимум прямой перебор. План запроса не смотрел, сразу переписал, т.к. процедура тормознулась примерно с 5 минут до 15 только из-за одного условия с таким запросом.

ДА, он бы это бы сделал, если бы таблица была бы локальной!!!!
А у тебя как я понял, она удаленная (через линкед сервер) работает. MS не знает не статистику не индексы не другие параметры для построения плана запроса... Тупой он. Поэтому выбирает стратегию выкачки сколько джойнов столько и выкачиваю!

0

10

а у тебя ключ (ПК) есть в TagValues ???

0

11

#p11814,Dev написал(а):

ДА, он бы это бы сделал, если бы таблица была бы локальной!!!!
А у тебя как я понял, она удаленная (через линкед сервер) работает. MS не знает не статистику не индексы не другие параметры для построения плана запроса... Тупой он. Поэтому выбирает стратегию выкачки сколько джойнов столько и выкачиваю!

Понятно. Буду знать. Не приходилось через linked большие объемы таскать. Получаеться, что раз мы механизм получения этих значений все-равно переносим на тот же сервак, то проц-ка ускориться сама по себе. Да еще хранить все эти миллионы не будем, ограничим периодом небольшим.

#p11820,Dev написал(а):

а у тебя ключ (ПК) есть в TagValues ???

Да, конечно, кажись Id автоинкрементный.

0

12

Продолжение истории с этой процедуриной  http://forumstatic.ru/files/0017/d4/9a/43483.gif
Пришлось еще до переноса всего механизма на один сервер по-быстрому оптимизировать эту usp. Для начала я уменьшил выборку данных через linked-server насколько смог - с 54млн до 7млн - ускорение всего на 1-2 мин (было 6-8 - стало около 5). Ожидал большего. Тестированием понял, что тормоза больше не в тех выборках, что я приводил, а в расчете средних значений за день/месяц и на выборке и вставке в итоговую таблицу (там тоже уже больше 4млн записей).
В итоге проиндексировали таблицу-приемник TagValues (дата и tag), и вся usp стала выполняться за 20-30 сек. Что уже меня устраивает на данный момент.

0

13

#p11745,Dev написал(а):

1. зачем тебе вообще джойны? У тебя одна таблица и связи идут по одному полю [Date], тем более оно в SARG?

Да, кстати, с grop by, как ты написал, есть нюанс. Для некоторых параметров мне пришлось исправлять v1.[Date]=v2.[Date] на Date Between... По логике дата-время у параметров в формулах должны совпадать, но исходные данные для всей этой штуки берутся с физических приборов и время может немного отличатся. Поэтому на grop by смысла переписывать нет.

0