Хранение графики в SQL-сервере

В инете между разными группами программеров постоянно ведутся различные религиозные войны. Религиозные - потому, что участвующих в них программеров уже ВСЕ РАВНО ни в чем переубедить не получится никогда.

Одна из таких бесконечных религиозных войн - месторасположение хранилища бинарных файлов. Участником таких войн - еще со времен появления SQL 6,5 - являюсь и я. Мое кредо - всегда! Всегда и все - только в базе! Долой наследников дискеток - файловые системы NTFS, FAT32, HPFS - ну и все остальные, не упомянутые тоже !!!

Все мои проги, описанные на этом сайте, хранят все бинарники только в базе. Например, ProjectExplorer (моя альтернатива TEAM Foundation Server). Все прочие проги, не упомянутые на моем сайте, но которые делал я - например различные CMS электронных магазинов, которыми я занимался несколько лет - также хранят все бинарники в базе.

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

Для начала сошлемся на "авторитные" источники. Авторитетные взято в кавычки - понятно почему - ангажированность MSDN и микрософтовских коперфильдов типо Экспозито - вряд-ли вызывает у серьезных людей какие-то сомнения, но тем не менее для начала две цитаты.

Экспозито. ASP NET 2. Углубленное изучение. Стр 349.

Используйте файловую систему, когда имеете дело с изображениями, размер которых превышает 1МБ, а также когда изображений много, несколько сот тысяч и более. В граничном решении оба решения равно допустимы. SQL обеспечивает очень быстрое чтение данных с диска и имеет прекрасный механизм кеширования, но протокол TDS (Tabular Data Stream - поток табличных данных) явно не рассчитан на передачу больших BLOB-обьектов.

Ну и вторая цитата. SQL 2005. Экзамен 70-431. Стр 121.

Чтобы найти строку в таблице, содержащей 2,5 миллиона строк, SQL Server должен просмотреть всего три страницы данных. И лишь когда в таблице станет более 300 миллионов строк, SQL Server придется просматривать четыре страницы для поиска нужной строки.

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

• Не могу себе вообще представить каталог в файловой системе из 300 миллионов файлов. Если даже такой каталог создать возможно, и файловая система не сдохнет от таких обьемов - выборка одного файла из нее будет продолжать целую вечность - а не мгновения, как в SQL. Ибо работа с такими обьемами (не говоря уже о меньших) - это и есть предназначение SQL сервера.
• В большинстве случаев SQL-серверу вообще не придется обращаться к диску - ибо под размеры буферов в SQL отводится весь доступный обьем ОЗУ (по умолчанию). Это размер при желании можно гибко регулировать. Никакого сравнения по гибкости и мощности с механизмом кеширования IIS. Ну не говоря уж о наследниках дискеток - разве для чтения из NTFS мы можем хоть как-то задать например 1Гигабайтный буфер в OЗУ? Сравнивать мехнаизмы кеширования голой файловой системы (и даже с надстройкой в виде буферизации IIS) - с механизмами кеширования SQL просто смешно. Единственный практический минус работы с такими большими кешами - ПЕРВЫЙ ЗАПРОС ИДЕТ ВСЕГДА МЕДЛЕННО (хотя все равно несравненно быстрее чем из файловой системы). Потом все просто летает. Но ПЕРВЫЙ запрос в SQL может и в 10 раз (а может и в 100 раз) медленнее пройти чем последующие.
• От указанных в букварике базовых показателей производительности SQL мы можем уйти ЕЩЕ ВЫШЕ. А что нам предлагают наследники дискеток? RAID? Но как им управлять? Номерочками - RAID5 - RAID10? Механизмы секционирования таблиц в SQL просто смешно сравнивать с технологиями чередования например или зеркалирования NTFS.
• Что нам мешает мультипоточную подкачку больших файлов организовать из секционированных таблиц и делать Response.Write в поток браузера хоть через сто байт (ну примерно аналогично пиринговым сетям)? А где аналогичные механизмы (хоть сколько нибудь похожие на это - не только в голой NTFS, но даже с надстройкой в виде IIS)? Кстати мы совсем не упомянули УПРЕЖДАЮЩЕЕ чтение оптимизатора SQL, который предвосхищая следующие запросы к данным - предварительно их считывает в свои буфера в все свободное время ПРО ЗАПАС.
• То, что сказал Экспозито насчет маркеров в несколько байт при передаче потоковых данных SQL - я не считаю необходимым вообще принимать во внимание. Ну какой размер этих маркеров - 0,05% ? Или даже меньше. Не вижу в этом вообще аргумента.
• Это все касается пока производительности. Теперь посмотрим на другие не менее важные характеристики SQL. Прежде всего для файловой системы - а учет всех файлов, понятное дело - все равно в базе - НИКАК НЕ ВОЗМОЖНО ОБЕСПЕЧИТЬ ЦЕЛОСТНОСТЬ ДАННЫХ.
• Наконец, реализация механизма доступа к файловой системе. Что нам предлагает убогая NTFS? Разграничение по правам доступа (а они у на без имперсонализации всегда одни - от имени учетной записи ASP.NET) - да плюс параметр в конфиге GET/POST на некие учетные записи юзеров. Это все. Это конечно даже обсуждать несерьезно. Какое по этой позиции может быть сравнение с теми способами доступа, которые мы можем реализовать САМИ в своем коде при доступе к записям SQL.

