愚者の経験

「また今度」はほとんどこない

カテゴリーアーカイブ: ADO

SQL ServerへのCSVファイルinsert高速化

クライアントでテキストファイル(csv)を処理してテーブルに
取り込む操作があったのですがそれが遅いとの事。

普通に「insert into xxxxx values(zzzzzzz,zzzz,zzzz)」系の
文字列にして、ADODB.Commandにセットしていたんですが
良かれと思ってあることをしていたのです…

    Dim InsertSQL As String
    Dim ValuesSQL As String
    Dim Row As String
    Dim rCount As Long
    Dim fNO As Long
    
    fNO = FreeFile
    InsertSQL = "insert into xxx(xxxx,xxx,xx,x)"
    
    Open filePath For Input As #fNO
    Do Until EOF(fNO)
        rCount = rCount + 1
        Line Input #fNO, Row
        
        ValuesSQL = ValuesSQL & ",(" & Row & ")"
        
        If (rCount Mod 1000 = 0) Then
            With New ADODB.Command
                .ActiveConnection = "xxxxxx"
                .CommandText = InsertSQL & " values" & Mid$(ValuesSQL, 2)
                .CommandType = adCmdText
                .CommandTimeout = 0
                .Execute , , adExecuteNoRecords
            End With
            ValuesSQL = ""
        End If
    Loop
    
    Close #fNO

大雑把に書くとこんな感じです。適当に書いてるのであしからず。
SQL Server2008から(確か)可能になった「insert into~~ valuesでの複数行追加
をやっていました。「valus(a1,b1),(a2,b2)」のような書き方で複数行がinsertできる機能ですが
既定で一度に1000行までしかinsertできない制約を除けば一度の実行の手間が省ける!
と思っていたんです。

しかしExecute前後でTimer計測すると1000行入れるのにも4-5秒ほどの時間がかかっていました。
インデックスを除去したりトランザクションで括っても結果は変わらず…「早くならない!これが限界か?」
おもむろに1000行カウントを外して1行ずつExecuteするように変更してみました。
するとなんと5000行を10秒で回りました。めちゃくちゃSpeedup。

さらに

insert into xxx(xxxx,xxx,xx,x) values('a1','b1','c1','d1');
insert into xxx(xxxx,xxx,xx,x) values('a2','b2','c2','d2');
insert into xxx(xxxx,xxx,xx,x) values('a3','b3','c3','d3');
insert into xxx(xxxx,xxx,xx,x) values('a4','b4','c4','d4');
…
insert into xxx(xxxx,xxx,xx,x) values('a1000','b1000','c1000','d1000');

上記のような全体を文字列にして実行回数自体を減らすと更に早くなりました。
これならトランザクションで効果が出そうだと思いましたがやはりあまり変わらず。

いづれにしても複数行insertで遅い時は敢えて1行ずつ実行することも
試してみる価値はあると思います。必ずしも新機能が高速だというわけではなく
当然ケースバイケースだということだと思います。でもちょっと驚き。

ちなみに1行30列ほどだったと思います。
また1行ずつExecuteするなら、「insert into xxx(xxxx,xxx,xx,x) values(?,?,?,?);」
このような形でCommandTextをセットし、PreparedをTrueにして実行すれば
こっちのほうが早いかもしれません。やったことないですが。

    Dim Row As String
    Dim rCount As Long
    Dim fNO As Long
    fNO = FreeFile

    With New ADODB.Command
        .ActiveConnection = "xxxx"
        .CommandText = "insert into xxx(xxxx,xxx,xx,x) values(?,?,?,?);"
        .CommandType = adCmdText
        .CommandTimeout = 0
        .Prepared = True
        ' Parameters.Apprend .CreateParameter*4が必要かも
    
        Open filePath For Input As #fNO
        Do Until EOF(fNO)
            rCount = rCount + 1
            Line Input #fNO, Row
            .Execute , Split(Row, ",", , vbBinaryCompare), adExecuteNoRecords
        Loop
        Close #fNO
    End With

超すっきりしますね(笑)繰り返しますが未検証なのであしからず。
パラメータの配列渡しはSplitじゃダメかも知れません。

そもそもBULK使えば?と言われそうですが、これはこれで共有フォルダの
設定とか考えなくていいので全く見込みがないわけじゃないと思うのです。

Office2013評価版とかいろいろ試す

「OfficeProfessionalPlus2013」を試してみました。
ファイル名は「OfficeProfessionalPlus_x86_ja-jp.img」です。

ADODB.Recordsetの不具合が直っていました!!個人的にかなりうれしいです。
Previewではフォームの「Recordset」プロパティにSetしてもデータが正しく
表示されてなかったのですが、上のOfficeでは正しく表示できているようです。
また、編集等もできたように思います。

これでストアドプロシージャで取得したデータを編集する方法が絶たれずに
済みました。

Access Proect(*.adp)でローカル一時テーブルを使う

またまたadpネタです。
adpでは「ローカルテーブルを持つことができない
という大きなハンデを抱えています。利用できるテーブルはすべてSQL Serverに作成
する必要があり、作業用のテーブルが必要な場合はSQL Serverの一時テーブル
(先頭に「#」を使ってCreate Table する構文)で作成できますが、これはセッション単位で
破棄してしまいます。

例えば以下のように

CREATE PROCEDURE [dbo].[sp_test] 
AS
BEGIN
	create table #temptbl (col1 INT PRIMARY KEY)
	
	select *
	from #temptbl
END

というストアドを作成してフォームのレコードソースに「sp_test」としたとしても、
データを入力することはできません。「tempdbのオブジェクトが」みたいなエラーが出ます。

ではユーザー(プログラム)別にテーブルを持つことは出来ないのかというと
VBAを使って可能です。

標準モジュールに以下のように記述します。

Option Compare Database
Option Explicit

Private cn As ADODB.Connection

Public Sub test()
    If (cn Is Nothing) Then
        Set cn = CurrentProject.AccessConnection        
        cn.Execute "CREATE TABLE #temptbl (col1 INT PRIMARY KEY)"
    End If
End Sub

フォームのストアドも修正します。CREATE TABLEを取るだけです。

ALTER PROCEDURE [dbo].[sp_test] 
AS
BEGIN
	select *
	from #temptbl
END

これでアプリケーションの開始時にtestプロシージャをコールしておけば
フォーム上で入力出来るローカルテーブルの出来上がりです。

testプロシージャではadpのコネクションを取得し、そのコネクション上でExecuteして
一時テーブルを作成します。この操作でAccessが持つセッションに
従属する(?)ローカル一時テーブルとなりフォームにバインド出来るようになります。

後はストアドで一時テーブルをselectしておけば編集可能になります。
起動と同時に作成し、アプリケーションの終了と同時に破棄されますので便利です。

create table ですがCurrentProject.AccessConnection.Execute ~~~
みたいに一行で実行してもいいです。

CREATE TABLEの文字列をスカラー関数にしてSQL Serverに
登録しておくことで一元管理もできそうです。

CREATE FUNCTION sf_test 
()
RETURNS nvarchar(max)
AS
BEGIN
	RETURN 'create table #temptbl (col1 INT PRIMARY KEY)'
END
GO
Option Compare Database
Option Explicit

Public Sub test1()
    Dim tblstr As String
    
    With CurrentProject.AccessConnection
        tblstr = .Execute("select dbo.sf_test()").Fields(0).Value
        
        .Execute tblstr
    End With
        
End Sub

ローカルテーブルが欲しい場合はおすすめ?です。
ところでAccess 2013 RTMになってADODB.Recordsetをフォームに
Set出来るようになったんでしょうか?実に気になります。

Access2010でのODBCDirectの代替案

Access2013はAccessProject(ADP)がサポートされないのでほぼODBCを使うことになると思います。
しかしAccess2007以降ODBCDirectは使えません。

以下の文は実行時エラー3847
「ODBCDirectはサポートされません。DAOの代わりにADOを使用するには、
コードを記述しなおしてください。」と怒られます。

        CreateWorkspace("test", "", "", dbUseODBC)

ではADOでしか外部データベースに接続できないのかというとそうでもありません。
ODBCDirectの代わりにどうやって動的(コード)で接続するかというと
TableDef、QueryDefにある「Connect」プロパティを操作することで実現可能になります。

SQLコマンドパススルー発行関数

Public Function ServerExecute(SQLString As String, Connect As String, _
                Optional ReturnsRecords As Boolean = False, _
                Optional RecordsetType As DAO.RecordsetTypeEnum = dbOpenForwardOnly, _
                Optional RecordsetOption As DAO.RecordsetOptionEnum = dbReadOnly + dbSQLPassThrough, _
                Optional StoredReturn As Boolean = False) As DAO.Recordset

    With CurrentDb.CreateQueryDef("")
        .Connect = Connect
        .SQL = SQLString
        .ReturnsRecords = ReturnsRecords
        .ODBCTimeout = 0

        If (ReturnsRecords = True) Then
            Set ServerExecute = .OpenRecordset(RecordsetType, RecordsetOption)
        Else
            If (StoredReturn = True) Then
                .ReturnsRecords = True
                .SQL = "declare @ret int" & vbCrLf & _
                    "execute @ret=" & .SQL & vbCrLf & _
                    "select @ret as Ret"
                Set ServerExecute = .OpenRecordset(RecordsetType, RecordsetOption)
            Else
                .Execute
            End If
        End If
    End With
End Function

こんなかんじにすればDatabaseオブジェクトをオープンせずに
外部データベースにコマンドを発行、結果の取得ができます。

DAO.Recordsetをフォームの「Recordset」プロパティにセットする場合はRecordsetTypeをdbOpenSnapshotに指定しないと、実行にエラー(ナンバー7965)となり
「”Recordset/レコードセット”プロパティに、そのオブジェクトは使えません」が出ます。

またテーブルのリンクを貼り直すには以下のような感じで。

Public Function TablesRelink(ByVal Connect As String) As Boolean
On Error Resume Next
        Dim tdf As TableDef
        Dim pConn As String

        ' テーブルオブジェクトをループ
        For Each tdf In CurrentDb.TableDefs
                With tdf
                        ' Connectプロパティがあるかどうかでリンクテーブルかを調べる
                        If (Nz(.Connect, "")  "") Then
                                pConn = .Connect

                                ' プログラムの接続文字列代入
                                .Connect = Connect
                                .RefreshLink

                                If (Err.Number <> 0) Then
                                        ' エラーの場合は接続文字列を元に戻す
                                        .Connect = pConn
                                        Err.Clear

                                        ' 元の接続文字列でリンク確認
                                        .RefreshLink
                                        If (Err.Number <> 0) Then
                                                TablesRelink = True
                                                Err.Clear
                                        End If
                                End If
                        End If
                End With
        Next
End Function

複数のODBCでリンクしている場合は難しいですね。
これは内部ではJET経由になるのでしょうね。

Windows8 もうすぐ発売

久しぶりのブログ更新。結構アクセス数が上がっててびっくりです。皆様に感謝。

明日2012-10-26にWindows8がいよいよ発売になります!!テンションが上ります。
これだけだとブログアップの久しぶり感と無理矢理投稿した感がが強すぎなので少しいつものAccessネタを…。

この「Windows8」に続いて「Office2013」も出るわけですが、Accessも当然(?)
「Access2013」として存在しています。

しかしなんと「Accessプロジェクト(*.adp)」の機能が削られてしまいました!!
「Office2013」のPreview版ではadpファイルを開くことすら出来ません。

いや…まだだ…「ADODB.Recordsetで受けてフォームのレコードソースに代入できる」
手軽さはかなり失われるけれども…とも思いました。しかし甘かったです。
この手法すらも正常に表示できないのです。
参考URL:http://answers.microsoft.com/ja-jp/office/forum/office_2013-access/aceess-2013/7696c071-1692-4af0-b107-ede240419579

ということはAccessが他のデータベースに接続する方法は「ODBC」になるわけです。
データサービスなるものがAccess2010からあってこれが怪しいと睨んではいますが
情報がなくぜんぜんわかりません。
まだPreview版とはいえこれは由々しき事態だと思います。「Windows8」がサポートするOfficeは
2010以上のバージョンらしいので「Windows8」でadpとなればすでに「Access2010」一択なのです。
更に「Windows8」には「Windows7」にあったような「XP Mode」のような救済措置はもうなく
Accessで作ったシステムはどんどん動かなくなっていきます。

次の開発ツールや手法を学ぼうと思ってはいるのですが、どうも時代はウェブ(HTML5)に移行しているようで
今からデスクトップアプリケーションとなると何で作るのがいいかかなり迷うところです。
一時期「Visual Studio 2011」でデスクトップアプリケーションが「Windows8」の「Metroアプリ」ぐらいしか
作れなかったように肩身狭い分野になることは間違いなさそうです。

もしAccessから乗り換える人、乗り換えた人、並行して別ツールで開発している方がいらしたら、
「こんなのやってるよ~」と教えていただければ幸いです。

Accessでaccdbを使わずに他のDBを使ってみる – SQL Server CE編

SQL Server Compact Editionも実験してみます。Versionは3.5のsp2を利用します。

Public Sub sqlceTest2()
    Dim i As Long
    Dim tm As Single

    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = _
            "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" & _
            "Data Source=C:\test.sdf;"
    cnn.Open

    With New ADODB.Command
        Set .ActiveConnection = cnn
        cnn.BeginTrans
        .CommandText = "insert into " & _
            "test1 (int1,int2,int3,int4,int5,text1,text2,text3,text4,text5) " & _
            "values (1,2,3,4,5,'a','b','c','d','e')"
        .CommandType = adCmdText
        .CommandTimeout = 0

        tm = Timer
        For i = 0 To 30000
            .Execute
        Next

        Debug.Print Timer - tm
        cnn.CommitTrans
    End With
End Sub

実行結果 6.2秒

Public Sub sqlceTest3()
    Dim i As Long
    Dim tm As Single

    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = _
            "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" & _
            "Data Source=C:\test.sdf;"
    cnn.Open

    With New ADODB.Recordset
        Set .ActiveConnection = cnn
        .CursorLocation = adUseClient

        tm = Timer

        For i = 0 To 10
            .Open "select * from test1", , adOpenStatic, adLockReadOnly
            .Close
        Next

        Debug.Print Timer - tm
    End With
End Sub

実行結果 3.5秒

Public Sub sqlceTest4()
    Dim i As Long
    Dim tm As Single

    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = _
            "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" & _
            "Data Source=C:\test.sdf;"
    cnn.Open

    With New ADODB.Command
        Set .ActiveConnection = cnn
        cnn.BeginTrans
        .CommandText = "update test1 set int1=10"
        .CommandType = adCmdText
        .CommandTimeout = 0

        tm = Timer
        For i = 0 To 100
            .Execute
        Next

        Debug.Print Timer - tm
        cnn.CommitTrans
    End With
End Sub

実行結果 8.8秒

早い…SQLCEは一応uniqueidentifier(guid)やrowversion(timestamp)も使えるので
案外Accessで使ってもいいのかもしれません。

Accessでaccdbを使わずに他のDBを使ってみる – SQLite編4

次にupdateしてみます。30001件の全行に対しint1=10に更新します。


Public Sub SQLiteTest4()
    Dim i As Long
    Dim tm As Single
    
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = _
            "Provider=OleSQLite.SQLiteSource.1;" & _
            "Data Source=C:\test.sqlite;"
    cnn.Open
    
    With New ADODB.Command
        Set .ActiveConnection = cnn
        cnn.BeginTrans
        .CommandText = "update test1 set int1=10"
        .CommandType = adCmdText
        .CommandTimeout = 0
        
        tm = Timer
        For i = 0 To 100
            .Execute
        Next
        
        Debug.Print Timer - tm
        cnn.CommitTrans
    End With
End Sub

Public Sub accdbTest4()
    Dim i As Long
    Dim tm As Single
    
    
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = _
            "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
            "Data Source=C:\test.accdb;"
    cnn.Open
    
    With New ADODB.Command
        Set .ActiveConnection = cnn
        cnn.BeginTrans
        .CommandText = "update test1 set int1=10"
        .CommandType = adCmdText
        .CommandTimeout = 0
        
        tm = Timer
        For i = 0 To 100
            .Execute
        Next
        
        Debug.Print Timer - tm
        cnn.CommitTrans
    End With
End Sub

実行結果 SQLite :18.5秒 Access:40.1秒
Deleteは確認してませんがSQLiteはSelect以外で高速です。Selectもインデックスを利用して計測すれば
結果が違ってくるかもしれません。

Accessでaccdbを使わずに他のDBを使ってみる – SQLite編3

30001件のデータが追加されたので今度はSelectしてみます。

Public Sub SQLiteTest3()
    Dim i As Long
    Dim tm As Single
    
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = _
            "Provider=OleSQLite.SQLiteSource.1;" & _
            "Data Source=C:\test.sqlite;"
    cnn.Open
    
    With New ADODB.Recordset
        Set .ActiveConnection = cnn
        .CursorLocation = adUseClient
                
        tm = Timer

        For i = 0 To 10
            .Open "select * from test1", , adOpenStatic, adLockReadOnly
            .Close
        Next
        
        Debug.Print Timer - tm
    End With
End Sub

Public Sub accdbTest3()
    Dim i As Long
    Dim tm As Single
    
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = _
            "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
            "Data Source=C:\test.accdb;"
    cnn.Open
    
    With New ADODB.Recordset
        Set .ActiveConnection = cnn
        .CursorLocation = adUseClient
        
        tm = Timer

        For i = 0 To 10
            .Open "select * from test1", , adOpenStatic, adLockReadOnly
            .Close
        Next
        
        Debug.Print Timer - tm
    End With
End Sub

実行結果 SQLite :8.8秒 Access:2.2秒
おかしいです…SQLiteに早くなって欲しいのに笑selectは完敗ですかね。

Accessでaccdbを使わずに他のDBを使ってみる – SQLite編2

前回の記事でSQLiteのInsertがやたら遅かった件ですが情報がありました。
参考URL:http://d.hatena.ne.jp/ytRino/20100915/1284519761

SQLiteはトランザクションを自動でつけるので遅くなる…ということでしょうか?
前後にトランザクションを明示してやると早くなるらしいです。なのでテストプロシージャを変更してみます。


Public Sub SQLiteTest2()
    Dim i As Long
    Dim tm As Single
    
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = _
            "Provider=OleSQLite.SQLiteSource.1;" & _
            "Data Source=C:\test.sqlite;"
    cnn.Open
    
    With New ADODB.Command
        Set .ActiveConnection = cnn
        cnn.BeginTrans
        .CommandText = "insert into " & _
            "test1 (int1,int2,int3,int4,int5,text1,text2,text3,text4,text5) " & _
            "values (1,2,3,4,5,'a','b','c','d','e')"
        .CommandType = adCmdText
        .CommandTimeout = 0
        
        tm = Timer
        For i = 0 To 30000
            .Execute
        Next
        
        Debug.Print Timer - tm
        cnn.CommitTrans
    End With
End Sub

Public Sub accdbTest2()
    Dim i As Long
    Dim tm As Single
    
    
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = _
            "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
            "Data Source=C:\test.accdb;"
    cnn.Open
    
    With New ADODB.Command
        Set .ActiveConnection = cnn
        cnn.BeginTrans
        .CommandText = "insert into " & _
            "test1 (int1,int2,int3,int4,int5,text1,text2,text3,text4,text5) " & _
            "values (1,2,3,4,5,'a','b','c','d','e')"
        .CommandType = adCmdText
        .CommandTimeout = 0
        
        tm = Timer
        For i = 0 To 30000
            .Execute
        Next
        
        Debug.Print Timer - tm
        cnn.CommitTrans
    End With
End Sub

実行結果 SQLite :8.44秒 Access:13.65秒
SQLiteはとても早くなりました。

Accessでaccdbを使わずに他のDBを使ってみる – SQLite編1

Accessと同じくファイルDBで、しかもAndroidなどにも広く使われている「SQLite」を
バックエンドに置きAccessのaccdbと比べて利点があるのかを調べてみます。

まず接続からいきます。

どうやら「SQLite」用のOLEDBプロバイダは本家で開発しておらず、サードパーティ製を使うしかないようです。ODBCドライバもサードパーティならあるようです。ここではOLEDBプロバイダを利用して実験します。

SQLite OLE DB Provider:http://cherrycitysoftware.com/ccs/providers/provsqlite.aspx

SQLiteのテーブル等を作成するために管理ツールも準備します。「tkSQLite」を使いました。
tkSQLite:http://reddog.s35.xrea.com/wiki/TkSQLite.html

早速実験してみます。以下のテーブルを使います。

[test1]テーブル

  • id integer 主キー インクリメント
  • int1 integer
  • int2 integer
  • int3 integer
  • int4 integer
  • int5 integer
  • text1 text
  • text2 text
  • text3 text
  • text4 text
  • text5 text

このテーブルに3万回Insertします。
実行方法はADODB.CommandオブジェクトでExecuteを3万回実行して速度を見ます。

“Insert into test1
(int1,int2,int3,int4,int5,text1,text2,text3,text4,text5)
values(1,2,3,4,5,’a’,’b’,’c’,’d’,’e’)”

Public Sub SQLiteTest()
    Dim i As Long
    Dim tm As Single

    With New ADODB.Command
        .ActiveConnection = _
            "Provider=OleSQLite.SQLiteSource.1;" & _
            "Data Source=C:\test.sqlite;"

        .CommandText = "insert into " & _
            "test1 (int1,int2,int3,int4,int5,text1,text2,text3,text4,text5) " & _
            "values (1,2,3,4,5,'a','b','c','d','e')"
        .CommandType = adCmdText
        .CommandTimeout = 0

        tm = Timer
        For i = 0 To 30000
            .Execute
        Next

        Debug.Print Timer - tm
    End With
End Sub

Public Sub accdbTest()
    Dim i As Long
    Dim tm As Single

    With New ADODB.Command
        .ActiveConnection = _
            "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
            "Data Source=C:\test.accdb;"

        .CommandText = "insert into " & _
            "test1 (int1,int2,int3,int4,int5,text1,text2,text3,text4,text5) " & _
            "values (1,2,3,4,5,'a','b','c','d','e')"
        .CommandType = adCmdText
        .CommandTimeout = 0

        tm = Timer
        For i = 0 To 30000
            .Execute
        Next

        Debug.Print Timer - tm
    End With
End Sub

実行結果        SQLite :280秒         Access:18秒
SQLite遅い…これはプロバイダのせいなのかわかりませんが。
ファイルサイズ SQLite :1,171kb Access:2,436kb
同じ行数でもSQLiteのほうが断然ファイルサイズが小さいです。