SQL Server に BLOB 値を書き込むときのリソースの節約

[アーティクル]
03/28/2008

バイナリラージオブジェクト (BLOB) をデータベースに書き込むには、文字列値またはバイト配列用に、フィールドを挿入または更新します。どちらの種類のデータを使うかは、フィールドの種類によって決まります (「データベースへの BLOB 値の書き込み」を参照)。ただし、BLOB が非常に大きい場合は、単一の値として書き込むと大量のシステムメモリが使用され、アプリケーションのパフォーマンスが低下する場合があります。

BLOB 値の書き込み時に使用されるメモリ量を減らすために、一般的には、BLOB を "チャンク" 単位でデータベースに書き込む方法が使われます。この方法で BLOB をデータベースに書き込む処理は、使用しているデータベースの機能によって異なります。

SQL Server に BLOB をチャンク単位で書き込む方法を次の例に示します。このサンプルでは、BLOB である従業員のイメージを含む新しいレコードを Northwind データベースの Employees テーブルに追加します。このサンプルでは SQL Server の UPDATETEXT 関数を使用して、新しく追加された従業員のイメージを、指定されたサイズのチャンク単位で Photo フィールドに書き込みます。

UPDATETEXT 関数では、更新する BLOB フィールドへのポインタが必要です。このサンプルでは、新しい従業員レコードが追加されると、SQL Server の TEXTPTR 関数を呼び出して、新しいレコードの Photo フィールドへのポインタを取得します。このポインタ値は出力パラメータとして戻されます。サンプルのコードはこのポインタを保持して、データのチャンクを追加するときに、UPDATETEXT に渡します。

新しい従業員レコードを挿入し、Photo フィールドへのポインタを保持するために使用される Transact-SQL の例を次に示します。ここで、@Identity と @Pointer は、SqlCommand の出力パラメータとして指定されています。

INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) 
  Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0)
SELECT @Identity = SCOPE_IDENTITY()
SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity

初期値 0x0 (null) が Photo フィールドに挿入されていることに注意してください。これにより、新しく挿入されたレコードの Photo フィールドのポインタ値を取得することができます。また、null 値は、追加されるデータのチャンクには影響しません。

このサンプルは、新しく挿入されたレコードの Photo フィールドへのポインタを保持しているため、SQL Server の UPDATETEXT 関数を使用して、BLOB フィールドにデータのチャンクを追加できます。UPDATETEXT 関数は、フィールド識別子 (Employees.Photo)、BLOB フィールドへのポインタ、現在のチャンクが書き込まれる BLOB 内の場所を表すオフセット値、および追加されるデータのチャンクを、入力として受け取ります。UPDATETEXT 関数の構文を記述したコード例を次に示します。ここで、@Pointer、@Offset、および @Bytes は SqlCommand の入力パラメータとして指定されています。

UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes

オフセット値はメモリバッファのサイズによって決まります。メモリバッファのサイズは、アプリケーション側の条件に基づいて、プログラマが決定します。バッファサイズを大きくすると、BLOB の書き込み速度は速くなりますが、使用するシステムメモリが増えます。このサンプルでは、比較的小さな 128 バイトのバッファサイズを使用します。オフセット値は、データの最初のチャンクが 0 で、その後はチャンクごとにバッファサイズ分がインクリメントされた値になります。

このサンプルでは、指定したファイルパスから、チャンク単位で従業員の写真を取得します。各チャンクは、指定したバッファサイズ単位で、バイト配列に読み込まれます。次に、バイト配列は、SqlCommand の @Bytes 入力パラメータの値として設定されます。@Offset パラメータ値が更新され、SqlCommand が実行されて、現在のチャンクが従業員レコードの Photo フィールドに追加されます。

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

