Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
SL8Behemoth
Member

Откуда:
Сообщений: 16
Имеем: программный продукт SyteLine 8.01. У клиента стоит тонкий клиент на основе MonGoose/WinStudio, сервер приложения не умеет ничего, кроме пересылки вызовом к sql-серверу и обратно. Дизайн форм и их vb-скрипты тоже лежат в одной из баз в ms sql. В остальных базах лежат учётные данные и настройки предприятий, работающих в данной erp-системе (это группа компаний: по одному юрлицу на каждую БД). Соответственно, сильно развита репликация справочников между БД.
Основные мозги всех обработок пользовательских действий, естественно, не в скриптах vb (там в основном защиты от дурака и юзерфрендлинесс), а в хранимках сервера. В отличие от 1С, указанная система в принципе не знает понятия "расчетные остатки за период", поэтому огромное количество процедур используют агрегатные функции к большим массивам данных (сотни тысяч строк по нескольку раз в одной транзакции). Много работы с обработкой внешних XLS файлов, анализом строковых переменных, отлавливанием последних по дате создания/изменения записей в заданной таблице. Много чистого чтения сразу нескольких таблиц, соответственно, блокировки происходят сплошь и рядом. Где можем рисковать, меняем на read uncommitted.
Группа программистов, обслуживающих данный программный продукт, с mssql позже версии 2008r2 и не работали.
Документацию по 2014 и 2016 изучил, но не могу понять, стоит ли овчинка выделки. Какие подводные камни (скажем, новые ключевые слова пересекаются с именами курсоров и полей), насколько ощутимый эффект в реальной жизни даёт блокировка на уровне отдельных записей, какие инструменты использовать для миграции?
7 авг 16, 11:53    [19511621]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
aleks2
Guest
SL8Behemoth
Где можем рисковать, меняем на read uncommitted.

С вами усе ясно.

Как фсегда, наибольший эффект дают архитектурные усовершенствования базы.
Кнопку "Быстро сделать как я хачу" в 2016 все еще не сделали.
7 авг 16, 12:36    [19511701]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
Проблемы, обусловленные плохим проектирование и реализацией БД не лечатся волшебным образом, путем миграции на СУБД более поздней версии.

Вам нужно:
- Если позволяют аппаратные и прочие ресурсы, включить у БД режим read_committed_snapshot_isolation. Соответственно, read uncommitted поубирать.
- Найти и нанять адекватного специалиста (возможно нескольких), который умеет решать проблемы типа:
автор
поэтому огромное количество процедур используют агрегатные функции к большим массивам данных (сотни тысяч строк по нескольку раз в одной транзакции).
7 авг 16, 12:36    [19511702]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
SL8Behemoth
Member

Откуда:
Сообщений: 16
invm, интересно, и какое Вы видите решение проблемы? Вот нужна мне (по бизнесу) следующая величина:

select ic.item, ic.whse, ic.loc, sum(m.qty) from matltran m join itemloc ic on ic.item = m.item and ic.whse = m.whse and ic.loc = m.loc where m.trans_type not in ('C','N')

И чего делать? Триггеры на matltran вешать, которые будут в таблицу итогов писать? По расписанию процедуру формирования итогов вызывать? Регламент закрытия периода вводить?
7 авг 16, 14:13    [19511948]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
SL8Behemoth
Member

Откуда:
Сообщений: 16
понял, что слишком простой пример дал, там остаток на сегодняшний день, он и ТПК в системе есть.. добавим : and m.trans_date < @cd

А вообще, я не понимаю, что за отвращение в адрес read uncommitted. Риска минимум,а быстродействие очевидно. Чай, мы не банк.
7 авг 16, 14:20    [19511967]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
SL8Behemoth
invm, интересно, и какое Вы видите решение проблемы? Вот нужна мне (по бизнесу) следующая величина:

select ic.item, ic.whse, ic.loc, sum(m.qty) from matltran m join itemloc ic on ic.item = m.item and ic.whse = m.whse and ic.loc = m.loc where m.trans_type not in ('C','N')

И чего делать? Триггеры на matltran вешать, которые будут в таблицу итогов писать? По расписанию процедуру формирования итогов вызывать? Регламент закрытия периода вводить?


Как минимум у Вас Gtoup by пропущен.

Если Вы так остатки на текущий момент собираете, то дальше ( больше строк) дольше будет делаться и версия MS SQL Вам не поможет.
Тут скорее всего железом или хотя бы убирать maxdop в 2 или даже 1.

Скриптами посмотрите каких индексов не хватает.
Логику запросов можно и пересмотреть.

