SqlBulkCopy では、Decimal 型で桁落ちをする

投稿日: 2011/10/24 作成者: masuda

SqlBulkCopy のスピードは 20 倍ぐらい早い | Moonmile Solutions Blog
http://www.moonmile.net/blog/archives/2234

なところで、SQL Server へのインサートを高速化したのですが、実はちょっとした落とし穴があります（落とし穴、というよりも「仕様」っぽいのですが）。

SQL Server で Decimal 型を使うと、Oracle の nameric 型のように精度を指定できます。たとえば decimal(18,4) と設定すると、小数点以下第4桁までの精度になります。

この場合、「0.12345」という値を入れようとすると、クエリでは「0.1235」のように最後の桁で四捨五入されるのですが、SqlBulkCopy を使うと「0.1234」のように切り捨てになる、という違いがあります。

1
2
3
4
CREATE TABLE [dbo].[t_dec](
    [dec] [decimal](18, 4) NULL,
    [dbl] [float] NULL
) ON [PRIMARY]

のようにテーブルを作成しておいて、データベースに書き込みます。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
''' <summary>
''' BulkCopy で書き込み
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
 
    Dim dec As Decimal = 0.1234567890123456789012345679D
    Dim dbl As Decimal = 0.12345678901234559
    Dim dt As New DataTable
 
    dt.Columns.Add("dec", GetType(Decimal))
    dt.Columns.Add("dbl", GetType(Double))
    Dim row As DataRow = dt.NewRow
    row("dec") = dec
    row("dbl") = dbl
    dt.Rows.Add(row)
 
    Dim cn As New SqlConnection(CNSTR)
    Dim bc As New SqlBulkCopy(cn)
    cn.Open()
    bc.DestinationTableName = "t_dec"
    bc.WriteToServer(dt)
    cn.Close()
    bc.Close()
 
    ' decimal(18,4) で指定していると、dec = 0.1234 となり切り捨てとなる
 
    Dim dt2 As New DataTable
    Dim da As New SqlDataAdapter("SELECT * FROM t_dec", cn)
    da.Fill(dt2)
    DataGridView1.DataSource = dt2
 
 
End Sub
 
''' <summary>
''' SqlCommand で書き込み
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
 
    Dim dec As Decimal = 0.1234567890123456789012345679D
    Dim dbl As Decimal = 0.12345678901234559
 
    Dim cn As New SqlConnection(CNSTR)
    Dim cmd As New SqlCommand("INSERT INTO t_dec values ( @DEC, @DBL )", cn)
    cmd.Parameters.Add(New SqlParameter("@DEC", dec))
    cmd.Parameters.Add(New SqlParameter("@DBL", dbl))
 
    cn.Open()
    cmd.ExecuteNonQuery()
    cn.Close()
 
    ' decimal(18,4) で指定していると、dec = 0.1235 となり四捨五入される
 
    Dim dt2 As New DataTable
    Dim da As New SqlDataAdapter("SELECT * FROM t_dec", cn)
    da.Fill(dt2)
    DataGridView1.DataSource = dt2
End Sub

こうすると、SqlCommand の場合は「0.1235」のように四捨五入されるのですが、SqlBulkCopy の場合は「0.1234」のように切り捨てられます。

なので、仕方がないので（？）、四捨五入されるように「0.00005D」を加算します（末尾の「D」は、decimal 型の印です）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
''' <summary>
''' decimal の精度に合わせて 0.00005 を加算する
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
 
    Dim dec As Decimal = 0.1234567890123456789012345679D
    Dim dbl As Decimal = 0.12345678901234559
    Dim dt As New DataTable
 
    dt.Columns.Add("dec", GetType(Decimal))
    dt.Columns.Add("dbl", GetType(Double))
    Dim row As DataRow = dt.NewRow
    row("dec") = dec + 0.00005D    ' 四捨五入させる
    row("dbl") = dbl
    dt.Rows.Add(row)
 
    Dim cn As New SqlConnection(CNSTR)
    Dim bc As New SqlBulkCopy(cn)
    cn.Open()
    bc.DestinationTableName = "t_dec"
    bc.WriteToServer(dt)
    cn.Close()
    bc.Close()
 
    ' BulkCopy だと切り捨てになるので、
    ' decimal(18,4) の場合は 0.00005D を加えて、
    ' dec = 0.1235 のように四捨五入にする
 
    Dim dt2 As New DataTable
    Dim da As New SqlDataAdapter("SELECT * FROM t_dec", cn)
    da.Fill(dt2)
    DataGridView1.DataSource = dt2
 
End Sub

さて、これを組み込みかどうかを思案中。

カテゴリー: 開発, VB パーマリンク