1С:Предприятие для SQL - надежность, быстродействие, аналитика

1С:Предприятие для SQL - надежность, быстродействие, аналитика

Microsoft SQL Server 2000 - это самая быстрая и
надежная база данных в мире по
тестам Transaction Processing Council на 01.08.2001.

Катастрофа, вот после чего многие задумываются о переходе на 1С:Предприятие для SQL:

У вас в офисе на мгновение выключился свет, пользователи перезагрузили компьютеры и попробовали снова запустить 1С: Предприятие, но: тут и стало понятно, что данные безвозвратно утеряны. Источник резервного питания на сервере не спас. Ваш системный администратор грамотный специалист и восстановил данные из резервной копии, сделанной вчера, ваши продавцы перестали продавать и лихорадочно вносят потерянную информацию. Благодаря администратору ущерб минимизирован, но все равно велик.

В тоже время у вашего конкурента работает 1С:Предприятие для SQL. Когда вы поинтересовались, что ему дал "SQL" вы выяснили:

• Надежная защита данных от сбоев и поломок компьютеров. Катастрофы подобные вашей ему не ведомы.

• Защита от похищения коммерческой информации

• Все быстрее работает. Быстрее обслуживаются клиенты, быстрее получаются отчеты, поэтому быстрее получаются правильные решения

• Более совершенные и быстрые аналитические отчеты. Кроме того, можно использовать сводные таблицы Excel для быстрого и легкого получения нужной статистики.

1С:Предприятие для SQL. Как повысить надежность, быстродействие и улучшить отчеты?

Хотя 1С:Предприятие для Microsoft SQL Server активно внедряется уже несколько лет, у многих потребителей до сих пор нет четкого представления о новых возможностях данного продукта по сравнению с локальной и сетевой версией.
Это обусловлено тем, что многие продавцы 1С не специализируются на продуктах Microsoft и поэтому не имеют в штате сертифицированных специалистов по Microsoft SQL Server. Как следствие потребители часто не получают адекватную информацию и необходимые услуги по продуктам 1С для SQL, во многих случаях ожидания сменялись разочарованием.
Данная статья дает обзор преимуществ и недостатков 1С Предприятия для SQL и описывает необходимые услуги, без которых покупка данного продукта пустая трата денег.

1С:Предприятие для SQL имеет следующие основные преимущества перед локальной и сетевой версией:

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

Рассмотрим данные аспекты по порядку.

Надежность

Не смотря на все усилия разработчиков программного и аппаратного обеспечения, аварии в работе компьютерных систем происходят достаточно часто. Обычно средняя по тяжести авария парализует компанию на целый день, часто суммарный убыток далеко перекрывает стоимость программного обеспечения и компьютеров. Вот почему руководителей компаний так беспокоит надежность информационных систем.

Пожалуй, надежность это один из основных факторов, из-за которого следует остановить свой выбор на 1С:Предприятии для SQL. В отличие от остальных возможностей версии для SQL повышенная надежность достигается сравнительно небольшим набором услуг, которые часто оказывают бесплатно при покупке программы. Что стоит за повышенной надежностью SQL Server? Рассмотрим по порядку.

Защита целостности данных при авариях оборудования и ошибках программного обеспечения.
Представьте себе, что в 1С:Торговле оформляется накладная. Система создала проводки и приступила к пересчету остатков на складе. Однако сразу после создания проводок и еще до начала пересчета остатков произошел сбой, например, отключилось электричество или произошла фатальная ошибка в Windows 95.
Подобные аварии на рабочих местах пользователей случаются примерно раз в 1 месяц. Произойдет ли разрушение данных в данном случае? Техническая поддержка компании 1С отвечает на это так: "Разрушение базы данных в этом случае может произойти, если это не SQL-версия".
Как видим, в локальной и сетевой версии 1С такая ситуация приведет к разрушению целостности базы данных и скорее всего вам придется потерять работу компании за день или больше и восстановить данные из резервной копии.
В версии для SQL в такой ситуации сработает система поддержки целостности данных. Microsoft SQL Server автоматически отменит незавершенную до конца операцию (транзакцию). Возможно, вы просто перезагрузите свой компьютер и продолжите работу дальше без проблем.

Создание резервных копий данных во время работы (например, через каждые 30 минут).
Защита целостности данных позволяет эффективно справляться с авариями на рабочих местах пользователей, однако это не предохраняет от тяжелых аварий самого сервера, где хранятся данные. К сожалению, это не редкость, из-за высокой нагрузки на сервер обычно такая авария происходит примерно раз в 1 год, а иногда и чаще.
В случае подобной аварии данные восстанавливают из резервных копий, однако сетевая версия 1С:Предприятия не позволяет создавать такие копии во время работы пользователей. В результате серьезная авария на сервере всегда приводит к потере минимум одного дня работы фирмы.
MS SQL позволяет создавать резервные копии во время работы и без серьезного замедления, обычно их делают через каждые 30 минут. Таким образом, даже в случае серьезной аварии сервера вы потеряете работу только за полчаса, и часто через час ваша компания снова может работать в нормальном режиме.

