[sqlite] UPDATE of many rows is very slow

[Hinrich Aue]

Hello sqlite users,

I believe I have used many ways to speed up the UPDATE of many rows,
But nothing helped so far.

We are opening a transaction,
like this:

             private SQLiteTransaction BeginTransaction(SQLiteConnection 
connection)
             {
                    return connection.BeginTransaction();
             }

and close it at the end of course.
We open one connection, one transaction, and then we update many rows on the 
database.
This means we are cumulate many different SQL statements over quite a few 
tables .

One big part is the update of 12000 records in one table

             protected override void UpdateRows(SQLiteConnection connection, 
IEnumerable<DataRow> rowsToUpdate)
             {
                    var command = new SQLiteCommand(Queries.SQLUpdateDocument, 
connection);

                    foreach (DataRow documentRow in rowsToUpdate)
                    {
                           command.Parameters.AddWithValue("@Filename", 
documentRow[Constants.Col_Document_Filename]);
                           command.Parameters.AddWithValue("@ClassID", 
documentRow[Constants.Col_Document_ClassID]);
                           command.Parameters.AddWithValue("@PageCount", 
documentRow[Constants.Col_Document_PageCount]);
                           command.Parameters.AddWithValue("@DocID", 
documentRow[Constants.Col_Document_GlobalDocID]);
                           command.Parameters.AddWithValue("@ReadOnly", 
documentRow[Constants.Col_Document_ReadOnly]);
                           command.Parameters.AddWithValue("@Confirmed", 
documentRow[Constants.Col_Document_Confirmed]);
                           command.Parameters.AddWithValue("@ParentFolderID", 
documentRow[Constants.Col_Document_ParentFolderID]);
                           command.Parameters.AddWithValue("@SequenceNumber", 
documentRow[Constants.Col_Document_SequenceNumber]);
                           command.Parameters.AddWithValue("@XmlRepr", 
documentRow[Constants.Col_Document_XmlRepr]);

                           command.ExecuteNonQuery();

                           documentRow.AcceptChanges();
                    }
             }

with the query beeing:

UPDATE T_Doc SET
Filename = @Filename,
ClassID = @ClassID,
PageCount = @PageCount,
ReadOnly = @ReadOnly,
Confirmed = @Confirmed,
ParentFolderID = @ParentFolderID,
SequenceNumber = @SequenceNumber,
XmlRepr = @XmlRepr
WHERE ID = @DocID;


the AcceptChanges() does not take long.
The ExecuteNonQuery() is much slower than expected.

PRAGMA foreign_keys = ON;

has been executed before the update command and ClassID and ParentFolderID 
reference other tables.

Anyways - the update is extremely slow, updating 12000 records takes like 15-30 
minutes.

Can anybody help me with this?

Hinrich Aue
Sr. SW Engineer
Kofax Development GmbH

Wentzinger Strasse 19
79106 Freiburg
Germany

Tel: +49 761 452 69 57234
Fax: +49 761 452 69 58734
hinrich....@kofax.com


NEWS FLASH: Kofax Among "Five Most Intriguing Companies at Finovate 2013 for 
Mobile Customer Onboarding" 
<http://www.finovate.com/spring13vid/videos/Kofax.mov>


________________________________

This communication is only for the use of the intended recipient. It may 
contain confidential or proprietary information. If you are not the intended 
recipient or have received this communication in error, please notify the 
sender via phone and destroy this communication immediately.
Kofax Development GmbH
Sitz der Gesellschaft: Freiburg i. Brg.
Registergericht: Amtsgericht Freiburg i.Br.
Registernummer: HRB 7007
Gesch?ftsf?hrer: Bradford Weller, Christian Hefner, Daniel Geiger

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users