Файловые потоки

Если в таблице базы данных MS SQL требуется хранить большие двоичные данные, например, файлы с большими документами, аудио или видео файлы, то обычно для этого используются столбцы типа varbinary ( max ). Такой столбец может вместить до двух гигабайт данных. Внутренне он (при большой длине) не хранится в строке из-за недостатска места. На строке есть только указатель на корневую страницу типа LOB. Сама строка с двоичным значением дробится на отдельные страницы, которые образуют структуру бинарного дерева, по которому можно спускаться, начиная с корневой страницы, через указатели.

Несмотря на несомненные достоинства у этого способа хранения есть недостатки:

1. при обновлении больших файлов может увеличиться уровень фрагментации
2. увеличивается объем кэша
3. база данных может стать очень большой
4. доступ на чтение и запись больших файлов может стать медленным
5. нельзя сохранить файл, объем которого превышает 2 гигабайта

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

В MS SQL 2008 появились файловые потоки, которые призваны устранить эти недостатки. Файловые потоки позволяют хранить данные в виде отдельных файлов. Размер сохраняемых данных ограничен лишь доступным объмом на диске. При этом можно осуществлять доступ к данным как средствами Transact-SQL, как если бы мы хранили бинарник в столбце varbinary ( max ), так и средствами потокового доступа, используя средства Win32 Api. При потоковом доступе во многих случаях увеличивается скорость чтения и записи в файлы. Снижается нагрузка на оперативную память: буферный пул не используется при операциях через файловые потоки, используется системный кэш NT.
Операции чтения и записи происходят в контексте транзакции, логируются транзакционным логом. На время операции запись таблицы блокируется для других соединений.
Для работы с загруженными файлами средствами файловых потоков создается отдельная файловая группа, которая входит в бэкап базы. Это исключает проблемы администрирования и безопасности. Кроме того, файловые потоки интегрируются со многими другими копонентами Database Engine. Например, можно выполнять полнотекстовое индексирование данных filestream. Есть несущественные ограничения, например, нельзя использовать базу с файловой группой filestream в сеансе зеркального отображения. При работе с данными filestream в транзакции, доступен только уровень изоляции read committed.
Microsoft рекомендует использовать файловые потоки для ускорения потокового доступа, если есть файлы, размер которых превышает 1 мегабайт.
Приступим к разработке таблицы, хранящей данные filestream и изучим возможности операций с ней. Сперва требуется включить доступ к технологии filestream. На серверном компьютере нажимаем Пуск->Все программы->Microsoft SQL Server 2012->Средства настройки->Диспетчер конфигураций. В левом окне выбираем службы SQL Server, в правом окне выбираем службу нашего сервера, нажимаем правую кнопку мыши, выбираем свойства. В окне свойств выбираем вкладку FILESTREAM:

Верхняя галочка дает возможность осуществлять доступ к данным FILESTREAM средствами Transact-SQL, нажав следующую галочку, можно работать с filestream средствами потокового доступа. Последняя галочка дает возможность потокового доступа с удаленных компьютеров, при этом выбирается имя общей папки. Проставив все галочки, жмем OK. Далее необходимо выполнить код:
exec sp_configure 'filestream_access_level', 2
reconfigure

Перезагружаем службу сервера. Создадим базу данных Files. Пусть она сразу не содержит файловую группу filestream:
create database Files

Теперь требуется добавить файловую группу filestream:

alter database Files add filegroup FilesWrk contains filestream

alter database Files add file ( name = FilesData, filename = 'C:\Users\edynak\Desktop\DataFiles' ) to filegroup FilesWrk

При добавлении файловой группы мы указали ключевое слово filestream. При добавлении файла к файловой группе требуется указать только логическое и физическое имена. Физическое имя это директория. В момент выполнения кода она не должна существовать. Должна существовать ее родительская папка. Файловых групп filestream может быть несколько, одну из них можно пометить как используемую по умолчанию с помощью ключевого слова default. Иначе файловой группой filestream, используемой по умолчанию, станет первая созданная файловая группа filestream.
После выполнения кода директория DataFiles появилась на рабочем столе, в ней есть пустая директория $FSLOG и файл filestream.hdr:

Папка $FSLOG очень важна, она логирует операции с файлами файловой группы filestream, играет роль транзакционного лога, и ее данные используется при бэкапировании и восстановлении базы.
Теперь создадим две таблицы:

use Files

create table dbo.FilesNoStream