Защита от несанкционированного доступа.
Следует сказать, что версия 1С для SQL позволяет обеспечить защиту данных от несанкционированного чтения и модификации.
Сетевая и локальная версия 1C не защищена от взлома, поэтому не только "хакер", но даже опытный пользователь сможет просмотреть любые данные прямо в Excel.
Кроме просмотра, пользователи случайно или преднамеренно могут повредить данные 1С. Подобных проблем безопасности 1С для SQL не имеет.

Мы проводили испытания 1С:Предприятия для SQL, имитируя различные аварии и нештатные ситуации, MS SQL Server их с честью прошел.
Чтобы добиться желаемой надежности требуется только, чтобы вам правильно установили и настроили Microsoft SQL Server (обычно это несколько часов работы квалифицированного профессионала).

Быстродействие

Медленная работа программ, часто служит причиной замедления обслуживания клиентов фирмы или не позволяет своевременно получить нужный отчет для принятия управленческого решения. Если у конкурента программа работает быстрее, он имеет преимущество перед вами на рынке.

1С:Предприятие для SQL при 15 пользователях работает быстрее сетевой версии на таком же оборудовании примерно в 2 раза.
Мы проверяли работу 1С:Торговли для 15 пользователей (использовался двухпроцессорный сервер с 512 мегабайтами памяти).
Расходная накладная в сетевой версии записывалась за 10 сек, а в версии для SQL за 5 сек. Если вы закажете услуги по созданию специальной конфигурации для своих нужд, то версия для SQL будет работать примерно до 10 раз быстрее сетевой. Например, наша SQL-версия отчета "Остатки на складе" работает в 4 раза быстрее стандартного отчета.

Однако очень часто потребитель обнаруживает после покупки 1С:Предприятия для SQL, что система не только не работает быстрее, а работает в 3-4 раза медленнее сетевой версии. Виной тому как правило, недобросовестный продавец, который не обЪяснил покупателю особенностей продукта и не оказал необходимых услуг. Рассмотрим, почему достигается или не достигается увеличение быстродействия.

В отличие от файлового сервера, используемого в сетевой версии, SQL Server, гораздо более мощная программа, которая не только хранит и передает данные, но и поддерживает их целостность, а также может их обрабатывать. SQL Server способен производить вычисления над данными не перемещая их по локальной сети, например, складские остатки в 1С:Торговле вычисляются непосредственно на MS SQL. Как следствие, MS SQL работает быстрее сервера сетевой версии, т.к. меньше загружает компьютеры пользователей и локальную сеть. Отсюда вывод - для SQL-версии требуется более мощный сервер и допустимы более слабые компьютеры пользователей, чем в сетевой версии. Обычно один дорогой компьютер для сервера купить дешевле, чем несколько дорогих компьютеров для всех пользователей, поэтому суммарная стоимость оборудования для SQL-версии будет ниже, чем для сетевой. Однако необходимость покупки или модернизации сервера часто скрывается недобросовестными продавцами, чтобы не спугнуть покупателя. Кроме того, часто продавец не является специалистом в аппаратном обеспечении и не может подобрать клиенту оптимальный по стоимости и производительности сервер.

Помимо подбора сервера следует учесть, кем и как будет производиться настройка 1С:Предприятия под ваши нужды. Если настройку будут делать специалисты опытные в MS SQL, которые используют его возможности (прямые SQL-запросы, хранимые процедуры и т.д.), то, скорее всего, ваша конфигурация будет работать примерно в 2 раза быстрее, чем стандартная. Однако если настройку будет производить специалист не опытный в MS SQL, то, скорее всего, скорость работы программы после настройки значительно упадет - скажется незнание особенностей программирования MS SQL Server.

Какие следует сделать выводы?

1) При покупке 1С:Предприятия для SQL, обсудите с продавцом, какой сервер необходим, а еще лучше купите у одного продавца сразу и программу, и компьютер. Такой сервис представляют компании -системные интеграторы. Купить и программу и компьютер у одной компании выгоднее потому, что вы не получите ситуацию, когда у вас все работает ужасно медленно, а две разные фирмы обвиняют друг друга и ничего не делают чтобы решить проблему. Кроме того, системные интеграторы часто дают большие скидки при комплексных поставках.

