SQL Server の FILESTREAM を利用して画像を登録する
MovieJukeBoxで扱う画像は、すべてでテーブルのVARBINARY(MAX)列に保存している。SSD上にDBを構築しているので、画像表示の遅れなど、さほど気になるところはないが、FILESTREAMをしようすることで、さらにパフォーマンスアップできるか確認してみる。
FILESTREAMを使用するにあたり期待すること
FILESTREAMではSQL Server 2014 の1DBあたり10GB容量制限にカウントする容量に含まれない
SQL Server 2014 Express を使用しているので、1DBあたりの容量制限10GBがある。既に10GBを超過しているので、画像を登録するテーブルはDBを分けて配置している。画像をFILESTREAMで管理することで、容量を抑えることができるので、DBを分割する必要がなくなる。
映画のポスター画像を格納するテーブル「TMDbPoster」。
左はテーブルに登録している方で、あらかじめ10GBを確保していて、既に3.6GBも消費している。
右はFILESTREAMで管理している方で、画像情報をDB内に保持しないから、約4MBしか使用していない。
パフォーマンス向上はあまり期待できない。平均画像サイズが1MBと超えないと高速化しないみたいだ。
FILESTREAM (SQL Server)
http://msdn.microsoft.com/ja-jp/library/gg471497.aspx
（引用）
比較的小さなオブジェクトの場合は、varbinary(max) BLOB をデータベースに格納する方が一般に高いストリーミング パフォーマンスが得られます。
SQL Server 2008 で FILESTREAM を使用してプログラミングを行う
http://msdn.microsoft.com/ja-jp/magazine/dd695918.aspx
FILESTREAMを有効化する
デフォルトインストールではFILESTREAMは無効化されているので、有効化する。
SQL Server 構成マネージャーを開き、SQL Server のインスタンスをダブルクリック
FILESTREAM タブで下図のように、Transact-SQL アクセスに対して FILESTREAM を有効にする にチェックをつける。
Management Studio を開き、サーバのプロパティを開く。
FILESTREAMアクセスレベルで「有効な Transact-SQL アクセス」を選択する。
インスタンスを再起動する。
FILESTREAMで画像ファイルを登録するDBを新規に作成
左上の「ページの選択」ペインでファイルグループを選び、MOVIEというファイルグループを追加する。
左上の「ページの選択」ペインで全般を選び、論理名を MOVIE にし、実態ファイルの場所を D:\FILESTREAM にしてFILESTREAMデータを追加する。
D:\FILESTREAM に MOVIE という名のフォルダができる。この中に画像データが格納されるらしい。
ここはSQL Serverが管理する場所なので、アクセス権がない。
FILESTREAMで画像ファイルを登録するテーブルを作成
FILESTREAMで画像を保存するテーブル「映画マスタ」を作成する
CREATE TABLE [dbo].[映画マスタ](
[ID] [int] IDENTITY(1,1) NOT NULL,
[IDFILE] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ファイル名] [nvarchar](300) NULL,
[タイトル] [nvarchar](300) NULL,
[日本語タイトル] [nvarchar](300) NULL,
[ジャンル] [nvarchar](300) NULL,
[シリーズ] [nvarchar](300) NULL,
[主演] [nvarchar](300) NULL,
[出演] [nvarchar](300) NULL,
[監督] [nvarchar](300) NULL,
[公開年] [nvarchar](4) NULL,
[公開年月日] [nvarchar](10) NULL,
[画像] [varbinary](max) FILESTREAM NULL,
[画像ファイル名] [nvarchar](300) NULL,
[表示順] [int] NULL,
[評価] [nvarchar](1) NULL,
[TVシリーズ] [nvarchar](1) NULL,
[R指定] [int] NULL,
[TMDbID] [int] NULL,
[壁紙画像] [varbinary](max) FILESTREAM NULL,
[壁紙画像パス] [nvarchar](256) NULL,
[TMDb取込日] [nvarchar](8) NULL,
[TMDb取込時間] [nvarchar](8) NULL,
[YouTube] [nvarchar](50) NULL,
CONSTRAINT [PK_映画マスタ] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] FILESTREAM_ON [MOVIE],
UNIQUE NONCLUSTERED
(
[IDFILE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [MOVIE]
データ移行
テーブル内に画像を保存している既存の「MOVIE.dbo.映画マスタ」から、FILESTREAMで画像を保存する「FSTEST.dbo.映画マスタ」にデータを移行する。1列目の ID は Identity なので、一時的に値を追加できるようにしている。2分10秒でコピー完了。
SET IDENTITY_INSERT FSTEST.[dbo].[映画マスタ] ON
INSERT INTO [FSTEST].[dbo].[映画マスタ]
(
[ID]
,[IDFILE]
,[ファイル名]
,[タイトル]
,[日本語タイトル]
,[ジャンル]
,[シリーズ]
,[主演]
,[出演]
,[監督]
,[公開年]
,[公開年月日]
,[画像]
,[画像ファイル名]
,[表示順]
,[評価]
,[TVシリーズ]
,[R指定]
,[TMDbID]
,[壁紙画像]
,[壁紙画像パス]
,[TMDb取込日]
,[TMDb取込時間]
,[YouTube]
)
SELECT
[ID]
,NEWID()
,[ファイル名]
,[タイトル]
,[日本語タイトル]
,[ジャンル]
,[シリーズ]
,[主演]
,[出演]
,[監督]
,[公開年]
,[公開年月日]
,[画像]
,[画像ファイル名]
,[表示順]
,[評価]
,[TVシリーズ]
,[R指定]
,[TMDbID]
,[壁紙画像]
,[壁紙画像パス]
,[TMDb取込日]
,[TMDb取込時間]
,[YouTube]
FROM [MOVIE].[dbo].[映画マスタ]
SET IDENTITY_INSERT TEST.[dbo].[映画マスタ] OFF
画像表示用ストアドプロシージャ
FILETREAMで画像取得する「getPicture_FileStream」を作成。
USE [MOVIE]
GO
/****** Object: StoredProcedure [dbo].[getPicture_FileStream] Script Date: 2014/10/25 19:05:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[getPicture_FileStream]
@ID int
AS
BEGIN
SET NOCOUNT ON;
SELECT [画像]
FROM [FSTEST].dbo.映画マスタ
WHERE (ID = @ID)
END
ちなみにテーブル内の画像を取得するオリジナルは「getPicture」。単にDB名が違うだけ。
USE [MOVIE]
GO
/****** Object: StoredProcedure [dbo].[getPicture] Script Date: 2014/10/25 19:08:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[getPicture]
@ID int
AS
BEGIN
SET NOCOUNT ON;
SELECT [画像]
FROM dbo.映画マスタ
WHERE (ID = @ID)
END
ASP.NETでの画像表示
画像の表示は下記のとおり。
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SDS" runat="server"
ConnectionString="<%$ ConnectionStrings:MOVIEConnectionString %>"
SelectCommand="getPicture" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="ID" QueryStringField="ID" Type="Decimal" />
</SelectParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim dv As Data.DataView = SDS.Select(DataSourceSelectArguments.Empty)
If Not dv Is Nothing Then
If dv.Table.Rows.Count > 0 Then
If Not IsDBNull(dv.Table.Rows(0)(0)) Then
Dim oW As Integer = 150 '
Dim oH As Integer = 300 '
Dim bmp As System.Drawing.Bitmap = System.Drawing.Bitmap.FromStream(New System.IO.MemoryStream(DirectCast(dv.Table.Rows(0)(0), Byte())))
If bmp.Width > oW Or bmp.Height > oH Then
Dim s As Double = Math.Min(oW / bmp.Width, oH / bmp.Height)
Dim sW As Integer = CInt(s * bmp.Width)
Dim sH As Integer = CInt(s * bmp.Height)
Dim outBmp As System.Drawing.Bitmap = New System.Drawing.Bitmap(sW, sH)
Using g As System.Drawing.Graphics = System.Drawing.Graphics.FromImage(outBmp)
g.DrawImage(bmp, 0, 0, sW, sH)
End Using
outBmp.Save(Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg)
Else
Response.BinaryWrite(DirectCast(dv.Table.Rows(0)(0), Byte()))
End If
Response.End()
End If
End If
End If
End Sub
End Class
コメント
コメントを投稿