也谈SQLite效率：Julia和CSV

        转载请注明出处，链接：http://blog.csdn.net/wowotuo/article/details/41909955

        由于对SQLite数据比较感兴趣，所以想了解一下。SQLite工具用的是SQLite expert，感觉还不错。接下来的问题是，如何写SQLite数据库的操作脚本了。由于我用的是Julia，还好，Julia有一个SQLite库（https://github.com/quinnj/SQLite.jl）。

（一）Julia的SQLite库的安装

    Julia其它的库都可以正常安装，但是初次安装SQLite，就是安装不了，很可能是因为deps中有一个SQLite_lib.dll，和其它的库不一样，老是报错。后来，只能求助Github中的SQLite.jl社区，还好，有高手相助，问题终于解决了。感谢呀！
    有相关的问题的，有一定的参考意义。

    具体见：https://github.com/quinnj/SQLite.jl/issues/55

（二）新建表

      假定，在相应的目录下，已经有数据库文件sqliteDB.db。

      julia>using SQLite;
      julia>db=SQLiteDB("C:\\Users\\Administrator\\Desktop\\sqliteData\\sqliteDB.db");

      julia>query(db, "CREATE TABLE newTest (DateTime DateTime, Price Float)");

     这样，一个字段为DateTime,格式为DateTime,另一个字段为Price，格式Float的表已经建立了。

（三）Julia的数据查询和读CSV文件的效率相比：有较大提升。

     当然，数据查询就必须用上query了。

     1、用法

     julia>using SQLite;
     julia>db=SQLiteDB("C:\\Users\\Administrator\\Desktop\\sqliteData\\sqliteDB.db")
     julia>stringSql = "SELECT * FROM IFnew WHERE  CLOSE-LOW>0 ";

     julia>@time qb=query(db,stringSql)  

      部分数据内容如下：

     | Row   | "DateTime"         | "Price" | "Volume" | "Bid"  | "BidVolume" |
|-------|--------------------|---------|----------|--------|-------------|
| 1     | "2014/12/10 9:14"  | 3159.8  | 698      | 3159.6 | 4           |
| 2     | "2014/12/10 9:15"  | 3159.8  | 417      | 3159.8 | 171         |
| 3     | "2014/12/10 9:15"  | 3162.0  | 336      | 3162.0 | 4           |
| 4     | "2014/12/10 9:15"  | 3166.8  | 327      | 3166.8 | 87          |
| 5     | "2014/12/10 9:15"  | 3167.8  | 289      | 3167.8 | 3           |
| 6     | "2014/12/10 9:15"  | 3180.0  | 277      | 3180.0 | 8           |
| 7     | "2014/12/10 9:15"  | 3181.2  | 534      | 3181.2 | 2           |
| 8     | "2014/12/10 9:15"  | 3181.6  | 316      | 3181.6 | 12          |
| 9     | "2014/12/10 9:15"  | 3189.4  | 249      | 3189.4 | 3           |

| Row   | "Ask"  | "AskVolume" |
|-------|--------|-------------|
| 1     | 3159.8 | 3           |
| 2     | 3162.0 | 1           |
| 3     | 3166.8 | 2           |
| 4     | 3167.6 | 1           |
| 5     | 3180.0 | 19          |
| 6     | 3183.6 | 2           |
| 7     | 3182.4 | 11          |
| 8     | 3189.0 | 1           |
| 9     | 3190.0 | 2           |
       2、效率

     （1）SQLite: query

      数据库中的一个50多万行左右的表文件，字段有8个，大约4秒左右。需要说明的是，因为初次使用，表结构并没有进行任何优化，是SQLite直接导入进表的。

      另外，读了一下数据表的另外一张表，存了三个月左右的高频TICK数据，共2222516行，字段大约Date,Time,Price,Volume等7个，花时情况：

      elapsed time: 5.398775057 seconds (659623560 bytes allocated, 19.68% gc time)

      这个数据每一次都有一些变化，不过都在5-8秒左右。我读CSV的话，大约60-90秒不等。

     （2）和读CSV文件相比：

       julia>@time csv= readcsv("E:\\IFData\\IFCSVnew\\IF01.csv") # 这个文件是IFnew表的一半内容，近30万行一个文件

       elapsed time: 12.540684093 seconds (823640112 bytes allocated, 12.28% gc time)

       如果要读二个这样的CSV的话，我估计总要超过20秒左右。

      （3）一次海量数据的查询：1100万笔

        julia>using SQLite;
        julia>db=SQLiteDB("C:\\Users\\Administrator\\Desktop\\sqliteData\\sqliteDB.db")
        julia>stringSql = "SELECT * FROM IFtick2  ";# 1111万笔的文件查询
        julia>@time qb=query(db,stringSql)  

        elapsed time: 40.562814078 seconds (3029593036 bytes allocated, 42.88% gc time)

        单表查询，用时约41秒左右。不过，内存占用飚得历害！不过，估计很少有人，这么蛮干的。这么大的TXT文件没有读过，估计至少需要600秒时间。

        总结：SQLite比读CSV在效率上还是有很大的提升，特别是文件越大，效率相对提升更加明显。大体上估计，效率可以提升5-10倍以上左右。

