从数据库SQLite C#服务堆栈读取SQL数据(数百万条记录)的最快方法是什么？

Question

我正在使用SQLite作为数据库处理Ormlite-ServiceStack。I在单选择查询(C# DotNet and database，v4.0.30319)中从SQLite数据库表中检索数百万(1195935)个记录，如下所示。

SQLite不支持存储过程。

整个过程需要30秒以上才能通过单次查询检索到数据。如何提高毫秒级的性能。我尝试过其他方法，如实体框架、SQLiteData适配器，但无法提高从数据库中以毫秒级获取数据的速度。

我的机器也是非常快的固态驱动器与16 GB内存，X64为基础的Windows 7专业。

public string connectionstring** = "Data Source =  " + System.Configuration.ConfigurationManager.AppSettings["DatabasePath"].ToString() + ";";

public class ClientSideDataResponse
{
    public int ID { get; set; }
    public int ByDevSessionId { get; set; }
    public int ByDeviceId { get; set; }
    public int value1 { get; set; }
    public int value2 { get; set; }
    public int  SequenceId{ get; set; }
    public DateTime Timestamp { get; set; }
}

    public List< ClientSideDataResponse> executeReadQuery_List_ClientSideData() 
    {
        System.Data.SQLite.SQLiteCommand myCommand = new System.Data.SQLite.SQLiteCommand();
        List<ClientSideDataResponse> results = new List<ClientSideDataResponse>();

        String _query = "SELECT ID, ByDevSessionId, ByDeviceId, Value1, Value2,  SequenceId, Timestamp   FROM ClientSideData ORDER BY ID";

        try
        {
            using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(connectionstring))
            {
                using (var cmd = con.CreateCommand())
                {
                    if (con.State == ConnectionState.Closed || con.State == ConnectionState.Broken)
                    {
                        con.Open();
                    }

                    using (var transaction = con.BeginTransaction())
                    {
                        cmd.CommandText = _query;
                        try
                        {
                            System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
                            while (reader.Read())
                            {
                                 ClientSideDataResponse  newItem = new ClientSideDataResponse();

                                if (!string.IsNullOrEmpty(reader["ID"].ToString()) == true)
                                {
                                    newItem.ID = Convert.ToInt32(reader["ID"]);
                                }
                                if (!string.IsNullOrEmpty(reader["ByDevSessionId"].ToString()) == true)
                                {
                                    newItem.ByDevSessionId = Convert.ToInt32(reader["ByDevSessionId"]);
                                }
                                if (!string.IsNullOrEmpty(reader["ByDeviceId"].ToString()) == true)
                                {
                                    newItem.ByDeviceId = Convert.ToInt32(reader["ByDeviceId"]);
                                }  
                                if (!string.IsNullOrEmpty(reader["Value1"].ToString()) == true)
                                {
                                    newItem.Value1 = Convert.ToInt32(reader["Value1"]);
                                }
                                if (!string.IsNullOrEmpty(reader["Value2"].ToString()) == true)
                                {
                                    newItem.Pulse = Convert.ToInt32(reader["Value2"]);
                                }
                                if (!string.IsNullOrEmpty(reader["SequenceId"].ToString()) == true)
                                {
                                    newItem.SequenceId = Convert.ToInt32(reader["SequenceId"]);
                                }
                                if (!string.IsNullOrEmpty(reader["Timestamp"].ToString()) == true)
                                {
                                    newItem.Timestamp = Convert.ToDateTime(reader["Timestamp"].ToString());
                                }

                                results.Add(newItem);
                            }
                            reader.Close();
                        }
                        catch (Exception ex)
                        { 
                            logger.Debug(ex.Message);
                            return results;
                        }

                        transaction.Commit();
                        cmd.Dispose();

                        if (con.State == ConnectionState.Open)
                        {
                            con.Close();
                        }
                        return results;
                    }
                }
            }
        }
        catch (Exception ex)
        {
             logger.Debug(ex.Message);
            return results;
        }
    }

Answer 1

有几件事你可以做，以加快速度。(但它可能不会让它变得更快)

1. 如果需要，创建一个索引以提高order by的性能。查看查询的执行计划。或者更好的是，如果使用者不需要，就移除order by。
2. 将结果列表的容量设置为在已知的情况下接近结果的内容。new List<ClientSideDataResponse>(1200000);
3. 而不是转换到和从string使用真正的值从数据库。(当然，除非数据库中的值实际上是字符串。那么它需要重新设计)

这是：

if (!string.IsNullOrEmpty(reader["ID"].ToString()) == true)
{
     newItem.ID = Convert.ToInt32(reader["ID"]);
}

取而代之的是：

if(!reader.IsDBNull(0)) //0 is the index of the column.
   newItem.ID = reader.GetInt32(0); 

查询的所有其他结果相同。

Answer 2

ServiceStack有为您提供所有这些的选择方法。

Chek ServiceStack.OrmLite项目。它也处理SQLite数据库。

你只需打电话给如下：

var results = Db.Select<ClientSideDataResponse>(); // That's all folks!

Answer 3

我感到惊讶的是，没有人告诉你不要从一个查询中检索数百万条记录。特别是自从它的标签asp.net (网页)。只需提取当前页面所需的总计数和记录即可。现在，确定您的页面大小并创建您的自定义分页，以及当用户单击页面'n‘检索该第n页时。对于第n页，算法将是

 records.Skip( (n-1) * page_size ).Take( page_size )

如果您正在使用EntityFramework。