時には機動警察パトレーバーのように、あるいは、600 万件のデータ加工を

たまぁーに、１巻だけ取り出して読み進めて、最後の熊耳巡査が出てくるところ（手元にあるのは文庫本）で、毎度思うところがある。

泉巡査がイングラムの能力を十分引き出せないのは何故か？格闘技術に疎いから、という結論になるのだけれど、イングラムというハードウェアに、イングラムを動かすソフトウェア（学習機能付き）が乗り、更に人の動かし方が最終的にイングラムの能力を決定づける（「個性」ともいうし、「個体差」ともいう）、という流れになる。

ハードウェアというのは、パソコンだったり、CPU だったり、メモリだったりするわけで、OS というのは、Windows だったり、Mac だったりする。いや、OSの取り方をもうちょっと広げれば、SQL Server だったり、Oracle だったり、.NET Framework だたり、するわけです。で、これを使うのが人なわけで、この場合はプログラマなのですね。

最近のハードウェアは 10 年前よりもぐんと良くなっているわけで、メモリが 2GB なんてのは当たり前で、HDD も 500GB という形で桁が違います。なので、同じ大量のデータを扱うにしても、昔のデータベースの扱い方と、最近の扱い方では全く違う…と言いますか、ハードウェアが違うところに人が関わって、でてくる性能に大きな違いがでます…と言いますか、作り方によって全く違うってのを実感しています。

たまたま、600 万件のアクセスログを解析しようと思ったわけですが、この 600 万件というデータ、10 年前だったら Oracle でもひいこらいう程度のものなので、ちょっと躊躇しました。テキストデータで 2GB 弱あるわけです。
で、ひとまず、手元の SQL Server 2008 Experss Edition にテキストデータを入れて、データ解析をしてみると、これが早い早い。最初のインポート（C# で作りました）はツールの作りが悪いのか、2 時間程データ挿入に掛かるのですが、その後データを加工する場合は、クエリを使うと結構のスピードで動きます。

インデックスが無い状態でも、全検索で 3 分位で結果がでるし、適当なインデックスをつければ 2,3 秒かからずに結果がでてきます。データの加工ですら、1 分位で済みます。

データ加工するところは、

25/May/2011:23:58:39 +0900

のようなアクセスログの日付データを、データベースの Datetime 型に直します。
なので、update 文で使えるように、

2011-5-25 23:58:39

な風に変えないと駄目なわけです。

最初は、C# のツールを使ってやろうと思ったのですが、そもそも SqlCommand を使って insert 文を使ってデータ挿入をすると、600 万件挿入するのに 2 時間かかるわけです。ということは、同じように SqlCommand を使って update 文を使ってデータを変更すると、2 時間掛かるのではないか？という予想が経ちますね。
データ加工のたびに、こんなに時間が掛かってしまってはろくなデータ解析ができません。

で、この遅い理由としては、

1. SqlCommand の呼び出しで、SQL Server との通信が入っている。
2. SqlCommand の呼び出しで、.NET Framework とネイティブデータの変換が入っている。
3. SqlCommand の呼び出しで、insert 文の解析が Sql Server で行われている。

が考えられるわけです。

1 の場合は、データ通信がなくなるように直接 SQL Server 上で行います。今回は、同じパソコン内でデータベースを動かしているので、あまり関係がないでしょう。
2 の場合は、C# でツールを作る限り駄目です。.NET Framework(実は Java も同じ)の場合は、.NET とネイティブデータの変換が必ず入ってしまうので、C/C++ で直接データを書き込むよりも遅くなるのは当たり前なのです。これは、SQL Server 自体が C/C++ で書かれている（内部データ自体は単なるバイナリ形式）であろうことから予想ができます。
3. SQL 文を解析しないようにして、bulk データを挿入できればよいのですが、あいにく SQL Server には bulk 以外にデータを挿入する方法がありません。SQLite みたいに SQL 文ではない形でデータ加工ができればよいんですけどね。

という訳で、2 時間の作業を短縮させるためには、2 のように .NET で書かないという方法を取らないと駄目なのです。

さて、ツールを作るならば C/C++ を使ってもよいのですが、結構面倒です（ADOを扱えばいいんですが、まぁ、面倒と言えば面倒）。なので、SQL Server で直接クエリを実行するようにします。

直接クエリを実行する場合は、SQL Server Management Studio でクエリ文を作るか、最終的にファンクションあるいはストアドプロシージャにしてしまうかです。

で、ざっくりと作ったの以下です。

CREATE function [dbo].[getltime]
( @d as varchar(50) )
returns datetime
as
begin
declare @dt varchar(50)
declare @i int
declare @day   varchar(10)
declare @month varchar(10)
declare @year  varchar(10)
declare @time  varchar(20)
declare @gmt   varchar(20)
declare @ltime datetime
declare @gtime datetime

if @d is null 
  return null

set @dt = @d
set @i = CHARINDEX( '/', @dt )
if @i = 0 
 return @d

set @day = SUBSTRING( @dt,0,@i )
set @dt = RIGHT(@dt,LEN(@dt)-@i)
set @i = CHARINDEX( '/', @dt )
if @i = 0 
 return @d
set @month = SUBSTRING( @dt,0,@i )
set @dt = RIGHT(@dt,LEN(@dt)-@i)
set @i = CHARINDEX( ':', @dt )
if @i = 0 
 return @d
set @year = SUBSTRING( @dt,0,@i )
set @dt = RIGHT(@dt,LEN(@dt)-@i)
set @i = CHARINDEX( ' ', @dt )
if @i = 0 
 return @d
set @time = SUBSTRING( @dt,0,@i )
set @gmt = RIGHT( @dt, len(@dt)-@i )
set @month = REPLACE(@month,'May','5')

set @gtime = CAST( @year+'-'+@month+'-'+@day+' '+@time as datetime )
if SUBSTRING(@gmt,0,1) = '+' 
begin
	set @gmt = (SUBSTRING(@gmt,2,2)+':'+SUBSTRING(@gmt,4,2))
	set @ltime = @gtime - @gmt
end
else
begin
	set @gmt = (SUBSTRING(@gmt,2,2)+':'+SUBSTRING(@gmt,4,2))
	set @ltime = @gtime + @gmt
end 

-- select @year, @month, @day, @time, @gmt, @ltime, @gtime , @gmt 
return @ltime
end

月の表示(May)が、いい加減ですが、まぁ汎用的に作るつもりはないので、これで良しとします。

これをあらかじめ作成しておいて、

update log set ltime = getltime([date]) 

で動かせばよいわけです。

これがどのくらいのスピードで動くのかというと、600 万件のデータを加工するに 3 分程度なんですね。
その、なんといいますか、ADO.NET が如何に遅いか、というのが分かるのです。いや、こんなに違うとは思わなかったのですが…ちょっと、.NET でデータベースを扱うときは、加工の仕方を考え直したほうがいいですね。

という話でした。