愚者の経験

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

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

[Access]AccessでSQLiteを使用するメリット

SQLiteがAccess自身のデータベースと何が違うかというとやっぱり
・ファイルの容量制限がAccess(2GB)を超えられる
・トリガーが使える
この2点が大きくアドバンテージを得る部分でしょう。

他にも一応メリットになりそうなものは
・SQLが標準に近い(データベースサーバ移行時に再利用しやすい)
・使える構文、演算子がSQLiteのほうが豊富
・関数を追加可能(Cで書く必要が…)
こんな感じでしょうか。

ただデメリットもあり、
・リレーション(外部キー)がない(多分)
・ファイルサイズがAccessよりも大きい(約1.3倍)
・おなじみ通貨型(固定小数点型)がない(SQLiteの小数点型はよくわかりませんm(__)m)
等が挙げられます。

[Access]SQLiteをODBCで使用する件解決?

参考URL:http://kzworks.at.webry.info/200908/article_41.html
上記にSQLiteのODBCドライバを改造して公開されている方がいらっしゃいましたので
使わせていただきましたところちゃんとODBCリンク使えました。

SQL Server Compact EditionのODBCドライバありませんね~

[Access]SQLiteをODBCで使ってみようと思ったんですが…

昔の投稿に「ODBCは枯れつつある技術でこれからはADO」みたいに書いたんですが
実際Accessは2013からADPを使えませんので「やっぱりODBC」と思って
「これから開発するときはODBCか…じゃあAccessのDB使わずに他の組み込みDBで
最初からODBC接続で開発したほうがアップサイジングしやすいし…」
と思ってSQLiteのODBCドライバーがあったのでちょっとやってみたんですが…

ODBCドライバーをインストールしてDSNを作るまでは良かったんですが
リンクテーブルを作ろうとしたら「out of memory」「SQLSetConnectAttr」等が
エラーを吐いてどうしようもないです…知っている方がいたら教えてくださいm(__)m

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のほうが断然ファイルサイズが小さいです。