2) Покупайте 1С для SQL только у компании, которая имеет квалифицированных специалистов в MS SQL, чтобы они могли вам качественно все настроить. Квалификация специалистов фирмы обычно подтверждается партнерским статусом компании с Microsoft.

3) Если будете изготавливать дополнительные отчеты, заказывайте их в SQL-вариантах, которые работают значительно быстрее.

Отчетность нового уровня

Нередко именно своевременные и полные отчеты, ориентированные на специфику компании, позволяют вовремя принять верное управленческое решение и успешно выдерживать конкуренцию.

В версии 1С для SQL потребителю становятся доступны за меньшую цену более сложные заказные отчеты, чем в сетевой версии. Кроме того, отчеты сделанные с использованием языка SQL, как правило, работают быстрее от 3 до 100 раз, чем их аналоги, выполненные без SQL. Рассмотрим данный вопрос подробнее.

Обычно программирование заказного отчета делится на 2 стадии. Это выборка необходимых данных и создание печатной формы отчета.
С помощью языка SQL значительно облегчается выборка данных. Если без применения SQL построение алгоритма выборки данных для сложного отчета займет несколько дней, то квалифицированный специалист, владеющий SQL, может сделать выборку данных фактически любой сложности в пределах 1 дня.
Поэтому хотя час специалиста владеющего SQL стоит дороже специалиста, который его не знает, отчеты на SQL делаются быстрее и потому дешевле стоят.
На сетевой версии многие отчеты недоступны пользователям из-за высокой цены и длительности разработки, а также из-за очень медленного построения. Также следует отметить, что благодаря компактной записи запросов к данным на SQL, отчеты на SQL содержат меньше ошибок и легче модифицируются.

Кроме дешевых и эффективных отчетов в 1С для SQL потребителю становится доступным профессиональное средство анализа данных через Excel, так называемый сервис OLAP (онлайновая аналитическая обработка), который входит в состав MS SQL Server.
Пользователь без привлечения программиста может анализировать данные в выбранных им разрезах. Чтобы пользователь мог это сделать, программисту требуется только один раз настроить OLAP.

Сделаем вывод.

Покупайте 1С для SQL только у компании, которая имеет квалифицированных специалистов в MS SQL, которые могли бы вам создать качественную отчетность. Квалификация специалистов фирмы обычно подтверждается статусом Microsoft Certified Partner.

Когда следует приобретать версию 1С для SQL?

Главный критерий в выборе 1С для SQL - это экономическая обоснованность. Рассмотрим типовые случаи.

Вариант 1: до 5 пользователей.
В данном случае версия для SQL будет стоить примерно в 2-3 раза дороже сетевой версии (около $1000). Делая выбор следует задать себе вопрос: минимум 1 день простоя компании в году в результате аварии будет стоить нам дороже или нет? Кроме этого следует задать себе вопрос: нужна ли мне сложная отчетность и окупится ли она?

Большинство небольших компаний сделают выбор в пользу сетевой версии, однако компания - дистрибьютор с 5 менеджерами по продажам, которые за год продают на 1 миллион долларов скорее всего сделает выбор в пользу SQL.

Вариант 2: до 15 пользователей.
Вопросы, которые следует себе задать, примерно те же самые. Однако следует учесть, что для данного варианта разница в цене сетевой версии и версии для SQL составит примерно 1,5-2 раза. Для данного варианта выбор версии для SQL целесообразен примерно для 40% компаний.
В качестве дополнительного критерия можно указать, что если ваша база данных превышает 200 мегабайт, то выбор обычно делается в пользу SQL.

Вариант 3: свыше 15 пользователей.
В качестве ответа можно привести цитату из документации 1С: "Если число пользователей больше 15, то как правило используют версию для SQL". Обычно от 15 пользователей очень критичными становятся такие возможности MS SQL Server как: повышенная надежность и быстродействие.

Конечно приведенные рекомендации ориентировочные. Количество факторов влияющих на выбор версии велик, обращайтесь за консультациями к компаниям - партнерам 1С.

Надежность

Мы проанализировали запросы, посылаемые 1С:Предприятием на SQL сервер.
Порадовало, что грамотно оформлено начало и конец транзакций. Например, отгрузка по накладной оформлена в виде одной большой транзакции, что гарантирует целостность всей операции. Очень хорошо, что специалисты 1С завершают транзакцию не просто как commit, а еще смотрят на счетчик транзакций @@trancount. При имитации сбоя (физический разрыв сети и выключение компьютера при обработке данных) транзакция благополучно "откатывалась".

Быстродействие

Поскольку специалисты 1С пока не разрабатывают конфигурацию специально для SQL, и делают основной упор на эффективный транслятор макроязыка, в Transact SQL выявлено две серьезные проблемы.

