Виконання масових операцій

У роботі часто потрібно швидко виконати копіювання великих обсягів інформації Це можуть бути дані, напрацьовані за ніч, або плоскі файли з роздільниками, які ще потрібно обробити Якщо кілька сотень мегабайт інформації повинні переміститися в бази даних SQL Server за обмежений проміжок часу, то на допомогу прийдуть масові операції

Популярність формату XML зростає з кожним днем, однак ще швидше збільшуються розміри файлів цього формату Теги XML додають у файл даних істотний обсяг, іноді збільшуючи його розмір вчетверо Для дуже великих файлів частіше використовують текстовий формат з роздільниками Для вставки даних з цієї спадщини минулого найкраще використовувати масові операції

У SQL Server масові операції виконуються в обхід журналу транзакцій, направляючи дані безпосередньо в базу даних Незважаючи на те що це значною мірою підвищує швидкість операції, ігнорування журналу транзакцій може ускладнити план відновлення даних Все залежить від використовуваної моделі відновлення

■ Проста модель відновлення У журнал заносяться тільки поточні транзакції

■ Модель відновлення з протоколированием масових операцій Окремі транзакції масових операцій йдуть в обхід журналу, проте згодом у нього заносяться дані операції в цілому

■ Повна модель відновлення Масові операції не заносяться в журнал транзакцій, тому сам журнал втрачає свою придатність Щоб перезапустити можливість процесу відновлення слідом за масової операцією, виконайте повне резервування даних, після чого перезапустіть журнал транзакцій

Додаткова Більш детально про моделі відновлення та їх налаштування ви дізнаєтеся в главі 36 Інформація Особливості ведення журналу транзакцій будуть розглянуті в розділі 51

З технічної точки зору синтаксис інструкції SELECT INTO можна також назвати масової операцією, оскільки вона також обходить стороною журнал транзакцій Вона створює нову таблицю з результуючого набору даних інструкції SELECT (див розділ 16)

Операції масової вставки є одним етапом процесу ETL (ця абревіатура буквально перекладається як витягти, перетворити, завантажити) Незважаючи на те що програмування цих процесів на мові Т-SQL допустимо, серйозну конкуренцію йому склала служба інтеграції SQL Server (Integration Service) Детально розробка рішень служби інтеграції описана в главі 42

Масова вставка має виключно високу швидкодію, і я успішно використовую її у виробничих процесах Проте слід особливо відзначити, що в даному випадку вихідні дані повинні бути попередньо перевірені Варіації типів даних, а також відсутні і нерегулярні стовпці можуть викликати серйозні проблеми

Масові операції можна виконати за допомогою утиліти командного рядка ВСР, інструкції bulk insert мови T-SQL, а також служби інтеграції

Команда bulk insert

Команда bulk insert може використовуватися в сценаріях Т-SQL і збережених процедурах для імпорту даних в SQL Server У параметрах цієї команди вказуються таблиця, яка отримує дані, шлях до вихідного файлу і параметри

Якщо ви хочете протестувати цю команду, скористайтеся файлом Addresscsv, з якого виконується завантаження даних в навчальну базу даних Ad venture works Цей файл вже може знаходитися на вашому жорсткому диску також його можна завантажити з сайту MDSN Його обсяг – 4 Мбайт, і в ньому міститься 19614 рядків адресних даних (чесно кажучи, цього занадто мало для операцій ETL)

Наступний пакет допоможе виконати масову вставку з файлу Address в таблицю AWAddress бази даних Adventure works:

Use Tempdb

CREATE TABLE AWAddressStaging (

ID INT,

Address VARCHAR(500),

City VARCHAR(500),

Region VARCHAR(500),

PostalCode VARCHAR(500),

GUID VARCHAR(500),

Updated DATETIME )

BULK INSERT AWAddressStaging FROM C:\Program Files\Microsoft SQL Server\90\Tools\Samples\ AdventureWorks OLTP\Addresscsv1 WITH (FIRSTROW = 1,ROWTERMINATOR =\n)

На моєму ноутбуці Dell XPS ця операція масової вставки зайняла менше половини секунди

