В якому випадку має сенс пряма запис в SQL-таблицю?
Коли може знадобитися пряма запис в SQL -таблиця на платформі 1С 8.1?
У нашому випадку таблиця регістру 1С8.1 використовувалася як джерело для перекачування даних в інший обліковий базу. Деякий час все працювало засобами 1С. Але настав момент, коли процес запису регістра став займати непристойно багато часу. При цьому всі дані в регістрі «гостро необхідні».
Як прискорити процес запису в SQL-таблицю
Після деяких поневірянь (перевершити 1С в плані оптимізації роботи з SQL мені не вдалося), постало питання про індексацію таблиці. В результаті було вирішено скопіювати таблицю регістра в окрему таблицю і вбити все індекси, що в рази прискорило процес прямого запису.
Далі описана реалізація процесу запису в SQL -таблиця без індексів.
Розберемо на конкретному прикладі.
Припустимо, що перед нами стоять завдання:
- Збирати за певними рахунками проводки за період в розрізі організацій (Орг) і сценаріїв (Сцен), обробляти ці дані за відомими правилами і записувати дані в таблицю окремої бази
- Записи за період по організаціях і сценаріями повинні записуватись
- Необхідно архівувати таблицю у вигляді копій на дату
Реалізація
Для створення бази нам буде потрібно "Enterprise Manager" (рис.1)
Відкривши "Enterprise Manager". позиціюємося на папці "Databases" і клацанням правої кнопки миші створюємо нову базу (рис.2).
Створюємо базу (рис.3).
Налаштування бази зображена на рис.4 і 5
(T-SQL:
Create DataBase MyBase
Alter DataBase MyBase SET Recovery Simple
Alter DataBase MyBase SET Auto_Shrink ON)
Відкриємо нашу базу, спозіціоніремся на "Tables" і правою кнопкою миші - створити нову таблицю (рис. 6)
Дотримуючись ТЗ, ми маємо "ключові" поля:
Решта поля представимо у вигляді основних типів даних (рис. 7). Типи значень і їх довжина взяті на прикладі регістра 1С.
Таким чином, ми маємо таблицю "MyTable" (рис. 8):
Для коректної роботи пишемо Організацію і Сценарій у вигляді посилань (рис. 8). Це нам знадобитися в подальшому.
Чи не претендую на те, що обробка повинна виглядати саме так. Хотілося б відзначити, що процеси обробки даних і їх запис в регістр розділені.
Збір та обробка даних
В даному випадку цей процес не становить інтересу. В кінцевому підсумку маємо таблицю значень "табліцаДанних"
Очищення таблиці, запис в регістр
Будемо використовувати наступну схему:
Не буду приводити весь код обробки, зупинюся лише на основних моментах.
СтрокаКоннекта = "Provider = SQLOLEDB; Password = sdR $ 543Qr; Persist Security Info = True; User ID = userSQL; Initial Catalog = MyBase; Data Source = serverSQL";
// Джерело отримання GUID: Mista. ru
Далі власні дослідження:
Фрагмент коду очищення і записи в регістр:
Необхідно відзначити, що в наведеному вище фрагменті коду "FIRM" - Організація, "BASE" - Сценарій, "Entity_Name" - приклад строкового значення, "InRep" - логічне і "SUMM" - сума.
Відразу хочу обмовитися, що архівування відбувається шляхом копіювання таблиці на певну дату.
55. Alexander Speshilov (speshuric) 950 16.02.11 23:36 Зараз в темі
Я не загубився, просто трохи завантажений.
Отже. Про дрібниці.
Про моє (не тільки моє) відношення до управління транзакціями з клієнта я основне сказав. Але є ще момент. Посилаючи "BEGIN TRANSACTION" прямо на сервер ми не враховуємо, що деякі провайдери (ну і MS SQL теж, але не в ADO, а в ADO.NET) можуть працювати в режимі пулу з'єднань. Це прекрасно, що клієнт швидко піднімає з'єднання з пулу, замість "довгого" очікування створення з'єднання, але цей механізм складніше. І глючная. Тобто "BEGIN TRANSACTION" гіпотетично може піти не в тому з'єднанні, що інші запити і "COMMIT", хоч і не в такому тривіальний випадку. Ні, звичайно, це зазвичай Фікс (разарботчіком драйвера), але навіщо шукати граблі, коли при сполученні є методи BeginTrans, CommitTrans і RollbackTrans?
Зовсім незрозуміла конструкція
Навіщо такі танці з транзакціями? Потрібна транзакція - впіхні в запит. Тільки якщо використовувати кілька команд в одному запиті, то варто спочатку вставляти "SET NOCOUNT ON;". Насправді від транзакції тут тільки зайве стрибання від 1С в ADO, тому простіше прибрати.
Дивне зауваження "Слід згадати, що для запису в таблицю SQL повинні бути права власника таблиці.". Я можу здогадуватися, що малося на увазі (наприклад те, що даним логіном пропонується зіставити користувача БД зі схемою dbo за замовчуванням), але право ж - неочевидно. Краще приведіть скрипти створення таблиць і логінів - це буде однозначно.
Неакуратна робота з дужками в шматку:
Якщо вам пофігу, що пишете, то як "новачок" зможе розібратися в ваших чернетках?
За що ви СокрЛП на рядок нацькували? Він же її пообкусает. Гаразд справа покусає (там багато кусають і не видно), але ж і зліва покусає!
Дивно, що транзакції використовуються, але при перезапису (як би єдина операція) видалення відбувається в одній транзакції, а вставка в інший. Ніпанятна.
Drop table в транзакції в циклі з питанням, як я писав вище - це ваще жесть. По суті - абсолютно розрахованому на одного рішення.
Потрібність "архівації" на цих обсягах теж викликає великі сумніви. На таблицях об'ємом до 100 ГБ в рік кластерного індексу за датою часто вистачає по вуха. А далі можна використовувати секціонування.
Запит до sysobjects йде без обмеження типів об'єктів. А раптом буде процедура з таким ім'ям?
Якщо вже коннект закриваєте в кінці, то чому рекордсети не закривати після використання?
Далі. У методу Execute з'єднання і команди взагалі-то є параметри, які є бітової маскою CommandTypeEnum і ExecuteOptionEnum. Зокрема є параметр "adExecuteNoRecords", який рекомендується для виконання запитів від яких не очікується результат. До речі, для читабельності рекомендую використовувати конструкції типу
так можна зробити код дуже читабельним і стерпним з VBS.
Приклад роботи з параметрами можна знайти тут. Якщо коротко, плюси: принципова неможливість SQL injection, не потрібно думати про подання констант (чисел, дат), одноразова компіляція запиту сервером, економія мережевого трафіку. Мінус мені відомий один: для деяких типів для дуже простих запитів (зокрема інсерт вузьку в таблицю без індексів) через повільну роботу на стику 1С і COM швидше в 1С зібрати потрібний скрипт (але теж треба вміти) і відправити його на сервер.
Блокувальні хинти. За замовчуванням MS SQL використовує рівень ізоляції READ COMMITTED і блокування по записах (по можливості) з подальшою ескалацією за кількістю блокувань. Це в цілому зручно, але не у випадку масового завантаження. У разі масової завантаження для економії пам'яті краще заблокувати таблицю цілком до кінця транзакції.
Ну а взагалі, якщо цікавить продуктивна завантаження даних, то краще чуйна підтягнути теорію. щоб втілити її в практиці. А якщо хочеться зробити приклад для новачків, а не суперперегрузкі то цей приклад повинен бути бездоганно гарний у своїй простоті: він повинен бути в єдиному стилі написання (а не те Conn, то CONN), він повинен бути цілісним і виконуваних, він повинен бути правильним не тільки з точки зору новачка, але і з точки зору нормального програміста.