愚者の経験

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

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

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で使ってもいいのかもしれません。