Public Class EmployeeData

    Public Shared Sub Main()
      Dim hireDate As DateTime = DateTime.Parse("4/27/98")
      Dim newID As Integer = _
          AddEmployee("Smith", "John", "Sales Representative", hireDate, 5, "smith.bmp")
      Console.WriteLine("New Employee added. EmployeeID = " & newID)
    End Sub

    Public Shared Function AddEmployee(lastName As String, firstName As String, title As String, hireDate As DateTime, _
                           reportsTo As Integer, photoFilePath As String) As Integer

    Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;")

    Dim addEmp As SqlCommand = New SqlCommand("INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) " & _
                                              "Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0);" & _
                                              "SELECT @Identity = SCOPE_IDENTITY();" & _
                                              "SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity", nwindConn) 

    addEmp.Parameters.Add("@LastName",  SqlDbType.NVarChar, 20).Value = lastName
    addEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = firstName
    addEmp.Parameters.Add("@Title",     SqlDbType.NVarChar, 30).Value = title
    addEmp.Parameters.Add("@HireDate",  SqlDbType.DateTime).Value     = hireDate
    addEmp.Parameters.Add("@ReportsTo", SqlDbType.Int).Value          = reportsTo

    Dim idParm As SqlParameter = addEmp.Parameters.Add("@Identity", SqlDbType.Int)
    idParm.Direction = ParameterDirection.Output
    Dim ptrParm As SqlParameter = addEmp.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
    ptrParm.Direction = ParameterDirection.Output

    nwindConn.Open()

    addEmp.ExecuteNonQuery()

    Dim newEmpID As Integer = CType(idParm.Value, Integer)

    StorePhoto(photoFilePath, ptrParm.Value, nwindConn)

    nwindConn.Close()

    Return newEmpID
  End Function

  Public Shared Sub StorePhoto(fileName As String, pointer As Byte(), nwindConn As SqlConnection)

    Dim bufferLen As Integer = 128   ' The size of the "chunks" of the image.

    Dim appendToPhoto As SqlCommand = New SqlCommand("UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes", nwindConn)

    Dim ptrParm As SqlParameter = appendToPhoto.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
    ptrParm.Value = pointer
    Dim photoParm As SqlParameter = appendToPhoto.Parameters.Add("@Bytes", SqlDbType.Image, bufferLen)
    Dim offsetParm As SqlParameter = appendToPhoto.Parameters.Add("@Offset", SqlDbType.Int)
    offsetParm.Value = 0

    ''''''''''''''''''''''''''''''''''''
    '' Read the image in and write it to the database 128 (bufferLen) bytes at a time.
    '' Tune bufferLen for best performance. Larger values write faster, but
    '' use more system resources.


    Dim fs As FileStream = New FileStream(fileName, FileMode.Open, FileAccess.Read)
    Dim br As BinaryReader = New BinaryReader(fs)

    Dim buffer() As Byte = br.ReadBytes(bufferLen)
    Dim offset_ctr As Integer = 0

    Do While buffer.Length > 0
      photoParm.Value = buffer
      appendToPhoto.ExecuteNonQuery()
      offset_ctr += bufferLen
      offsetParm.Value = offset_ctr
      buffer = br.ReadBytes(bufferLen)
    Loop

    br.Close()
    fs.Close()
  End Sub

End Class
[C#]
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

public class EmployeeData
{
  public static void Main()
  {
    DateTime hireDate = DateTime.Parse("4/27/98");
    int newID  = AddEmployee("Smith", "John", "Sales Representative", hireDate, 5, "smith.bmp");
    Console.WriteLine("New Employee added. EmployeeID = " + newID);
  }

  public static int AddEmployee(string lastName, string firstName, string title, DateTime hireDate , int reportsTo, string photoFilePath)
  {
    SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;");

    SqlCommand addEmp  = new SqlCommand("INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) " +
      "Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0);" +
      "SELECT @Identity = SCOPE_IDENTITY();" +
      "SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity", nwindConn);

    addEmp.Parameters.Add("@LastName",  SqlDbType.NVarChar, 20).Value = lastName;
    addEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = firstName;
    addEmp.Parameters.Add("@Title",     SqlDbType.NVarChar, 30).Value = title;
    addEmp.Parameters.Add("@HireDate",  SqlDbType.DateTime).Value     = hireDate;
    addEmp.Parameters.Add("@ReportsTo", SqlDbType.Int).Value          = reportsTo;

    SqlParameter idParm = addEmp.Parameters.Add("@Identity", SqlDbType.Int);
    idParm.Direction = ParameterDirection.Output;
    SqlParameter ptrParm = addEmp.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
    ptrParm.Direction = ParameterDirection.Output;

    nwindConn.Open();

    addEmp.ExecuteNonQuery();

    int newEmpID = (int)idParm.Value;

    StorePhoto(photoFilePath, (byte[])ptrParm.Value, nwindConn);

    nwindConn.Close();

    return newEmpID;
  }

  public static void StorePhoto(string fileName, byte[] pointer,  SqlConnection nwindConn)
  {
    int bufferLen = 128;  // The size of the "chunks" of the image.

    SqlCommand appendToPhoto = new SqlCommand("UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes", nwindConn);

    SqlParameter ptrParm  = appendToPhoto.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
    ptrParm.Value = pointer;
    SqlParameter photoParm = appendToPhoto.Parameters.Add("@Bytes", SqlDbType.Image, bufferLen);
    SqlParameter offsetParm = appendToPhoto.Parameters.Add("@Offset", SqlDbType.Int);
    offsetParm.Value = 0;

    //''''''''''''''''''''''''''''''''''
    // Read the image in and write it to the database 128 (bufferLen) bytes at a time.
    // Tune bufferLen for best performance. Larger values write faster, but
    // use more system resources.

    FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
    BinaryReader br = new BinaryReader(fs);

    byte[] buffer = br.ReadBytes(bufferLen);
    int offset_ctr = 0;

    while (buffer.Length > 0)
    {
      photoParm.Value = buffer;
      appendToPhoto.ExecuteNonQuery();
      offset_ctr += bufferLen;
      offsetParm.Value = offset_ctr;
      buffer = br.ReadBytes(bufferLen);
    }

    br.Close();
    fs.Close();
  }
}

参照

データベースへの BLOB 値の書き込み | サンプル ADO.NET シナリオ | ADO.NET を使用したデータのアクセス | .NET Framework データプロバイダによるデータのアクセス

次の方法で共有

SQL Server に BLOB 値を書き込むときのリソースの節約

参照

その他のリソース