It is taking more than 15 seconds to delete 100 rows from Sqlite

Question

0.00/5 (No votes)

See more:

Updated but still slow.

C#

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:

C#

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

Posted 3-Dec-14 6:00am

job mwa

Updated 3-Dec-14 6:24am

v5

Add a Solution

Comments

PIEBALDconsult 3-Dec-14 12:34pm

The updated code still has many problems. And ExecuteScalar is inappropriate in this context -- did IntelliSense trick you? I have updated my suggestion.

[no name] 3-Dec-14 12:44pm

a.) Which part is slow(er),first or "then"?
b.) How many rows the table "Person" contains?

job mwa 3-Dec-14 13:51pm

both and table person contains 200 rows.

[no name] 3-Dec-14 15:19pm

If you make simply a select (instead of delete, but same "WHERE"), same slow?

2 solutions

Solution 2

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:

C#

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[^]

Posted 3-Dec-14 6:09am

PIEBALDconsult

Updated 3-Dec-14 6:47am

v6

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2014-12-03T06:06:00

Solution 1

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...

Posted 3-Dec-14 6:06am

OriginalGriff

Comments

job mwa 3-Dec-14 12:42pm

what do you mean by "issue a single delete using a "WHERE Id IN (list of Ids)" clause"?? i think there maybe a solution here.

PIEBALDconsult 3-Dec-14 12:44pm

http://www.codeproject.com/Tips/93248/SQL-Server-User-Defined-Table-Types-and-Table
(May not work with SQLlite.)

OriginalGriff 3-Dec-14 14:06pm

WHERE IN works with SqLite:
http://www.tutorialspoint.com/sqlite/sqlite_where_clause.htm

PIEBALDconsult 3-Dec-14 14:10pm

Yes, but a table-valued-parameter ?

OriginalGriff 3-Dec-14 14:26pm

Haven't tried one in SqLite: but in this case string concatenation is safe anyway, since the IDs are all int.

PIEBALDconsult 3-Dec-14 14:33pm

I agree that it's "safe enough" in this case. On the other hand, I'm actually not a big fan of IN.

OriginalGriff 3-Dec-14 14:06pm

WHERE...IN works in SQLIte:

string ids = string.Join(",", db.Values.Select(v => v.ToString()));
string sqlCommand = "DELETE FROM Person WHERE Id IN (" + ids + ")";

Under normal circumstances I wouldn't recommend concatenating strings to form SQL commands, but in this case since the source Id values are all integers, SQL injection isn't possible so it's allowed.

PIEBALDconsult 3-Dec-14 14:36pm

Oh, another thought, this loses any ability to know which IDs succeeded and which failed or weren't present -- which may or may not be an issue in any particular circumstance.

job mwa 4-Dec-14 9:17am

WOrked very well thanx alot ill have to read up some more on WHERE IN.

OriginalGriff 4-Dec-14 9:22am

You're welcome!