Список этот бесконечный. Любая характеристика SQL в разы провосходит просто виндузню. Например защищенность файлов в бестолковой шифрованной файловой системе EFS и зашифрованные столбцы в SQL. Пробовали сравнить удобство управления ключами шифрования, например - в том и другом случае?

Единственная ложка дегтя, которая меня огорчает - бестолковость MS. И отсутствие видения целостной концепции - куда движется их виндузня. Поясню конкретнее - они например уже догадались отображать весь WEB-конфиг в виде таблицы, но так и не догадались чтобы IIS мог вычитывать откомпилированные DLL непосредственно из SQL. Хотя предусмотрели все-все-все для хранения сборок внутри SQL. И в самом движке ASP2 предусмотрели VirtualPathProvider, который мог бы вычитывать библиотеки из SQL. Но не уложили нормальный оттестированный класс в NET и не подключили его грамотно к IIS. Такая бестолковость - в данном случае полнейшее отсутствие интеграции IIS и SQL вызывает отвращение и жалость к кампании, которая скупила и доработала так много замечательных продуктов - но так и не сумела их соединить в ЕДИНЫЙ комплекс.

Еще одна огорчительность этой бестолковой компании - отсутствие работы с SQL - как местом хранения исходных текстов. Хотя куча их же продуктов прекрасно умеет это делать - например TEAM Foundation Server, но VS2005 умеет хранить свои проекты кроме файловой системы - лишь на FTP и во Front Page Extension. Обидно, блин. ТЕАМ великолепно хранит исходники в SQL, а VS2005 не может их там хранить. В SQL есть все для хранения там бинарных сборок - но опять же бестолковая VS2005 не умеет уложить откомпилированный сайт в SQL. Хотя просто отдельно взятые сборки превосходно туда укладывает. IIS управляется даже таблицами уже - но считать конфиг из SQL не может... Ну как относиться после этого к MS?

Если же пойти еще дальше - то непонятно, почему к исполняемым файлам SQL вообще не докрутить бутовый загрузчик и не обходится вообще БЕЗ гребаной виндузни? Наверное для MS непосильны стандартные идеи, как например у компании ACRONIS, у которой все продукты работают либо непосредственно бутаясь с компактов либо под виндузней. Никогда, наверное, у них не хватит ума докрутить бутовый загрузчик к SQL и сделать как у ACRONIS и тысяч других компаний... Эх, ну почему же дурни не распределяются РАВНОМЕРНО по всем компаниям...

Если пойти еще дальше в анализе бестолковых идей MS - то мое отвращение вызывает в первую очередь попытки нагрузить SQL несвойственными - РАСЧЕТНЫМИ задачами. Например зиповкой/раззиповкой данных. Этим ведь крадется драгоценнейшее время у проуессора, у той же например предподкачки в буфера предсказанных будущих запросов к дискам. Есть и другие тупизмы, которыми MS нагружает SQL. Ну например, введенной в SQL2008 конструкцией Select * from &Table. Вот именно в таких дебильных контрукциях - все преимущества SQL будут подсаживаться просто на нет - и он будет просто тупо превращаться в файловую систему. Без индексов, без предсказаний, без предкомпилированных планов и тд. Такие вот ПОПУЛИСТСКИЕ векторы развития SQL - не в ту сторону - меня огорчают донельзя.

