Быстрое обновление данных в клиент-серверном варианте (SQL)

В статье рассматривается инструкция UPDATE языка запросов SQL.

Как мы обычно меняем записи регистров сведений?

1С с записями баз данных работает вполне сносно, особенно если это набор записей того или иного регистра. Если записей немного, то для их изменения вполне годится чтение набора записей, заполнение нужной колонки и запись изменившихся данных. Т.е. примерно так:

  Набор = РегистрыСведений.КакойТоРегистрСведений.СоздатьНаборЗаписей();
Набор.Отбор.Реквизит.Значение = ЗначениеФильтра;
Набор.Отбор.Реквизит.Использование = Истина;
Набор.Прочитать();
Для Каждого Ст ИЗ Набор Цикл
Набор.НужнаяКолонка = НужноеЗначение;
Набор.НоваяКлонка = Набор.КолонкаСоСтанымЗначением;
КонецЦикла;
Набор.Записать();

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

  Запрос = Новый Запрос(
"ВЫБРАТЬ
|  НужнаяКолонка КАК НужнаяКолонка,
|  КолонкаСоСтанымЗначением КАК НоваяКлонка,
|  Реквизит,
|  Реквизит1,
|  Реквизит2
|ИЗ
|  РегистрыСведений.КакойТоРегистрСведений
|ГДЕ
|  Реквизит = &ЗначениеОтбора");

Запрос.УстановитьПараметр("ЗначениеОтбора");
Для Каждого Ст ИЗ Запрос.Выполнить().Выгрузить() Цикл
Запись = РегистрыСведений.КакойТоРегистрСведений.СоздатьМенеждерЗаписи();
ЗаполнитьЗначенияСвойств(Запись, Ст);
Запись.Записать();
КонецЦикла;

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

 

Как можно поменять записи колонок таблиц в SQL, не трогая остальное?

Данный вопрос можно очень быстро решить с помощью инструкции UPDATE языка запросов SQL.

Сделать это можно так:

    UPDATE [MyBase].[dbo]._InfoReg1234
SET _Fld5678 = 1
WHERE _Active = 0

Собственно, здесь мы просто заполняем поле _Fld5678 таблицы _InfoReg1234 значением «1». Если тип поля _Fld5678 булево, то считайте, что вы проставили «ИСТИНА» во всех активных записях регистра.


Как понять, в каком поле какой таблицы SQL что хранится?

Для того, чтобы узнать, какое поле какой таблицы как называется на SQL-сервере, можно использовать функцию встроенного языка 1С «ПолучитьСтруктуруХраненияБазыДанных», которая возвращает таблицу значений, содержащую в себе структуру базы данных.

  Для Каждого Стр ИЗ ПолучитьСтруктуруХраненияБазыДанных(,Истина) Цикл

НовСтр = ТаблицаБазДанных.Добавить();
ЗаполнитьЗначенияСвойств(НовСтр,Стр);

Для Каждого стр1 ИЗ Стр.Поля Цикл

НовСтр = Поля.Добавить();
ЗаполнитьЗначенияСвойств(НовСтр,Стр1);

КонецЦикла;

КонецЦикла;

После выполнения данного кода у вас в таблице значений «ТаблицаБазДанных» будет находится список таблиц, а в таблице значений «Поля» — список полей. Если для каждого элемента первой таблицы добавить некий индекс и прописать его для всех элементов второй таблицы, то будет достаточно просто искать во второй таблице поля для нужной таблицы базы данных. Сама по себе структура этих таблиц подробно описана в справке по 1С.

 

Рабочий пример:

 

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

 

Дано:

  1. Документ «КакойТоДокумент», таблица SQL «_Document55»;
  2. Поле, в которую нужно записать сумму «_Fld12507»;
  3. Табличная часть «_Document55_VT172″;
  4. Поле табличной части, содержащее сумму «_Fld749»

Итак, давайте напишем скриптик для SQL, который все, что нам нужно, сделает за считанные минуты даже если у нас не одна сотня тысяч документов:

UPDATE _Document55
SET _Fld12507 = SUM(t2._Fld749)
FROM _Document55 AS t1
LEFT JOIN _Document55_VT172 AS t2
ON t1._IDRef = t2._Document55_IDRRef
GROUP BY
t1._IDRef

Собственно, все.

 

Что мы тут написали?

  1. Мы указали UPDATE для таблицы «_Document55″, т.е. мы будем именно эту таблицу обновлять.
  2. Мы указали, что поле «_Fld12507″ мы будем заполнять результатом агрегатной функции SUM — сумма — для поля «_Fld749» таблицы t2 («_Document55_VT172″).
  3. Стандартное FROM (ИЗ) и имя таблицы нашего документа.
  4. LEFT JOIN (ЛЕВОЕ СОЕДИНЕНИЕ) — соединяемся с табличной частью по (ON) ссылке.
  5. GROUP BY (ГРУППИРОВАТЬ ПО) — группируем по ссылке.


Заключение.

В качестве заключения могу сказать, что SQL не так сложен, как его малюют. На нем вполне реально реализовать не только операции замены каких-то данных, но и весьма существенные процессы, такие, например, как обмен данными между базами. У нас в компании SQL-скрипты применяются весьма часто. В частности, для сравнения данных между многочисленными базами с разнородным содержимым (например, между УТ и БП КОРП).


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

39 Comments

  1. genayo

    А где собственно обработка с которой скрины?

    Reply
  2. starik-2005

    А зачем Вам? На Инфостарте таких обработок штук сто.

    Reply
  3. genayo

    (2) да в общем незачем, но просто непонятно, к чему скрины тогда…

    Reply
  4. starik-2005

    (3) genayo, скрин как бы намекает, что такая обработка может существовать в природе. Я не публикую обработки — я публикую статьи, прочитав которые грамотный читатель сможет создать такую обработку ))

    Reply
  5. BabySG

    (0) Статья крайне опасна (особенно для начинающих), т.к.:

    1. Подобное использование нарушает ЛС

    2. Полностью не учитывается, что в событиях объекта может производится дополнительная обработка при постановке/снятии пометки удаления

    Проще говоря: чревато разрушением данных

    Reply
  6. Danila-Master

    http://v8.1c.ru/predpriyatie/questions_licence.htm

    Пунк 65:



    Нельзя обращаться к данным информационной базы напрямую, минуя уровень объектов работы с данными «1С:Предприятия» — например при помощи средств СУБД или при помощи внешних компонент, которые реализуют прямой доступ к СУБД. Это ограничение распространяется на любые действия с данными, в том числе на изменение их структуры, а так же на чтение или изменение самих данных информационной базы или служебных данных «1С:Предприятия».

    Reply
  7. starik-2005

    (5) BabySG, Вы отчасти правы — не стоит садить в одну клетку питона и кролика, если, конечно, вы не хотите покормить питона кроликом…

    Также стоит заметить, что как минимум две инструкции в 1С крайне опасны: непосредственное удаление объекта через Объект.Удалить() и запись пустого набора записей регистра через НаборЗаписейРегистра.Записать(). Второе аналогично операции TRUNCATE SQL.

    (6) Danila-Master, Вы по какой-то причине проигнорировали текст: «Данное ограничение необходимо для обеспечения стабильности работы механизмов системы, осуществления поддержки и возможности перехода на новые версии «1С:Предприятия».» Во встроенном языке платформы множество возможностей удалить или модифицировать данные так, что они перестанут «читаться» 1С. При этом у 1С нет собственных средств, позволяющихъ максимально утилизировать возможности СУБД на базе SQL. Я не предлагаю обращаться напрямую к файлу 1CD (или как его так, в котором содержится файловая база — на инфостарте, кстати, есть пример такой обработки — поищите и возмутитесь), я лишь говорю о том, что неисчерпаемый ресурс производительности заложен в самом механизме серверов SQL — его можно и нужно использовать, да, с умом, но безумный программист, поверьте, и на встроенном языке может причинить кучу непирятностей базе данных. Да и вообще метод «ПолучитьСтруктуруХраненияБазыДанных» указывает именно на «Это означает, что средства СУБД (или любые другие внесистемные средства) можно использовать только в том случае, если документация по продуктам линейки «1С:Предприятие» (включая ИТС) содержит явную рекомендацию использовать данное средство для решения данной задачи.» Если уж 1С дала доступ к данным, то она косвенно согласилась, что разработчики могут использовать структуру данных для своих нужд.

    Reply
  8. starik-2005

    А вообще, я давно коллекционирую фобии разработчиков. Вот, пожалуйста, одна из них во всей красе. )))

    Reply
  9. Danila-Master

    (7)

    Я не предлагаю обращаться напрямую к файлу 1CD (или как его так, в котором содержится файловая база — на инфостарте, кстати, есть пример такой обработки — поищите и возмутитесь)

    При чем тут файл 1CD? Ведь речь идет про доступ к таблицам на уровне SQL

    Если уж 1С дала доступ к данным, то она косвенно согласилась, что разработчики могут использовать структуру данных для своих нужд.

    Сама 1С трактует этот пукт ЛС обнозначно, что нельзя обращаться к данным информационной базы напрямую. Даже использовать представления SQL нельзя.

    А вот «Внешние источники данных» — документированная возможность, поэтому ее использовать можно.

    Reply
  10. Danila-Master

    (8) Вы не первый, кто поднимает подобную тему. Именно для этого и был создан п.64, который разъясняет политику ЛС.

    А дальше уже Ваше право делать что-то или не делать. В конце концов, всем неоднократно объясняют, указывают на закон, что пяьным за руль садиться нельзя, но есть такие: «Ну я же чуть-чуть!»

    Дальнейшая полемика, имхо, бессмысленна.

    Хотя, замечу, тоже был бы не против прямого доступа к данным.

    Reply
  11. starik-2005

    (9) Danila-Master, внешние источники работают весьма неплохо на чтение, но попытки 1С сделать запись в них пока весьма смехотворны — валится в дедлоки, хотя вроде причин для этого нет никаких. Вот когда сделают что-то действительно стоящее — будем использовать, пока же работа механизма крайне неудовлетворительна. А их пункт 64 весьма противоречив. ни одна уважающая себя контора не напишет о том, что к базам данных, расположенным на серверах под управлением сторонних СУБД, нельзя обращаться с помощью инструментов тех самых сторонних СУБД. Надеюсь, 1С осознает весь трагизм и глупость ситуации и убьет автора данного пункта об свою желтую стену )))

    Reply
  12. starik-2005

    (10) Danila-Master, на это только одно ответить могу: делали, делаем и делать будем. Иначе может получиться, что и бэкап средствами симантека делать нельзя, т.к. в документации описана лишь возможность делать бэкапы встроенными средствами SQL-сервера. Надеюсь, они добавят пункт о том, что любые действия с БД вне контекста 1С:Предприятие осуществляются на свой страх и риск — тогда все и всем станет ясно.

    Reply
  13. genayo

    (10) А если нарушить этот пункт соглашения, тогда наступит что? Что 1С сделает с виновным, перестанет предоставлять обновления?

    Reply
  14. BabySG

    (7) объектные методы вызывают обработчики платформы, где может содержаться некая логика. Это очень существенное отличие от указанных методов прямых запросов.

    Я сам, конечно, применял подобное, но не «в лоб». В большинстве случае нужно было писать запросы намного сложнее, либо организовывать постобработку средствами 1С.

    Подобные методы точно не для начинающих — так данные снесут только в путь. Ведь это имеет смысл только для больших и очень больших баз. а в момент допуска к таким базам уже должны быть соответсвующие знния (как минимум на уровне сертификата проф. эксперта)

    Reply
  15. starik-2005

    (14) BabySG, вот никогда сертификатами не заморачивался — всегда считал, что лучше пойти на семинар по ТРИЗ, чемнакурсы по 1С.

    Программировать нужно учиться, умея прежде всего думать, а не наоборот))

    Reply
  16. vde69

    1с не зря исключила прямые апдейты…. конечно в исключительных случаях наверно они нужны, но поверьте мне не стоит они тех последствий…

    сделав что-то с данными нештатно Вы

    1. не оставляете следов (не понятно кто и чего менял)

    2. отключаете все программные реакторы (подписки и прочее)

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

    по этому не рекомендую

    Reply
  17. kalimehtar

    Насколько это безопасно при работающем 1С?

    Сервер 1С, я предполагаю, часть данных кэширует. И после такого «обновления», до перезапуска он про изменения не узнает.

    Reply
  18. fvadim

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

    Reply
  19. starik-2005

    (16) vde69, в ряде случаев следить действительно надо. Но при заполнении новых реквизитов проблем никаких — абдейтим ровно одну таблицу.

    Если говорить о ссылочных типах, то отличия появляются только при составном типе данных — там появляется поле TRef и Type, в первом из которых хранится номер объекта (для документа с таблицей _Document55 это значение будет 55), во втором хранится тип элементов метаданных — там тоже все просто. В 90% случаев связанность данных нарушить не получиться, в остальных случаях надо тестировать на копии.

    (17) kalimehtar, безопасно, но, действительно, 1С кеширует выборки. Иногда изменения появляются через несколько минут (если вы открывали объект, потом меняли данные скриптом, потом открыли объект еще раз). Все зависит от кеша rphost’а — можно просто рестартануть сервис и все встанет на свои места срезу. Но это касается именно объектов. При выполнении запроса обращение идет непосредственно к таблицам SQL, поэтому данные будут актуальны.

    (18) fvadim, можно и через профайлер, только к чему такие извращения?

    Reply
  20. Steelvan

    Я, когда готовился сдавать на эксперта, тоже этим баловался.

    А потом, когда начал писать тиражные решения, понял что лучше писать нормальным языком 1С.

    Это можно использовать на локальных решениях в качестве саморазвития. И только.

    Грамотно построенная система с правильным обслуживанием хорошо работает и без таких «штук».

    Reply
  21. starik-2005

    (20) Steelvan, тогда ответьте, как достаточно быстро заполнить новый реквизит в документе, если документов в базе примерно 5кк? У нас, например, есть четыре документа, в которые мы добавили общие реквизиты. Общее число документов примерно 5кк. 1С через Объект.Записать(РежимЗаписи.Запись) обновляет не только сам документ, но и пять-семь его табличных частей. Я тут посчитал — стандартный механизм в 10 потоков будет заполнять новый реквизит примерно 18 часов (в один поток — 180 часов). Инструкция UPDATE обновила таблицы за 4 минуты, посчитав сумму из табличных частей и поместив результат в таблицу основного документа. Вот пока 1С не научится записывать хотя бы основную таблицу документа без его табличных частей, народ будет стараться вместо добавления реквизита в документ, добавлять допреквизит и огребать проблемы с блокировками, трудности с разделителями данных и прочие «плюшки».

    А вот то. что специалистов, способных написать качественный и быстрый запрос на SQL крайне немного — вот это действительно проблема. И такие специалисты в разработке высоконагруженных сервисов будут нужны не особо.

    Reply
  22. Steelvan

    Пишите вы грамотно, вам бы еще «не» поменьше использовать. Текст на порядок лучше читаться будет.

    «…обновляет не только сам документ, но и пять-семь его табличных частей…»

    …обновляет и сам документ и пять-семь его табличных частей…

    «Вот пока 1С не научится записывать хотя бы основную таблицу документа без его табличных частей, народ будет стараться вместо… »

    «Вот когда 1С научится записывать хотя бы основную таблицу документа без его табличных частей, народ перестанет вместо…»

    «И такие специалисты в разработке высоконагруженных сервисов будут нужны не особо. »

    Здесь запутано, как понимать ?

    И такие специалисты в разработке высоконагруженных сервисов будут ЛИШНИЕ ?

    После рефакторинга модулей на 10 000 строк понимание важности восприятия постепенно, но приходит.

    А если по сути, то я вам и писал «Это можно использовать на локальных решениях».

    Хотя, конечно, это можно использовать везде. Только кто это будет покупать и тем более поддерживать. И рискованно выстраивать на таких конструкциях серьезный функционал. Большой риск что на следующей версии платформы это придется переписывать. Вот такой ответ без «не» 😉

    Reply
  23. nSpirit2

    Не стоит так делать никогда особенно прочитав такое на инфостарте.


    UPDATE [MyBase].[dbo]._InfoReg1234

    SET _Fld5678 = 1

    WHERE _Active = 0

    Только надо помнить о то что кроме этой таблички регистра есть еще и таблица _InfoRegChangeRec1234 которую не стоит забыть, а то плохо потом будет.

    Также как у документов есть _DocumentChangeRec.

    Если есть планы обмена то все эти фокусы со скулем могут плохо выйти.

    Ну и не стоит забывать о реквизитов составного типа, которые тоже хранятся особым образом.

    Reply
  24. starik-2005

    (22) Steelvan, писать-то я научился не только на SQL, что, полагаю, не может вас не радовать (должно радовать).

    Рефакторинг модуля на 10 000 строк — это что? Я на днях типовое проведение документов в «1С Бухгалтерия 2.х» переписал полностью, ибо уровень программистов из 1С оставляет надежду лишь на то, что они на рынке труда внезапно окажутся ЛИШНИМИ ))) Разве это может увеличить мою значимость здесь? Вряд ли.

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

    Reply
  25. starik-2005

    (23) nSpirit2, если у вас есть РИБ — это, конечно, нужно учитывать. Но лучше запустить скрипт для каждой из баз, чтобы не тащить туда миллионы записей.

    Нет, я, конечно, понимаю, что тут в основном программисты начинающие, но надо как-то расти. И как раз стараюсь обозначить точки роста, показать горизонты. А то этакие тепличные существа мира 1С постоянно будут жаловаться на проблемы платформы и отказывать заказчикам в быстром решении проблем исключительно на базе собственного незнания механизмов, выходящих за рамки платформы.

    Reply
  26. nSpirit2

    (24)

    Рефакторинг модуля на 10 000 строк — это что? Я на днях типовое проведение документов в «1С Бухгалтерия 2.х» переписал полностью, ибо уровень программистов из 1С оставляет надежду лишь на то, что они на рынке труда внезапно окажутся ЛИШНИМИ ))) Разве это может увеличить мою значимость здесь? Вряд ли.

    А что вы там переписали если не секрет ?

    (25) Тут скорее понимание взаимодействия платформы со SQL. Но имхо это очень редкая задача изменить 5кк старых документов без перепроведения просто установив реквизит 😉

    Reply
  27. starik-2005

    (26) nSpirit2, переписал партионку — оптимизировал запросы, реализовал стек партий для группового проведения; изменил механизм зачета авансов — добавил отрицательные суммы в реализациях товаров и услуг и возврате товаров от клиента, реализовал верное отражение данных на 62-м счету (без «бабочек»). Добился адекватной работы при регистрации документа в последовательности, реализовал в итоге приемлемое количество ожиданий на блокировках при многопоточном проведении (соответственно, и при одновременном проведении документов несколькими пользователями)..

    Ну и по мелочи со счетами учета…

    Reply
  28. Steelvan

    (24)

    Хм, обидчивый, а воспитание видимо запрещает. гы гы гы

    Reply
  29. starik-2005

    (28) Steelvan, А «видимо», видимо, надо запятыми отделять от основного предложения. А у Вас как-то это невидимо…

    Reply
  30. Steelvan

    А это была проверка на внимательность. Давай зачетку !

    Reply
  31. starik-2005

    (30) Steelvan, не знаю, что такое «зачетка». Не использован никогда.

    Reply
  32. engineer74

    (6) Danila-Master, а как быть тем у кого базы большие . где реструтуризации идут сутки? или 1с не использовать в больших предприятиях?

    Reply
  33. starik-2005

    (32) engineer74, судя по всему — не стоит ))

    Reply
  34. engineer74

    Старик давай разваивай тему. мне нрвятся твои статьи. Интересно: алгоритм получение строки подключения именно в текущей базе (чтобы копия случайно вживую не залезла.). Тема реструктуризации больших баз. Логирование изменений регистра сведений посредством тригеров. Представления в ms sql c «человеческим названием». ну и дальше по пути оптимизации производительности.

    Reply
  35. starik-2005

    (34) engineer74, тут одна опасность — при таком подходе становится не нужна 1С, как средство. Кое-где ее использовать действительно не стоит.

    Reply
  36. engineer74

    (35) пока 1с это стандарт — код можно передать другим для поддержки и развития — это требования.

    Reply
  37. Steelvan

    (31)

    Если вы первый раз слышите слово «зачетка» и у вас нет опыт работы с ней, то спросите у гугла.

    Reply
  38. starik-2005

    (37) Steelvan, это какая-то бумажка, куда преподаватель за хорошие глаза, посещение лекций или бутылку водки ставит «хор», без оного «уд», а при длинных ногах «отлично»? Да в гробу я такое образование видел ))) Гордиться таким могут только идиоты, поэтому те, кто вспоминает про зачетки, ассоциируются у меня с прогнувшимися под систему не очень умными людьми исключительно.

    Reply
  39. starik-2005

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

    Reply

Leave a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *