45792 (Работа с объектами большого объема в MS SQL и ADO)

Работа с объектами большого объема в MS SQL и ADO

Алексей Ширшов

Введение

Эта статья появилась на свет только благодаря вашим не перестающим появляться вопросам типа: «Кто-нибудь может привести пример кода для работы с полями базы, содержащими картинки…используя ADO и Visual C++…», и тому, что мне лень на них отвечать.

Работа в MS SQL

Давайте сначала разберемся, как работать с большими объектами (LOB – large objects) на уровне базы данных. MS SQL Server поддерживает следующие типы больших объектов:

image – содержит бинарные данные переменной длины. Длина не может превышать 2 гигабайт.

text – содержит текстовые данные переменной длины в кодировке сервера (in code page of the server). Длина не может превышать 2 гигабайт.

ntext – содержит текстовые данные в Unicode-формате. Длина не может превышать 2 гигабайт.

Для хранения данных всех этих типов и низкоуровневой работы с ними SQL Server использует один и тот же механизм.

Физическое размещение больших объектов

MS SQL Server 2000 поддерживает два метода хранения больших объектов: первый метод оставлен ради совместимости со старыми версиями и не обеспечивает должной производительности в определенных случаях, по сравнению с новым методом. По умолчанию сервер работает в старом режиме.

При использовании старого метода сервер всегда размещает данные в отдельных страницах, а указатель на первую из них хранит непосредственно в строке данных.

ПРИМЕЧАНИЕ Точнее, в строке данных хранится указатель на корень B-tree, а не на какие-либо таблицы данных. Подробнее об этом, см. следующий раздел.

Используя новую стратегию, сервер может хранить часть данных непосредственно в строке таблицы. Это приводит к экономии памяти и увеличению производительности для LOB-ов небольшого размера.

Стратегия размещения по умолчанию

В качестве структуры хранения данных используется B-tree. В строке данных хранится 16-байтный указатель на корень дерева – структуру размером 84 байта. Если размер данных не превышает 32 Кб, в корневой структуре хранятся ссылки на блоки данных, расположенных на этой же или других страницах. Большие объекты хранятся на специальных страницах, на которых нельзя размещать никакие другие данные, кроме image, text и ntext. Однако данные этих типов из разных таблиц могут быть размещены на одной странице. Если общий размер данных не больше 64 байт, все данные сохраняются в корневой структуре.

Рисунок 1.

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

Улучшенная стратегия

В SQL Server 2000 появилась возможность использовать новый метод хранения больших объектов. В нем отсутствует 16-байтный указатель. В строке данных (data row) могут находиться как сами данные (в случае, если они меньше заданной величины), так и корень B-tree. Для каждой таблицы размер хранимых больших объектов можно задавать индивидуально с помощью процедуры sp_tableoption. Проверить режим размещения можно с помощью инструкции objectproperty с параметром TableTextInRowLimit. В следующем скрипте создается таблица (которую мы будем использовать на протяжении всей статьи) blob_test, затем проверяется режим размещения данных в этой таблице, и, наконец, устанавливается размер данных в строке (350 байт), что автоматически задает улучшенную стратегию размещения больших объектов в таблице.

create table blob_test(id int identity, img image,txt text,ntxt ntext) select case when OBJECTPROPERTY(object_id('blob_test'), 'TableTextInRowLimit') = 0 then 'data outside the table' else 'data in row' end sp_tableoption blob_test, 'text in row', 350

Вместо размера больших объектов в процедуру sp_tableoption можно было передать значение On. В этом случае размер устанавливается равным 250 байтам. Отключить размещение данных в строке можно, задав в качестве параметра значение 0 или Off. Максимальный размер данных в строке равен 7000 байт. Следующий рисунок иллюстрирует схему распределения данных при размере, превышающем 350 байт (для нашей таблицы).

Рисунок 2

Если в строке данных присутствует расширяемое поле типа varchar или varbinary, то при его расширении, если общий размер строки превысит 8060 байт, часть данных из строки может быть выгружена на дополнительные страницы. Другими словами, остальные поля имеют приоритет перед LOB при нехватке пространства в строке данных. Вернем нашу таблицу в начальное состояние, так как следующие примеры рассчитаны на режим по умолчанию:

sp_tableoption blob_test, 'text in row', 'off'

После перевода таблицы в режим «данные в строке» сами данные в строку не переносятся, однако обратное действие вызывает немедленную операцию по переносу данных на отдельные страницы. При этом вся таблица полностью блокируется, а при большом количестве переносимых данных операция может занять длительное время.

Работа с большими данными

В работе с бинарными данными на уровне сервера большого смысла нет. Поэтому большинство примеров использует текстовые данные, хотя описываемые процедуры вполне сгодятся и для бинарных данных.

При работе с LOB можно использовать обычные операторы SQL (select, insert). Но иногда может понадобиться работать не с LOB целиком, а с его частями. Операторы работы с такими небольшими порциями довольно необычны для SQL тем, что в них используются указатели, смещения и другие низкоуровневые понятия.

