CSV取り込みの重複キー検出について
前提
大量データの一括取り込み時に、キー重複エラーを回避したい。重複した行をユーザーインターフェイスで出力したい。処理が遅く、非同期にしづらい。ユーザーから「早くできないの?」と言われている人向け。
本題に入る前に
「件数が多いんだから、遅いのは当たり前だろ」という人もいるかもしれないが、DBのインポートにかかる時間なんてたかが知れている。100万行程度であれば、インデックスを消してしまえばあっという間に取り込みが終わる。索引をつける時間のほうがよほどコストが高い。今どきのDBはAutoAnalyzeが動くから、CSV取り込み時に何度もAnalyzeがかかるような設定(閾値)になっていないか?くらいは、最低限事前に調べておいたほうがいいだろう。
DBによっても、この手の取り込み繰り返し処理はコーディング次第で速度にかなりの差が発生する。例えば、Oracleは一行ごとにinsertしていってもさほど遅くはない。費用面でとんでもないコストがかかるのだから、当たり前の話ではあるが。。同じコードをPostgresで走らせてみると、遅い。とにかく遅い。1000行くらいでもセッションタイムアウトするんじゃないかっていうくらいに遅い。費用面でとんでもなく安い(というか無料)のだから、これもまた当たり前なのだが。
ただし、Postgresが遅いかというとそんなことはない。単に、一行ずつInsertするのが遅いだけで、Postgresを使ったことがあるコーダーはそんなプログラムを書かない。たいてい、ある程度まとまった量を一気(※1)にInsertするか、Copyするはずだ。
※1 WindowsServerだと2000行、Linuxであれば10000行くらいでよいと思う。
ハード側のスペック不足はもちろんだが、設計の時点でDBの特性を生かせていない可能がないか、そちらも事前に調査しておくのも必要だ。
本題
■キー重複を調べてみると?
csvなので、データクラスのListが設計されていることを前提に考えてみる。
Public Class Person
Private _name As String
Private _age As Integer
Private _sex As Integer
Sub New(name As String, age As Integer, isMen As Boolean)
_name = name
_age = age
_sex = IIf(isMen, 0, 1)
End Sub
Public Sub SetSex(isMen As Boolean)
_sex = IIf(isMen, 0, 1)
End Sub
Public Function GetName() As String
Return _name
End Function
Public Function GetAge() As Integer
Return _age
End Function
Public Function IsMen() As Boolean
Return _sex = 0
End Function
End Class
Public Class PersonList
Implements IList
Private _list As List(Of Person)
Default Public Property Item(index As Integer) As Object Implements IList.Item
Get
Throw New NotImplementedException()
End Get
Set(value As Object)
Throw New NotImplementedException()
End Set
End Property
Public ReadOnly Property IsReadOnly As Boolean Implements IList.IsReadOnly
Get
Throw New NotImplementedException()
End Get
End Property
Public ReadOnly Property IsFixedSize As Boolean Implements IList.IsFixedSize
Get
Throw New NotImplementedException()
End Get
End Property
Public ReadOnly Property Count As Integer Implements ICollection.Count
Get
Throw New NotImplementedException()
End Get
End Property
Public ReadOnly Property SyncRoot As Object Implements ICollection.SyncRoot
Get
Throw New NotImplementedException()
End Get
End Property
Public ReadOnly Property IsSynchronized As Boolean Implements ICollection.IsSynchronized
Get
Throw New NotImplementedException()
End Get
End Property
Public Sub Clear() Implements IList.Clear
Throw New NotImplementedException()
End Sub
Public Sub Insert(index As Integer, value As Object) Implements IList.Insert
Throw New NotImplementedException()
End Sub
Public Sub Remove(value As Object) Implements IList.Remove
Throw New NotImplementedException()
End Sub
Public Sub RemoveAt(index As Integer) Implements IList.RemoveAt
Throw New NotImplementedException()
End Sub
Public Sub CopyTo(array As Array, index As Integer) Implements ICollection.CopyTo
Throw New NotImplementedException()
End Sub
Public Function GetEnumerator() As IEnumerator Implements IEnumerable.GetEnumerator
Throw New NotImplementedException()
End Function
Public Function Add(value As Object) As Integer Implements IList.Add
Throw New NotImplementedException()
End Function
Public Function Contains(value As Object) As Boolean Implements IList.Contains
Throw New NotImplementedException()
End Function
Public Function IndexOf(value As Object) As Integer Implements IList.IndexOf
Throw New NotImplementedException()
End Function
End Class
一応、PersonListのほうはIListを実装しておいた。単純な使い方であれば不要だ。
さて、このシステムだが、同じ名前の人は登録できなくなっているとする。この前提の場合、どのように重複チェックをしたらいいだろう?
調べ方にもよるが、単純にググる程度であれば出てくるのは大体「Contains」を使用したサンプル。
Public Function Contains(value As Object) As Boolean Implements IList.Contains
Return _list.Contains(value)
End Function
これは遅いんじゃないだろうか?オブジェクト型の比較の場合はキャストは発生しない?のであれば問題なさそうだが、キャストしているとしたら遅延バインディングするはずだ。そもそも、正常な判定ができているんだろか?いろいろと疑念の残るメソッドになってしまったので、キャストは発生しない、正常な判定ができている前提で話を進めることにする。
※別の機会に検証してみます
ビジネスロジック側から、PersonList.Addを動かす前に、ContainsしてFalseだったらAddとした。この場合、1000行くらいであればそこそこ動いてくれるが、10000行規模になると途端に動かなくなる。数分待ってようやくDBの処理に到達するというレベルになるかもしれない。
重複チェックはContains、と決めつけてしまうのはあまりよろしくない。Containsはあくまでも重複を判定するものであり、実用性で考えると、小規模システム向けのメソッドだ。では、100万行とはいかないまでも、10000行程度ではびくともしないような現実的なメソッドを考えると下記となる。
Private nameDictionary As Dictionary(Of String, Integer) = Nothing
Public Function Contains(value As Person) As Boolean
If nameDictionary Is Nothing Then
Dim dic As New Dictionary(Of String, Integer)
For Each item As Person In _list
dic.Add(item.GetName, 0)
Next
nameDictionary = dic
End If
Try
nameDictionary.Add(value.GetName, 0)
Catch ex As Exception
Return False
End Try
Return True
End Function
としたほうがよほど早い。
遅い遅いといわれている、メンバーへのアクセス、これまた遅いといわれるTry。10000行すべてが重複していればとんでもなく遅くなるのは間違いないが、運用レベルではそんな無茶なデータは発生しない。せいぜい数行、あっても数十行だろう。重複した場合は、Dictionaryのキー重複エラーが発生してFalseが返却される。
たまに走るメンバーへのアクセス、たまに走るエクセプション、この遅い遅いの組み合わせのほうが、実は運用においては最適化されているのだ。
まとめ
コーディング中にちょっと躓いたり、ちょっと悩んだりしたときに、ブラウザを開いてサンプルを探してみる、というのは私自身よくあることだ。実際に、ググってみると山のように有象無象のサンプルが出てくる。中には、思いがけないような品質のものもあるし、あり得ないほどに悪質(時代的な意味合いでも)なソースもある。
特にVBは気を付けないと、VB6時代のソースが引っかかってきたりもする。これだけは断言できるが、VB6のソースは使い物にならない。必ずしも「新しいものが良いもの」とは言えないが、ことプログラミングに関しては、年々進化していっているので、コーディングルールに反しない程度であれば、積極的に新しい情報を採用していったほうがいいだろう。
また、今回の事例のように、「動くコード」と「運用できるコード」は全く別なので、この辺の情報の使い分けもうまくやる必要がある。とりあえずテストで動けばいいでしょ、という考えで組まれたプログラムが、本番DBに接続したら動かなくなった、なんて体験をした人は少なくないはずだ。
CSV取り込み、は割と発生しやすい機能なので、運用できるコードを意識してコーディングしたい。
コメントを残す