(

       iFileId      int identity ( 1, 1 )       not null,

       guiRowId     uniqueidentifier rowguidcol not null constraint DF_FilesContAdd_guiRowId default ( newsequentialid() ),

       vcFileName   varchar ( 255 )             not null,

       varFileCont  varbinary ( max )           not null,

       constraint PK_FilesContAdd_iFileId primary key clustered ( iFileId asc ) on [PRIMARY],

       constraint AK_FilesContAdd_vcFileName unique nonclustered ( vcFileName asc ) on [PRIMARY],

       constraint AK_FilesContAdd_guiRowId unique nonclustered ( guiRowId asc ) on [PRIMARY]

) on [PRIMARY]

create table dbo.FilesStream

(

       iFileId      int identity ( 1, 1 )        not null,

       guiRowId     uniqueidentifier rowguidcol  not null constraint DF_FilesCont_guiRowId default ( newsequentialid () ),

       vcFileName   varchar ( 255 )              not null,

       varFileCont  varbinary ( max ) filestream not null,

       constraint PK_FilesCont_iFileId primary key clustered ( iFileId asc ) on [PRIMARY],

       constraint AK_FilesCont_vcFileName unique nonclustered ( vcFileName asc ) on [PRIMARY],

       constraint AK_FilesCont_guiRowId unique nonclustered ( guiRowId asc ) on [PRIMARY]

) filestream_on FilesWrk

Первая таблица хранит двоичные данные с помощью традиционного подхода, вторая имеет столбец типа varbinary ( max ), отмеченный опцией filestream. В конце определения второй таблицы указано ключевое слово filestream_on для выбора файловой группы filestream, в которой будут размещены данные. Если бы это слово не было указано, то использовалась бы файловая группа filestream, используемая по умолчанию. У таблицы есть столбец типа uniqueidentifier, помеченный свойством rowguidcol, на нем создан уникальный индекс. Это обязательно для таблицы с filestream-столбцом. Он используется на странице для того, чтобы по строке определить расположение файла. Чтобы не думать о том какие значения хранить в этом столбце я сделал для него значение по умолчанию, которое генерирует новый идентификатор-guid. Но поскольку этот столбец входит как ведущий столбец в индекс, неправильно использовать в качестве значения по умолчанию функцию newid. Вместо нее используется функция newsequentialid, которая случайным образом генерирует идентификаторы, которые все время возрастают.
После создания таблиц в папке DataFiles появилась директория с именем в стиле guid. Такая директория появляется для каждой секции каждой таблицы, имеющей данные filestream (если таблица не секционирована, то появляется одна папка для этой таблицы). Внутри новой директории есть еще одна директория с guid-именем, которая соответствует столбцу с опцией filestream:

С таблицей FilesStream теперь можно производить все привычные операции: select, insert, update, delete:

insert into dbo.FilesStream ( vcFileName, varFileCont )

       values ( 'test1', cast ( 'data1' as varbinary ( max ) ) )

select cast ( varFileCont as varchar ( max ) ), *

from dbo.FilesStream

В директории, соответствующей столбцу, появился файл. Если его открыть с помощью Notepad, то мы увидим вставленные данные:

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

update dbo.FilesStream

       set varFileCont = cast ( 'data2' as varbinary ( max ) )

       where iFileId = 1

Это говорит о том, что при обновлении существующий файл не меняется, а создается новый. Старый же файл со временем будет удален сборщиком мусора, который работает как отдельный поток и периодически проверяет наличие данных для удаления. Если база данных имеют не простую модель восстановления, то сборщик мусора активируется после того как дважды произошло резервное копирование журнала транзакций базы. Технология сборщика мусора во многом была заимствована компонентом In Memory OLTP, когда происходит отображение данных из таблиц оперативной памяти на файлы данных и дельт (которые находятся в файловой группе с опцией memory_optimized_data).
Создадим хранимую процедуру для загрузки бинарных данных в обычную таблицу:

use Files

go

if sessionproperty ( 'quoted_identifier' ) = 0

       set quoted_identifier on

go

if sessionproperty ( 'ansi_nulls' ) = 0

       set ansi_nulls on

go

if object_id ( 'dbo.SaveFile', 'P' ) is null

       exec ( 'create proc dbo.SaveFile as return 1' )

go

alter proc dbo.SaveFile

(

       @vcFileName varchar ( 800 ),

       @varFile varbinary ( max )

)

as