Перше, що слід дізнатися про операцію bulk insert, – це те, що кожен стовпець джерела даних вставляється безпосередньо в таблицю призначення, використовуючи відображення один до одного. Перший стовпець джерела вставляється в перший стовпець таблиці приймача, другий – у другій і тд Якщо в таблиці приймача занадто багато стовпців, то зайві ігноруються Якщо ж у ній стовпців менше, ніж потрібно, то з зайвих даних джерела вийде справжнє місиво

Так як операція bulk insert залежна від розташування стовпців таблиць джерела і приймача, то краще використовувати між цими таблицями ще один рівень абстракції у вигляді подання Якщо структури таблиць джерела і приймача не збігаються, скоректуйте уявлення, і вам не доведеться вносити глобальні правки в схему бази даних

Можна також спочатку вставити дані в окрему таблицю, перевірити їх, потім виконати інші перетворення і обєднати цю таблицю з таблицею призначення

Додаткова Команда bulk insert не підтримує конкатенацію рядків і змінних у па-інформація раметров from З цієї причини, якщо вам потрібно приєднати шлях до імені фай-ла, згенеруйте динамічну інструкцію SQL для виконання масової вставки Про створення та виконанні динамічних інструкцій SQL см в главі 18

Параметри команди bulk insert

У практичних завданнях мені постійно доводилося використовувати деякі параметри команди bulk insert

■ FieldTerminator У цьому параметрі визначається символ, що розділяє окремі стовпці файлу джерела даних За замовчуванням використовується кома, але я завжди заміняв її вертикальної рисою

■ RowTerminator У цьому параметрі визначається символ, завершальний рядок даних За замовчуванням використовується 1 \ П1 – Стандартний символ перекладу рядка Однак файли на мейнфреймах і в інших системах часто використовують інші ознаки кінця рядка У цьому випадку відкрийте шістнадцятковий редактор і подивіться, якими кодами завершуються рядка у файлі, а потім визначте кінець рядка в шістнадцяткових кодах Наприклад, шістнадцяткове значення 1 Про А програмується таким чином:

ROWTERMINATOR = ОхОА

■ FirstRow Цей параметр вам стане в нагоді, якщо вихідний файл містить заголовки стовпців У цьому випадку задайте номер того рядка, в якій починаються реальні дані, що заносяться в таблицю

■ TabLock Цей параметр дозволяє заблокувати всю таблицю для монопольного доступу, що захистить SQL Server від проблем блокування сторінок таблиці, що використовуються тільки для вставки Використання цього параметра може значно підвищити продуктивність операції, але в той же час не дасть можливості в процесі вставки звертатися до таблиці іншим користувачам Якщо масова вставка є частиною процесу ETL, повязаного з відособленою таблицею, то це не викличе проблем Але якщо масова вставка виконується в робочу таблицю, з якої користувачі витягують дані, то використання цього параметра не рекомендується

■ Rows per Batch Цей параметр вказує серверу виконувати в одному пакеті вставку тільки заданої кількості рядків, а не всього файлу Налаштування розміру пакета може позитивно позначитися на продуктивності Я рекомендував би почати зі 100 рядків, а потім поекспериментувати, щоб знайти прийнятне значення

■ MaxErrors Цей параметр визначає, вставка скількох рядків повинна пройти невдало, перш ніж буде скасована вся операція Залежно від конкретних виробничих вимог вам може знадобитися встановити значення цього параметра в нуль

Параметр Errorf ile вказує на файл, в якому будуть накопичуватися будів-Новинка киВідкинуті операцією bulk insert У виробничих умовах краще

2005 не нехтувати цією дією

Інші параметри мені жодного разу не стали в нагоді на практиці Серед них Check_ Constraints, CodePage, DataFileType, Fire_Triggers, Keepldentity, Keep-Null s, Kilobytes_per_batch і Order Якщо дані вставляються спочатку в відокремлену таблицю і тільки потім обєднуються з основною інформацією після виконання відповідних перетворень, то використання цих параметрів навряд чи доречно

Операція масової вставки обробляє стовпці в тому порядку, в якому вони знаходяться у файлі з роздільниками Цей порядок повинен в точності збігатися з таблицею-приймачем Масова вставка в уявлення додає додатковий рівень абстракції, тому в даному випадку можна буде внести зміни в порядок стовпців пізніше