Если в старые периоды не пишите, то и закрытие периода можно вводить ( от версии MS SQL так же не зависит)
7 авг 16, 14:41    [19512037]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
SL8Behemoth
И чего делать?
Триггеры или индексированные представления, если уж постоянное агрегирование вам такие проблемы создает.
SL8Behemoth
А вообще, я не понимаю, что за отвращение в адрес read uncommitted. Риска минимум, а быстродействие очевидно. Чай, мы не банк.
Изучайте - http://sqlblog.com/blogs/tamarick_hill/archive/2013/05/06/pros-cons-of-using-read-uncommitted-and-nolock.aspx
Для разрешения конфликтов читатель-писатель вообще без рисков придумали уровень изоляции транзакций read_committed_snapshot_isolation.
Странно, что изучив документацию и на 2014-й, и на 2016-й, вы этого не знаете.
7 авг 16, 15:40    [19512215]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
SL8Behemoth
Member

Откуда:
Сообщений: 16
invm,

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

Что же до кинутой Вами ссылки, то там банальный ликбез по поводу возможных последствий грязного чтения и... как раз приводятся в пример банки! Так я как раз говорю, что риск грязного чтения для нас приемлемый, проблема не в этом.
7 авг 16, 17:47    [19512520]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
SL8Behemoth
Так я как раз говорю, что риск грязного чтения для нас приемлемый, проблема не в этом.
Риск грязного чтения не только грязные данные. Можете, например, еще и ошибку 601 получить.
Или существенную деградацию производительности - https://www.sql.ru/forum/1126333-a/with-nolock-bez-nego-znachitelno-bystree-stranno
7 авг 16, 17:56    [19512548]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
SL8Behemoth
Member

Откуда:
Сообщений: 16
invm, и вот там как раз написано в сообщении WITH(NOLOCK) - без него значительно быстрее. Странно. что этот феномен наблюдается безусловно только на 2008, а на более поздних версиях - только при определённых настройках. Вот и мотив переходить на 2014)))
7 авг 16, 18:35    [19512665]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
iljy
Member

Откуда:
Сообщений: 8711
SL8Behemoth,

как же надоела эта тема с грязными чтениями. Уже вроде всем по сто раз объяснено, что это last resort для затыкания дыр после ленивого проектировщика, да и то хреновый, а нет, регулярно откуда-то новые "мы не банки" берутся. Вы данные рассогласованные получаете, причем степень этой рассогласованности на сложной нагруженной системе может быть любой. Висячие внешние ключи из-за отката транзакции, дублирующие либо пропущенные ветки в древовидных структурах и прочие радости - подумаешь, фигня какая. И ведь эффект-то по производительности в 99% нулевой, а для решения конфликтов еще в эпоху мастодонтов снапшот придумали, но ОБС, что нолок - это кул, а в интерфейсе можно будет делать на весь экран одну кнопку "зделай мине п%$!&то вот прям ща".

А мотивом переходить на 14 должны быть новые аналитические функции, улучшение функциональности OVER, column store и т.д. Ну или на крайняк существенное обновление версии оси, на которую 2008 уже без бубна и не встанет.
7 авг 16, 23:05    [19513639]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
SL8Behemoth
Member

Откуда:
Сообщений: 16
iljy, убедили.
Но вопрос действительно был не про nolock, а про мотивацию миграции на 2014. Про тот же column store - ну знаю я, что он есть, но каковы практические области его эффективного использования, какие конкретно алгоритмы/виды запросов будут работать быстрее при его применении? Вот что хотелось бы обсудить.
7 авг 16, 23:25    [19513700]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33262
Блог
SL8Behemoth,

Данные будут меньше места занимать примерно в 10 раз, примерно такое же ускорение будет в запросах, которые будут работать в batch-режиме.

У нас в некоторых случаях был двукратный рост производительности от простой смены версии 2008R2 на 2014, это еще без использования новых возможностей (dwh, код с массовым использованием временных таблиц).
7 авг 16, 23:54    [19513750]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
SL8Behemoth
Member

Откуда:
Сообщений: 16
Критик, а с чего именно данные станут занимать места в 10 раз (???) меньше БЕЗ ущерба для производительности? Я знаю, что SQL 2014 более оптимален в использовании дискового пространства, но если использовать всю его начинку в этом направлении, то по сути это архивация данных, как она может не ударить по скорости чтения?

И почему массовое использование временных таблиц вы считаете возможностью 2014? Это и в 2008R2 прекрасно можно было делать.
8 авг 16, 06:13    [19513993]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 199
SL8Behemoth
Критик, а с чего именно данные станут занимать места в 10 раз (???) меньше БЕЗ ущерба для производительности?

В Columnstore другие алгоритмы сжатия.
И производительность вырастет скорее на аналитических отчетах, чем на точечных выборках
8 авг 16, 07:25    [19514025]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33262
Блог
SL8Behemoth,