begin

       set nocount, xact_abort on

       declare @ShortName varchar ( 255 ) = reverse ( left ( reverse ( @vcFileName ), charindex ( '\', reverse ( @vcFileName ), 1 ) - 1 ) )

      

       set @ShortName = left ( @ShortName, charindex ( '.', @ShortName, 1 ) - 1 )

      

       if exists

       (

             select 1

             from dbo.FilesNoStream

             where vcFileName = @ShortName

       )

       begin

             raiserror ( N'Файл с именем "%s" уже существует.', 16, 1, @ShortName )

             return

       end

      

       insert into dbo.FilesNoStream ( vcFileName, varFileCont )

             values ( @ShortName, @varFile )

       select scope_identity() iFileId

end

go

Следующая хранимая процедура выполняет поиск полного пути файла для строки с данными filestream. Этот путь требуется для получения дескриптора файла при потоковом доступе к нему:

use Files

go

if sessionproperty ( 'quoted_identifier' ) = 0

       set quoted_identifier on

go

if sessionproperty ( 'ansi_nulls' ) = 0

       set ansi_nulls on

go

if object_id ( 'dbo.GetFilePath', 'P' ) is null

       exec ( 'create proc dbo.GetFilePath as return 1' )

go

alter proc dbo.GetFilePath

(

       @vcFileName varchar ( 300 )

)

as

begin

       set nocount, xact_abort on

       declare @ShortName varchar ( 255 ) = reverse ( left ( reverse ( @vcFileName ), charindex ( '\', reverse ( @vcFileName ), 1 ) - 1 ) )

      

       set @ShortName = left ( @ShortName, charindex ( '.', @ShortName, 1 ) - 1 )

       select varFileCont.PathName() [path]

       from dbo.FilesStream

       where vcFileName = @ShortName

end

go

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

use Files

go

if sessionproperty ( 'quoted_identifier' ) = 0

       set quoted_identifier on

go

if sessionproperty ( 'ansi_nulls' ) = 0

       set ansi_nulls on

go

if object_id ( 'dbo.GetFilestreamTranContext', 'P' ) is null

       exec ( 'create proc dbo.GetFilestreamTranContext as return 1' )

go

alter proc dbo.GetFilestreamTranContext

as

begin

       set nocount, xact_abort on

       select get_filestream_transaction_context() TranContext

end

go

Напишем консольное приложение, которое будет вставлять содержимое одного большого файла в таблицы FilesNoStream и FilesStream. В приложении будет 2 статических метода. Первый будет использовать хранимую процедуру dbo.SaveFile для вставки данных в dbo.FilesNoStream, второй - потоковый доступ для вставки в таблицу dbo.FilesStream. Приложение будет получать через командную строку имя загружаемого файла и признак используемого метода. Код приложения:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.IO;

namespace DbProject

{

    class FilesData

    {

        static void NoStreamOper(SqlConnection cn, string FileName)

        {

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = cn;

            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            FileStream fs = null;

            try

            {

                fs = File.OpenRead(FileName);

                byte[] bytes = new byte[fs.Length];

                fs.Read(bytes, 0, Convert.ToInt32(fs.Length));

                Console.WriteLine(System.DateTime.Now);

                cmd.CommandTimeout = 1000000000;

                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                cmd.CommandText = "dbo.SaveFile";

                cmd.Parameters.Add("@vcFileName", System.Data.SqlDbType.VarChar, 800).Value = FileName;

                cmd.Parameters.Add("@varFile", System.Data.SqlDbType.VarBinary, -1).Value = bytes;

                cmd.ExecuteNonQuery();

               

                Console.WriteLine(System.DateTime.Now);

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex.ToString());

            }

            finally

            {

                if (fs != null)

                {

                    fs.Close();

                    fs.Dispose();

                }
            }

        }

        static void StreamOper(SqlConnection cn, string FileName)

        {

            SqlCommand cmd = new SqlCommand();

            string path;

            cmd.Connection = cn;

            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            SqlFileStream fstream = null;

            FileStream fs = null;

            try

            {

                fs = File.OpenRead(FileName);

                byte[] bytes = new byte[fs.Length];

                fs.Read(bytes, 0, Convert.ToInt32(fs.Length));

                Console.WriteLine(System.DateTime.Now);

                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                cmd.CommandText = @"dbo.GetFilePath";

               

                SqlParameter prm = new SqlParameter();

                prm.ParameterName = @"@vcFileName";

                prm.SqlDbType = System.Data.SqlDbType.VarChar;

                prm.Direction = System.Data.ParameterDirection.Input;

                prm.Value = FileName;

                cmd.Parameters.Add(prm);

               

                SqlDataReader read = cmd.ExecuteReader();

                read.Read();

               

                path = read[0].ToString();

                read.Close();

               

                SqlTransaction tran = cn.BeginTransaction("FilesTran");

                cmd.Transaction = tran;

               

                cmd.Parameters.Clear();

                cmd.CommandText = "dbo.GetFilestreamTranContext";

                read = cmd.ExecuteReader();

                read.Read();

               

                byte[] tranCont = (byte[])read[0];

                read.Close();

               

                fstream = new SqlFileStream(path, tranCont, FileAccess.ReadWrite);

               

                fstream.Write(bytes, 0, bytes.Length);

                fstream.Close();

                cmd.Transaction.Commit();

                Console.WriteLine(System.DateTime.Now);

            }

            catch (Exception ex)

            {

                if (cmd.Transaction != null)

                {

                    cmd.Transaction.Rollback();

                }

                Console.WriteLine(ex.ToString());

            }

            finally

            {

                if (fs!=null)

                {

                    fs.Close();

                    fs.Dispose();

                }

                if (fstream != null)

                {

                    fstream.Close();

                    fstream.Dispose();

                }

            }
        }

        static void Main(string[] args)

        {

            string DbFile = args[0], AccType = args[1];

            SqlConnection cn = new SqlConnection(@"Data Source=EDYNAK\SHADOW;Database=Files;Trusted_Connection=Yes");

            cn.Open();

           

            if (AccType == "nostream")

            {

                NoStreamOper(cn, DbFile);

            }

            else

            {

                StreamOper(cn, DbFile);

            }

            cn.Close();

            cn.Dispose();

            Console.ReadLine();

        }

    }

}
В методе StreamOper мы сначала открываем файл, который будет загружаться в базу. Затем с помощью хранимой процедуры получаем имя файла, соответствующего строке таблицы. Затем открывается транзакция. Когда есть транзакция, то функция get_filestream_transaction_context возвратит ее идентификатор для связки потоковых операций с транзакцией. Затем экземпляр класса SqlFileStream вызывает метод Wrirte, после чего идет фиксация транзакции.
Предварительно сделаем строку в FilesStream пустой:

update dbo.FilesStream

       set varFileCont = cast ( '' as varbinary ( max ) )

       where iFileId = 1

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

create database TestBigData

       on primary

       (

             name = Data,

             filename = 'C:\Users\edynak\Desktop\test1.mdf',

             size = 500 Mb,

             filegrowth = 100 Mb,

             maxsize = unlimited

       )

alter database TestBigData set offline

Проведем несколько испытаний. При первой загрузке через метод NoStreamOper время загрузки в базу составило 196 секунд. При этом уровень потребления памяти сервером вырос в несколько раз. Загрузка через потоковый доступ прошла за 5 сек.

Перед каждым последующим тестом проводим очистку данных:

update dbo.FilesStream

       set varFileCont = cast ( '' as varbinary ( max ) )

delete dbo.FilesNoStream

Проведем 10 таких тестов. В таблице представлены результаты скосрости загрузки данных различными методами:

Номер теста	Время работы при потоковом доступе (сек.)	Время работы при обычном доступе (сек.)
1	5	196
2	7	19
3	6	19
4	6	23
5	5	13
6	5	11
7	18	16
8	5	10
9	14	15
10	5	12
11	6	23

Мы видим, что при последующих тестах время загрузки методом StreamOper в среднем в 2-3 раза быстрее чем при загрузке методом NoStreamOper. В тестах 2-11, в ходе работы метода NoStreamOper, потребление памяти сервером увеличивалось на 600 Кб. Для работы метода StreamOper этот показатель составил всего около 40 Кб, в 15 раз меньше.

Мы видим, что практически всегда запись в таблицу файловой группы filestream при потоковом доступе в 2-3 раза быстрее чем при вставке в обычную таблицу. В то же время запись в обычную таблицу в первый раз длилась на порядок дольше. А при потоковом доступе скорость вставки не зависела от того в первый или не в первый раз происходила вставка и составляла около 6 секунд. Складывается ощущение, что на ситуацию влияет кэширование, и, если сервер не аллокировал достаточно памяти, то при работе метода NoStreamOper потребуется дополнительная работа на то, чтобы эту память загрузить. Чтобы проверить это предположение, я перезагрузил сервер, и сразу после этого запустил загрузку файла методом StreamOper, а затем методом NoStreamOper. Результаты оказались поразительны: при работе метода StreamOper потребление памяти выросло на жалкие 7 мегабайт (со 178 до 185 Мегабайт). А при работе метода NoStreamOper объем памяти, используемой sqlservr.exe вырос со 187 Мб до 1363 Мб, на 1176 мегабайт, что в 168 раз больше чем при использовании потокового доступа! Время работы метода StreamOper составило при этом 5 сек., а метода NoStreamOper - 28 секунд, в 5.6 раз дольше. Поэтому можно утверждать, что применение файловых потоков уместно в средах, где производятся частые загрузки новых документов объем которых зачастую составляет несколько десятков или сотен мегабайт. Применение файловых потоков сможет увеличить скорость записи файлов в таблицу и снизить нагрузку на оперативную память.

Результаты исследования чтения бинарных данных оказались даже более поразительными. Проверим скорость работы таких запросов:

declare @data varbinary ( max )

select @data = substring ( varFileCont, 100000000, 400000000 )

from Files.dbo.FilesStream

where vcFileName = 'test1'

go

select getdate ()

declare @data varbinary ( max )

select @data = substring ( varFileCont, 100000000, 400000000 )

from Files.dbo.FilesNoStream

where vcFileName = 'test1'

select getdate ()

Предварительно я начал запускать эти запросы только после перезапуска сервера, когда память еще не зарезервирована. Первый запрос отработал за 13 секунд и объем памяти возрос до 900 Мб. После второй перезагрузки сервера второй запрос работал почти в два раза дольше, 27 сек., и объем памяти увеличился то 1.4 Гб. Однако когда объем памяти, используемой сервером превысил 1 Гб, то при последующих запусках первый запрос работал 3 сек., а второй - 1.5 сек. Посмотрим, что происходит при потоковом чтении. Доработаем код метода Main:

        static void Main(string[] args)

        {

            string DbFile = args[0], AccType = args[1];

            SqlConnection cn = new SqlConnection(@"Data Source=EDYNAK\SHADOW;Database=Files;Trusted_Connection=Yes");

            cn.Open();

           

            if (AccType == "nostream")

            {

                NoStreamOper(cn, DbFile);

            }

            if (AccType == "stream")

            {

                StreamOper(cn, DbFile);

            }

            if (AccType == "streamread")

            {

                StreamReadOper(cn, DbFile);

            }

            cn.Close();

            cn.Dispose();

            Console.ReadLine();

        }

Добавим также метод StreamReadOper:

        static void StreamReadOper(SqlConnection cn, string FileName)

        {

            SqlCommand cmd = new SqlCommand();

            string path;

            int numBytes;

            byte[] buffer = new byte[400000000];

            cmd.Connection = cn;

            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            SqlFileStream fstream = null;

            try

            {

                Console.WriteLine(System.DateTime.Now);

                Decimal curTime;

                curTime = System.DateTime.Now.Ticks/10000;

                Console.WriteLine(curTime.ToString());

                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                cmd.CommandText = @"dbo.GetFilePath";

                SqlParameter prm = new SqlParameter();

                prm.ParameterName = @"@vcFileName";

                prm.SqlDbType = System.Data.SqlDbType.VarChar;

                prm.Direction = System.Data.ParameterDirection.Input;

                prm.Value = FileName;

                cmd.Parameters.Add(prm);

                SqlDataReader read = cmd.ExecuteReader();

                read.Read();

                path = read[0].ToString();

                read.Close();

                SqlTransaction tran = cn.BeginTransaction("FilesTran");

                cmd.Transaction = tran;

                cmd.Parameters.Clear();

                cmd.CommandText = "dbo.GetFilestreamTranContext";

                read = cmd.ExecuteReader();

                read.Read();

                byte[] tranCont = (byte[])read[0];

                read.Close();

                fstream = new SqlFileStream(path, tranCont, FileAccess.Read);

                fstream.Seek(100000000L, SeekOrigin.Begin);

                numBytes = fstream.Read(buffer, 0, buffer.Length);

               

                fstream.Close();

                cmd.Transaction.Commit();

                Console.WriteLine(System.DateTime.Now);

                curTime = System.DateTime.Now.Ticks / 10000;

                Console.WriteLine(curTime.ToString());

            }

            catch (Exception ex)

            {

                if (cmd.Transaction != null)

                {

                    cmd.Transaction.Rollback();

                }

                Console.WriteLine(ex.ToString());

            }

            finally

            {

                if (fstream != null)

                {

                    fstream.Close();

                    fstream.Dispose();

                }

            }

        }

Этот метод читает те же данные. Метод Seek ищет место в файле, с которого надо выполнять чтение (с символа под номером 100000000). Чтение осуществляется в массив, длина которого составляет те же 400000000. Запустив консольное приложение с подходящими параметрами командной строки, обнаруживается, что для чтения серверу вообще не нужна дополнительная память. А сама операция длится от 200 до 400 миллисекунд, то есть в 3.75-7.4 раз быстрее!