Теперь после этого небольшого теоретического экскурса - рассмотрим пару простейших примеров работы с бинарными данными в SQL.

Надо сказать, что я с момента появления SQL 6,5 старался работать с бинарниками только в SQL. У меня на хомяке множество вариантов подобного рода кода лежит - ну вот хотя бы проект, который я делал для MS - естественно тут все рисунки в базе. Но для начала мы рассмотрим - как закачать фотку ПРЯМО В ПРОФИЛЬ ASP.NET. И вывести ее оттуда. Это вариант для тех - кому ЛЕНЬ даже свою структуру базы продумывать, для тех кто просто использует стандартные аутентификационные базы.

Для начала, как обычно - определяем профиль. И сохраняем аплоадером в профиль фотографию вот таким классом.

00001: 'небольшой интерфейсик для сохранения/вытаскивания байтов рисунка в типизированный профиль, где допускается только строчное поле
00002: Public Class ProfileImage
00003:     Public Shared Function SaveImageToProfileObj(ByVal Uploader As Web.UI.WebControls.FileUpload, ByRef MyTypedProdfileObject As ProfileCommon) As Integer
00004:             Dim Buf1(Uploader.PostedFile.InputStream.Length) As Byte
00005:             Dim RealReadBytes As Integer = Uploader.PostedFile.InputStream.Read(Buf1, 0, Uploader.PostedFile.InputStream.Length)
00006:             MyTypedProdfileObject.usPhotoLen = RealReadBytes
00007:             Dim XMLSerializer As New System.Xml.Serialization.XmlSerializer(GetType(System.Byte()))
00008:             Dim XMLString As New IO.MemoryStream
00009:             XMLSerializer.Serialize(XMLString, Buf1)
00010:             Dim AnsiConverter As New System.Text.ASCIIEncoding
00011:             Dim XmlBase64 As String = AnsiConverter.GetString(XMLString.GetBuffer)
00012:             Dim StartPos As Integer = XmlBase64.IndexOf("<base64Binary>")
00013:             Dim EndPos As Integer = XmlBase64.IndexOf("</base64Binary>")
00014:             MyTypedProdfileObject.usPhoto = XmlBase64.Substring(StartPos + Len("<base64Binary>"), EndPos - StartPos - Len("<base64Binary>"))
00015:             XMLSerializer = Nothing
00016:             AnsiConverter = Nothing
00017:             XMLString.Close()
00018:             XMLString = Nothing
00019:             Return RealReadBytes
00020:     End Function
00021:     Public Shared Function GetImageFromProfileObj(ByVal CurrentUserName As String) As Byte()
00022:         Dim MyTypedProfileObject As ProfileCommon = Profile.ProfileBase.Create(CurrentUserName)
00023:         Dim RestoreXML As String = "<?xml version=""1.0""?>" & vbCrLf & "<base64Binary>" & MyTypedProfileObject.usPhoto & "</base64Binary>"
00024:         Dim AnsiConverter As New System.Text.ASCIIEncoding
00025:         Dim XMLStream As New IO.MemoryStream(AnsiConverter.GetBytes(RestoreXML))
00026:         Dim DeSerializer As New System.Xml.Serialization.XmlSerializer(GetType(System.Byte()))
00027:         Dim RestoredBytes() As Byte = DeSerializer.Deserialize(XMLStream)
00028:         DeSerializer = Nothing
00029:         AnsiConverter = Nothing
00030:         XMLStream.Close()
00031:         XMLStream = Nothing
00032:         Return RestoredBytes
00033:     End Function
00034: End Class

В чем тут фишка? А в том, что надо предварительно сериализовать бинарный поток в строку. Что и делается в этом классе. Кстати эта сериализация в данном случае происходит почти без прироста длины файла.

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

< THANKS ME>