（四）Julia 的append

    append的用法，很简单。就上面IFnew Table为例。

    （1）一次insert 一笔记录

     假定我们要insert 一笔记录在表的最后面，我们可以如下：

    用法：

    julia> table =["2014-9-15 15:19" 3260.0 700 3360.0 800 3620.0 12]；

    julia> append(db,IFnew,table ) 

    1x1 ResultSet
    | Row | "Rows Affected" |
   |-----|-----------------|
   | 1   | 0               |

  这个表明，insert 记录已经成功，你可以到SQLite expert 的Data中查看，在最后一行，增加了新的记录！

   （2）一次insert N笔记录

    对数据库的insert，尽量要单次多笔进行操作，可以提升操作效率。事实上，append中，已经使用了事务，也就是说，单笔和N笔已经等同。所以，尽量一次insert多笔效率会大幅提高。

    以一次操作二笔记录为例：

    julia> table2 =["2014-9-16 15:19" 3260.0 700 3360.0 800 3620.0 12;"2014-9-16 15:19" 3260.0 700 3360.0 800 3620.0 12]；

    julia> append(db,"IFnew",table2 )   # IFnew 表，table在这里为append的内容

    1x1 ResultSet
    | Row | "Rows Affected" |
   |-----|-----------------|
   | 1   | 0               |

   (3) 一次海量数据的append：一次30万笔

   using SQLite;
   db=SQLiteDB("C:\\Users\\Administrator\\Desktop\\sqliteData\\sqliteDB.db")
   csv= readcsv("E:\\IFData\\IFCSVnew\\IF01.csv") # 大约30万行，9个字段的数据结构
   table =csv[2:end,:] # 去掉第一行字段行
   @elapsed append(db,"IFnew",table)  

   几次结果显示，时间在9-11秒之间。

   (4) 直接用ResultSet来append ：一次220万笔

   using SQLite;
   db=SQLiteDB("C:\\Users\\Administrator\\Desktop\\sqliteData\\sqliteDB.db")
   stringSql = "SELECT * FROM IFtick ";# IFtick是一个200多万行的一个高频数据库，见上
   qb=query(db,stringSql)

   @elapsed append(db,"IFtick",qb)  

   惊喜是，这个库接接口做得非常好，可以直接用从SQLite查询的结果，方便地进行insert!，对格式的限制比较小。

   在效率上，同样经测，完成一次220多万笔记录的insert大约在30秒左右。

   总之，感觉是insert 比查询的效率要低，但是insert这个效率，还行，基本满足我的要求。因为，我并不太关心insert效率，这些操作都是在空余时间后完成的。

  (五）Julia的ResultSet 类型

      SQLite库还不错，SQLiteDB函数返回的是一个ResultSet类型的文件，基本上有矩阵的相关特性。

      比如：

      qb[1,1] ="SFIF0001"

      qb[1,2] =734244.3861111 ;

      qb.values[3][1] =3468.0; 

      .......

      具体地说，ResultSet里面有二个字段，colnames,values.

      sqliteDB.db中的IFnew表的结构是，字段：Code, DateTime,Close,Volume,Amount,High,Low,OpenInterest。
      (1) colnames:
      qb.colnames[1] ="Code"
      qb.colnames[2] ="DateTime"
      qb.colnames[3] ="Close"
       (2)values:
      qb.values[1][1] ="SFIF0001"  # 表明第一个字段的第一个值
      qb.values[2][1] =734244.3861111 #表明第二个字段的第一个值，注这是Matlab型的数值型日期
      qb.values[3][1] =3468.0 # 表明是Close字段的第一个值

        另外，如果你需要，还可以把ResultSet转换成Matrix，这样，你就可以彻底做你想做的事情。（当然DataFrames类型也是可以的，我没有转过！）。

       matdata =convert(Matrix, qb) # ResultSet =>Matrix

      有了这些，就可以开始干活了，对数据库的数据进行封装了。

      总结：Julia的SQLite库做得还不错，最重要的是SQLite数据库不仅简单，效率还不错！对于单机用户而言，推荐！