从Sqlite删除100行需要15秒以上 [英] It is taking more than 15 seconds to delete 100 rows from Sqlite

问题描述

已更新但仍然很慢。

  public   static   void 删除（ int  Id）
 {
 SQLiteConnection conn; 
 使用（conn =  new  SQLiteConnection（ @  数据源= C：\ Users \ job \ Document \ Visual Studio 2013 \Projects \BulkDeletSQLite\test.sqlite ））
 {
 conn.Open（）; 
  string  delete =   DELETE FROM Person WHERE Id = @MyId; 
 使用（ var  command =  new  SQLiteCommand（delete，conn））
 {
 command.Parameters.AddWithValue（  @ MyId，Id）; 
 command.ExecuteScalar（）; 
} 
 conn.Close（）; 
} 
} 

然后：

  foreach （ var  item  in  db.Values.ToList（））
 {
 CreateTableForDB.Delete（item.Id）; 
} 

解决方案

首先使用 System.Diagnostics.StopWatch class [ ^ ]详细查看该代码，并确切地确定缓慢的位置部分是：使用几个秒表，并分别检查连接构造和打开，事务，以及实际删除本身，然后是Linq代码。但我怀疑它是Linq：你反复调用Delete方法 - 这意味着你重新开启了一个新的连接，发出了一个事务删除并再次关闭它。

我很想打开它一次，使用WHERE Id IN（Ids列表）子句发出单个删除，并查看它的工作速度。我怀疑很多，更快......

0）使用参数化语句。

1）没有理由将交易用于个别操作。

2）停止在每个周期中实例化和处理所有内容;重用你拥有的东西。

3）如果你传递了一个连接，为什么要把它扔出去？

4）WTF和那个Linq ？ ：omg：



5）考虑发送多个ID而不是一个，然后在Delete方法中循环。



6）我还建议不要删除;通常，从长远来看，设置某种状态以指示活动/非活动是一种更好的技术。



粗糙和未准备好，但应该提出一个想法：

  public   static   void 删除（IEnumerable< int> Idlist）
 {
 使用（ SQLiteConnection conn =  new  SQLiteConnection（blah blah））
 {
 conn.Open（）; 
 
  string  delete =  @  DELETE FROM Person WHERE Id = @ id;; 
 使用（SQLiteCommand command =  new  SQLiteCommand（delete，conn））
 { 
 command.Parameters.Add（ new 参数...）; 
 command.Transaction = conn.BeginTransaction（）; 
 
  foreach （ int  id  in  Idlist）
 {
 command.Parameters [ 0 ] .Value = id; 
 command.ExecuteNonQuery（）;  //  考虑保存结果 
} 
 command.Transaction.Commit（） ; 
} 
 conn.Close（）; 
} 
} 

见另外：

通过ADO简化数据库访问。 NET接口 [ ^ ]

Updated but still slow.

public static void Delete(int Id)
        {
            SQLiteConnection conn;
            using (conn = new SQLiteConnection(@"Data Source=C:\Users\job\Documents\Visual Studio 2013\Projects\BulkDeletSQLite\test.sqlite"))
            {
                conn.Open();               
                string delete = "DELETE FROM Person WHERE Id = @MyId";
                using (var command = new SQLiteCommand(delete,conn))
                {
                    command.Parameters.AddWithValue("@MyId", Id);
                    command.ExecuteScalar();           
                }
                conn.Close();
            }
        }

then:

foreach (var item in db.Values.ToList())
{
    CreateTableForDB.Delete(item.Id);
}

解决方案

Start by using the System.Diagnostics.StopWatch class[^] to look at that code in detail, and work out exactly where the slow part is: use several stopwatches, and check the connection construct and open, the transaction, and the actual delete itself all separately, then the Linq code. But I suspect it's the Linq: you are calling the Delete method repeatedly - which means you reapeated open a new connection, issue a transacted delete and close it again.
I would be tempted to open it once, issue a single delete using a "WHERE Id IN (list of Ids)" clause, and see how fast that worked. I suspect a lot, lot quicker...

0) Use a parameterized statement.
1) No reason to use a transaction for an individual operation.
2) Stop instantiating and disposing everything on each cycle; reuse what you have.
3) If you're passing in a connection, why throw it out?
4) WTF with that Linq? :omg:

5) Consider sending in multiple IDs rather than just one and then loop within the Delete method.

6) I also advise against deleting; generally, setting a status of some sort to indicate active/inactive is a better technique in the long run.

Rough and unready, but should give an idea:

public static void Delete(IEnumerable<int> Idlist)
        {
            using (SQLiteConnection conn = new SQLiteConnection(blah blah) )
            {
                conn.Open();

                string delete = @"DELETE FROM Person WHERE Id =@id;";
                using (SQLiteCommand command = new SQLiteCommand(delete,conn))
                {
                    command.Parameters.Add ( new parameter ... ) ;
                    command.Transaction = conn.BeginTransaction() ;

                    foreach ( int id in Idlist )
                    {
                        command.Parameters [ 0 ].Value = id ;
                        command.ExecuteNonQuery(); // Consider saving the result
                    }
                    command.Transaction.Commit();
                }
                conn.Close();
            }
        }

See also:
Simplified Database Access via ADO.NET Interfaces[^]

这篇关于从Sqlite删除100行需要15秒以上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！