При створенні інструкцій масової вставки часто має сенс відкривати вихідний файл за допомогою Excel і перевіряти дані Сортування даних у стовпцях допоможе виявити існуючі аномалії форматування

Утиліта ВСР

Утиліта ВСР (ця абревіатура розшифровується як програма масового копіювання) – варіація командного рядка операції масового копіювання Вона відрізняється від операції масової вставки тим, що може як імпортувати, так і експортувати дані Ця утиліта використовує багато з параметрів операції bulk insert Її базовий синтаксис наступний:

ВСР табліца_назначенія путь_к_файлу_данних параметри

Для таблиці призначення використовуйте повне чотирьохкомпонентну імя (сервер база_ даних, схема, обєкт) Якщо хочете ознайомитися з повним синтаксисом цієї утиліти, наберіть у командному рядку ВСР

Так як ця програма є зовнішньою, вона вимагає авторизації для підключення до сервера У вас є два варіанти дій: вказати в командному рядку параметр-Р і запрограмувати пароль в пакетному файлі або не вказувати параметр – Р, і тоді система сама запросить у вас введення пароля Особисто мені не подобається жоден з цих варіантів

Для простих операцій ETL я вважаю за краще використовувати операцію T-SQL bulk insert У складних операціях, що містять перетворення даних, виграє використання служби інтеграції Якщо говорити чесно, то я вкрай рідко вдаюся до послуг утиліти командного рядка ВСР

Резюме

Цю главу можна вважати доповненням до попередньої – в ній розглядалася специфічна команда Т-SQL Масові операції є тією робочою конячкою, яка дозволяє імпортувати величезні масиви даних, ігноруючи журнал транзакцій і направляючи дані безпосередньо в таблиці Єдиним їх недоліком є ​​те, що вони ускладнюють план відновлення даних Найкраще виконувати операції масової вставки за допомогою команди bulk insert мови T-SQL або проектів служби інтеграції

У наступному розділі ми розглянемо ще одну специфічну функцію Т-SQL, і це одна з моїх улюблених тем Приєднуйтесь до мене і відмовтеся від використання курсорів

Курсор

Мова SQL відмінно справляється з обробкою наборів рядків Однак SQL виріс з коротких штанців старих структур файлів ISAM, і порядкова обробка даних залишилася тільки у формі нескінченно повільного курсора

Незважаючи на те що існують цілком виправдані причини використання курсорів, основний з них все ж є те, що програмісти зі старим процедурним мисленням відчувають себе з ними більш комфортно, ніж з реляційною алгеброю, заснованої на множинах

Курсори SQL створюють оманливе враження легко настроюються Коли програмісти бачать довгий список їх параметрів, вони думають, що з їх допомогою можна добитися високої продуктивності курсорів Так і типи курсорів мають такі привабливі назви, як курсор прямого доступу, динамічний і ключовою Наведу цитату з MSDN: СУБД Microsoft SQL Server 2000 реалізувала курсор з оптимізованою продуктивністю, назвавши його курсором прямого доступу . Питання 70-229 іспиту з проектування баз даних SQL Server 2000 навіть містив питання, який з курсорів можна образно назвати пожежним. Не вірте всьому цьому Курсори не можуть бути швидкими в силу своєї природи – вони за один раз підбирають одну крихту даних

Другий рівень теорії оптимізації (див главу 6) розглядає питання створення високопродуктивних систем Він передбачає створення пакетних, а не ітеративних рішень Незважаючи на те що в цьому розділі ми розглянемо саме ітеративний прохід за даними за допомогою курсору, ви повинні з неї винести стратегічний висновок, що потрібно максимально обмежити використання курсорів, замінюючи їх пакетами запитів

Додаткова Курсори SQL Server є серверними, що ^ інформацій відрізняє їх від клієнтських курсорів ADO Ці

            w курсори розміщуються в сервері перед тим, як

небудь дані відправляються клієнту Клієнтські курсори часто використовуються для прокрутки рядків у наборах даних ADO при заповненні користувача форм

Про курсор ADO йтиметься у розділі 30

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*