в скобках - это про мою систему
8 авг 16, 10:31    [19514500]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
SL8Behemoth
Member

Откуда:
Сообщений: 16
Критик, а подводные камни миграции какие?
8 авг 16, 17:31    [19516982]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
o-o
Guest
а такие камни, что в 2014-ом новый Cardinality Estimator.
и если у Критика вдруг без малейшего телодвижения где-то возросла производительность,
то разумеется это не "данные стали места меньше занимать"
(сами данные без каких-то команд ни в какие колумн-сторы не реорганизуются)
это значит, что например для его запросов с OR и AND оценки стали точнее.
а для каких-то запросов как раз они станут хуже.
и вот тогда ваши хорошие планы полетят,
а будут построены кривые и будете вы хинтами прописывать каждому такому запросy,
чтобы при его оптимизации старый Cardinality Estimator использовался
8 авг 16, 17:43    [19517028]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
SL8Behemoth
Member

Откуда:
Сообщений: 16
Такая же проблема была при переходе с 2000 на это 2008. Я не понимаю, почему за восемь лет нельзя было написать нормальный оптимизатор запросов.
9 авг 16, 05:07    [19518450]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
aleks2
Guest
SL8Behemoth
Такая же проблема была при переходе с 2000 на это 2008. Я не понимаю, почему за восемь лет нельзя было написать нормальный оптимизатор запросов.

Взял бы и... написал.

ЗЫ. Все оптимизаторы "нормальные", это писатели с кривым мозгом.
9 авг 16, 06:02    [19518472]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
SL8Behemoth
Member

Откуда:
Сообщений: 16
aleks2, если запрос без хинтов (!) работал с приемлемой производительностью на 2008 сервере, то почему его следует считать кривым лишь потому, что новая версия движка БД работает с ним уже неэффективно?

Или критерием кривизны для Вас является как раз воспроизводимость перформанса под каждым следующим релизом MS SQL Server?
9 авг 16, 19:53    [19521836]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
SL8Behemoth
Member

Откуда:
Сообщений: 16
Отписываюсь по результатам: в начале апреля перевели ERP-систему на MS SQL Server 2016 SP1 (c 2008 SP 2). Полезные уроки:
1) Поскольку теперь временные таблицы имеют отрицательный object_id, то пришлось переписать все алгоритмы типа if object_id('tempdb..#h') > 0 drop table #h на drop table if exists #h . Иначе возникали ошибки при повторном вызове одних и тех же хранимых процедур из одной и той же сессии.
2) Полезной находкой оказались STRING_SPLIT (у нас была написана своя функция, но понятное дело, что написанное в ядре куда более эффективно), FETCH NEXT ... ROWS ONLY, DATEFROMPARTS
3) Даже до применения COLUMNSTORE индексов на ряде brute force запросов, используемых в отчётах, эффективность была на 10% выше.
4) К сожалению, темпорально-историческими таблицами пока не можем воспользоваться, ибо ERP-система не переваривает тип данных datetime2
9 май 17, 17:08    [20466718]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5162
SL8Behemoth
пришлось переписать все алгоритмы типа if object_id('tempdb..#h') > 0 drop table #h на drop table if exists #h .

достаточно было сделать вот так :
if object_id('tempdb..#h') is not null
9 май 17, 19:53    [20467067]     Ответить | Цитировать Сообщить модератору
 Re: Перевод БД местечковой ERP системы с MS SQL Server 2008R2 на SQL Server 2014  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30786
SL8Behemoth
пришлось переписать все алгоритмы типа if object_id('tempdb..#h') > 0
Ну, это у вас бага в коде была, ведь если объекта не существует, то object_id вернёт не меньше либо равно 0, а NULL, о чём и указано в документации, так что нужно было и тогда, и сейчас использовать if object_id('tempdb..#h') IS NOT NULL

А в остальном, как я понял, всё прошло гладко?

SL8Behemoth
2) Полезной находкой оказались STRING_SPLIT (у нас была написана своя функция, но понятное дело, что написанное в ядре куда более эффективно), FETCH NEXT ... ROWS ONLY, DATEFROMPARTS
3) Даже до применения COLUMNSTORE индексов на ряде brute force запросов, используемых в отчётах, эффективность была на 10% выше.
4) К сожалению, темпорально-историческими таблицами пока не можем воспользоваться, ибо ERP-система не переваривает тип данных datetime2
В переходе на новую версию главное то, что она продолжает поддерживаться. Всё таки 2008R2 уже старовата.
А новые фичи как то постепенно будете использовать...
9 май 17, 20:49    [20467210]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить