愚者の経験

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

日別アーカイブ: 6月 22, 2012

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は完敗ですかね。