从数据库SQLite C＃Service Stack读取SQL数据（数百万条记录）的最快方法是什么？

I am working on Ormlite-ServiceStack with SQLite as a database. I am retrieving the millions (1195935) of records from SQLite database table in single Select query (C# DotNet and Database is SQLite (v4.0.30319)) as below.

我正在使用SQLite作为数据库处理Ormlite-ServiceStack。我正在单个Select查询(C#DotNet和Database is SQLite(v4.0.30319))中从SQLite数据库表中检索数百万(1195935)个记录,如下所示。

Store procedure is not supported in SQLite.

SQLite不支持存储过程。

The whole process is taking more than 30 sec for retrieving the data by single query. How can I improve the performance at Millisecond level. I have tried by other ways like Entity Framework, SQLiteData Adapter but not able to up the speed for fetch the data from database at millisecond level.

通过单个查询检索数据的整个过程花费超过30秒。如何提高毫秒级的性能。我已尝试过其他方式,如Entity Framework,SQLiteData Adapter,但无法提高从毫秒级数据库中获取数据的速度。

My Machine is also very fast Solid State Drive with 16 GB RAM, X64 based Windows 7 professional.

我的机器也是非常快的固态驱动器,16 GB RAM,基于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;
        }
    }

3 个解决方案

#1

There are a few thing you can do to speed things up. (But it will probably not make it a lot faster)

你可以做一些事情来加快速度。 (但它可能不会让它快得多)

Create an index if needed to increase performance for the order by. Look at the execution plan for the query. Or even better, remove the order by if not needed by the consumer.

如果需要,创建索引以提高订单的性能。查看查询的执行计划。或者甚至更好,如果消费者不需要,则删除订单。

Set the capacity of the result list to something that would be close to the result if it is known. new List<ClientSideDataResponse>(1200000);

将结果列表的容量设置为接近结果的容量(如果已知)。 new List (1200000);

Instead of converting to and from string use the real value from the database. (Unless of course the values in the database is actually strings. Then it needs to be redesigned)

而不是转换为字符串和从字符串转换使用数据库中的实际值。 (当然,除非数据库中的值实际上是字符串。然后需要重新设计)

This:

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

Would instead be:

反而是:

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

Same for all other results from the query.

对于查询的所有其他结果也是如此。

#2

ServiceStack has selection methods which do all of this for you.

ServiceStack具有选择方法,可以为您完成所有这些操作。

Chek ServiceStack.OrmLite project. It handles SQLite databases too.

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

You will just have to call something like:

你只需要打电话:

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

#3

I am surprised that no one told you not to retrieve millions of records from a single query. Especially since its tagged asp.net (web). Just pull a total count and the records you need for the current page. Now determine your page size and create your custom pagination and when user clicks on page 'n' retrieve that nth page. For page n the algorithm will be

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

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

if you are using EntityFramework.

如果您正在使用EntityFramework。

#1