1) Чрезвычайно большое количество команд на сервер (проведение накладной состоит из 340 команд).

2) Интенсивное использование курсоров и циклов, в том числе в хранимых процедурах

Большое количество команд накладывает серьезное ограничение на быстродействие 1С. Дело в том, что сервер MS SQL способен обработать примерно до 100-200 запросов в секунду, не зависимо от их содержания и мощности сервера (ограничение парсера), т.е. следует делать запросы более сложными и сокращать их число. В тоже время MS SQL способен запускать от 200 до 5000 хранимых процедур в секунду по протоколу DTS как RPC-пакет.

Таким образом, расходная накладная в первых версиях 1С:Предприятии не могла проводиться быстрее 3 секунд, фактически не зависимо от мощности сервера. Однако в 15-й версии 1С:Предприятия 7.7 уже все маленькие запросы убраны в хранимые процедуры и данное ограничение снято.
Следует отметить, что специалисты 1С от версии к версии сокращают число команд и укрупняют сами команды. Например, сложные обращения к журналам уже собраны один большой SQL-запрос. Также радует, что сложная логика начинает переноситься в хранимые процедуры, например, учетные регистры 1С торговли имеют систему вычисления сальдо в виде хранимых процедур.

Тем не менее, огорчает слишком интенсивное использование курсоров и while-циклов, обусловленное трансляцией макроязыка. Большое использование курсоров примерно в 1,5 раза повышает требования к процессору сервера. Поэтому рекомендуем эксплуатировать 1С:Предприятие при больше чем 15 пользователях на сервере не хуже чем: 2 процессора, 512 мегабайт оперативной памяти.

Мы также попробовали заменить стандартный учетный регистр 1С на учетный регистр собственного изготовления, который был сделан на триггере без использования курсоров и циклов (подробнее о конструировании таких регистров на www.ivn.newmail.ru). Наш вариант расчета сальдо работал в 4-6 раз быстрее, но был не совместим со стандартными отчетами 1С:Торговли.
Тем не менее, ничто не мешает специалистам 1С создать подобные алгоритмы, поэтому можно ожидать радикального повышения производительности в следующих версиях. До этого подобную оптимизацию можно выполнить самостоятельно, трудоемкость этого может быть сглажена тем, что оптимизация может быть сделана одновременно с разработкой заказной конфигурации.

Мы провели инспекцию индексов.
Порадовало, что нам не удалось найти запросов, которые бы выполнялись без использования индексов и приводили бы к неоправданному table scan. Мастер индексов (Index Tuning Wizard) особых провалов в индексации не обнаружил. Также приятно порадовало достаточно эффективное создание индексов для обработки связей документов.
Несколько насторожило, что все индексы составные, причем в конец добавлено поле primary key. Однако исследование через sysindexes показало, что такие индексы эквивалентны индексам без фиктивной уникальности. Тем не менее, при построении собственных отчетов через ADO рекомендуем создавать и свои индексы, в том числе реально составные.

Несколько подробнее остановимся на Index Tuning Wizard, т.к. его может запустить даже не профессионал в MS SQL и получить полезные рекомендации по индексированию, особенно актуальные для заказных конфигураций.
Внимание! До начала экспериментирования сделайте резервную копию базы данных!

Перед запуском мастера нужно сохранить статистику работы пользователей. Для этого нужно в SQL Profiler создать трассировку и указать, что данные трассировки хранить в некой таблице MS SQL, например, TraceTable.
Собрав статистику, следует из Enterprise Manager запустить Index Tuning Wizard и указать, что данные надо брать и таблицы TraceTable.
После анализа мастер предложит создать новые индексы. Проверьте влияет ли новая схема индексов на производительность положительно, если нет, восстановите базу данных из резервной копии. Подробнее об использовании Index Tuning Wizard см. в документации к MS SQL.

Отчетность

Опыт показывает, что через ADO можно строить эффективные отчеты на SQL по таблицам 1С. В связи с этим можно выдать несколько рекомендаций.

1)Создайте view поверх таблиц, по которым будете строить отчеты. Это позволит вам в случае модификации метаданных поменять только view, а не весь SQL-код. Также на view можно настроить куб OLAP.

2)Всегда держите полный скрипт всех своих view и хранимых процерур. Если метаданные изменились значительно рекомендуем скрипт переставить. Благодаря этому MS SQL сможет рассчитать более оптимальные методы обращения к данным (Query Plan).

3)Если запрос сложный, разбейте его на несколько действий через промежуточные таблицы.

4)В качестве промежуточных таблиц не используйте временные таблицы (#temp), дело в том, что частое создание и удаление таких таблиц блокирует sysobjects. Используйте физические таблицы, разделенные через @@spid