Указатель представляет собой 16-байтовую переменную типа binary или varbinary. Это абстракция, указывающая на данные в конкретной колонке конкретной строки. Указатель получается путем вызова функции textptr, куда передается имя колонки. Он может быть равен NULL в том случае, если данных не существует. Если указатель равен NULL, вы не можете использовать функции READTEXT, WRITETEXT и UPDATETEXT. Указатель должен содержать какое-либо значение, поэтому для правильной работы этих функций в колонке изначально должны содержаться данные. Для простоты мы запишем туда следующие значения:

insert into blob_test values(0x0,'My wife is Rosa','My son is Dima')

Значения для колонки типа image должны указываться в шестнадцатеричном формате, а для типов text и ntext это должны быть строки.

Для всех операторов DML, изменяющих данные, предыдущее значение всей строки сбрасывается в лог транзакций, однако для операторов WRITETEXT и UPDATETEXT это зависит от модели восстановления базы данных. Для модели Bulk logged данные не записываются в лог транзакций, вместо этого измененные страницы помечаются особым образом и записываются в архив лога транзакций при вызове соответствующей операции архивирования.

READTEXT

Этот оператор предназначен для блочного чтения больших текстовых и бинарных данных:

READTEXT { table.column text_ptr offset size } [ HOLDLOCK ]

Параметры:

table.column – таблица и колонка;

text_ptr – указатель, полученный с помощью функции textptr;

offset – смещение, с которого начинается чтение данных;

size – размер считываемых данных.

Пример:

declare @p binary(16) select @p = textptr(txt) from blob_test where id = 1 select case when @p is not null then '@p is valid' else '@p is invalid' end if @p is not null READTEXT blob_test.txt @p 0 4

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

declare @p binary(16) declare @idx int,@l int select @p = textptr(ntxt), @idx = patindex('%is%',ntxt)-1, @l = datalength(ntxt)/2-(patindex('%is%',ntxt)-1) from blob_test where id = 1 if @p is not null readtext blob_test.ntxt @p @idx @l

Здесь хочется отметить две особенности: patindex возвращает смещение относительно начала строки в символах, считая от единицы, тогда как readtext воспринимает смещение от нуля, а datalength возвращает длину данных в байтах, так что для типа ntext мы должны поделить ее на два.

Давайте задумаемся, что произойдет, если кто-либо попытается изменить данные между операциями получения указателя и его использования. Ничего особенного, просто SQL Server выдаст ошибку 7123, говорящую, что была попытка использовать недействительный указатель. Одной проверки на NULL оказывается недостаточно. Для проверки указателя на действительность нужно воспользоваться функцией textvalid. Однако эта проверка не избавляет нас от проблемы, а лишь помогает выявить ее. Нам нужно, чтобы для данного указателя соблюдалось условие повторяемого чтения. Этого проще всего добиться, использовав в запросе хинт REPEATABLEREAD. Перепишем пример следующим образом:

declare @p binary(16) declare @idx int,@l int begin tran select @p = textptr(ntxt), @idx = patindex('%is%',ntxt)-1, @l = datalength(ntxt)/2-(patindex('%is%',ntxt)-1) from blob_test (REPEATABLEREAD) where id = 1 if textvalid(@p) = 1 and @idx >= 0 and @l > 0 readtext blob_test.ntxt @p @idx @l commit

Теперь код написан «по всем правилам»:

на строку с идентификатором 1 накладывается коллективная блокировка, что предотвращает ее изменения из других транзакций;

смещение проверяется на отрицательные значения, так как функция patindex может вернуть 0, если не найдет шаблон;

длина считываемого текста также проверяется на неотрицательные значения.

Функция READTEXT не вернет вам всего объема данных. Размер максимально доступных данных, которые можно получить с помощью этой функции, равен @@textsize. По умолчанию это значение равно 4 Кб. Увеличить его можно с помощью функции set textsize. Для сброса переменной в значение по умолчанию установите размер, равный нулю.

WRITETEXT

Эта функция оставлена только для совместимости. Ее заменила более мощная UPDATETEXT, которую я рассмотрю позднее.

Вот синтаксис функции WRITETEXT:

WRITETEXT { table.column text_ptr } [ WITH LOG ] { data }

table.column – таблица и колонка;

text_ptr – указатель;

with log – игнорируется для SQL Server 2000;

Data – данные. Их размер не может превышать 120 Кб.

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

declare @p binary(16) begin tran select @p = textptr(img) from blob_test (updlock) where id = 1 if textvalid('blob_test.img',@p) = 1 writetext blob_test.img @p 0x4034 commit

Более подробно механизм блокировок в MS SQL Server и понятие уровней изоляции транзакций рассмотрены в предыдущем номере журнала.

UPDATETEXT

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

UPDATETEXT { table_name.dest_column_name dest_text_ptr } { NULL | insert_offset } { NULL | delete_length } [ WITH LOG ] [ inserted_data | { table_name.src_column_name src_text_ptr } ]

table_name.dest_column_name – таблица и колонка.

dest_text_ptr – указатель на обновляемую область.

insert_offset – смещение в байтах, по которому будут изменяться данные. Если указывается NULL, данные будут добавлены к текущим данным.

delete_length – количество удаляемых байт. Если указывается NULL, данные будут удалены от смещения до конца. Для вставки данных необходимо указать значение 0.

with log – не имеет значения на SQL Server 2000.

inserted_data – вставляемые данные.

table_name.src_column_name – таблица и колонка, откуда данные вставляются.