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
[email protected]
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
