[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.
Yes. Nothing complicated, fortunately. On Tue, Feb 9, 2016 at 4:09 PM, Steven M. McNeese < steven.mcneese at freedomparkdfw.com> wrote: > So are you saying you are just reading data from your SQLite db using C# > and > just need to insert using the SQLite command? > > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Chris > Prakoso > Sent: Tuesday, February 9, 2016 7:56 AM > To: SQLite mailing list > Subject: Re: [sqlite] C# + SQLite - Update/Insert using Transaction is > slower than without. > > Steven, > > I don't use any back-end, the code I pasted here IS my back-end. I opted > for direct SQLite connection. So I don't use EF6 nor Linq. > > Chris > > On Tue, Feb 9, 2016 at 1:47 PM, Steven M. McNeese < > steven.mcneese at freedomparkdfw.com> wrote: > > > Chris, > > > > What are you using in c# for SQLite back end? Ado.net? Linq. Let me > > know and I can help you with bulk inserts. > > > > Sent from my iPhone > > > > > On Feb 9, 2016, at 6:13 AM, Chris Prakoso wrote: > > > > > > Ok. Got it. > > > Now, if only I can get that multiple rows update working on my code, > > > it would be perfect. > > > > > > Thanks a lot, > > > Chris > > > > > >> On Tue, Feb 9, 2016 at 12:07 PM, Clemens Ladisch > > >> > > wrote: > > >> > > >> Chris Prakoso wrote: > > >>>public bool UpdateData(string tableName, > > >> Dictionary fields, List whereKeys) > > >>>{ > > >>>... > > >>>using (SQLiteTransaction transaction = > > >> conn.BeginTransaction()) > > >>>{ > > >>>... > > >>>rowsUpdated = cmd.ExecuteNonQuery(); > > >>>transaction.Commit(); > > >> > > >> Using one transaction for each statement is slow. > > >> (Re-opening the database doesn't help either.) > > >> > > >> You should use a single transaction around all update statements. > > >> > > >> > > >> Regards, > > >> Clemens > > >> ___ > > >> sqlite-users mailing list > > >> sqlite-users at mailinglists.sqlite.org > > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-user > > >> s > > > ___ > > > sqlite-users mailing list > > > sqlite-users at mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > --- > This email has been checked for viruses by Avast antivirus software. > https://www.avast.com/antivirus > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.
Steven, I don't use any back-end, the code I pasted here IS my back-end. I opted for direct SQLite connection. So I don't use EF6 nor Linq. Chris On Tue, Feb 9, 2016 at 1:47 PM, Steven M. McNeese < steven.mcneese at freedomparkdfw.com> wrote: > Chris, > > What are you using in c# for SQLite back end? Ado.net? Linq. Let me know > and I can help you with bulk inserts. > > Sent from my iPhone > > > On Feb 9, 2016, at 6:13 AM, Chris Prakoso wrote: > > > > Ok. Got it. > > Now, if only I can get that multiple rows update working on my code, it > > would be perfect. > > > > Thanks a lot, > > Chris > > > >> On Tue, Feb 9, 2016 at 12:07 PM, Clemens Ladisch > wrote: > >> > >> Chris Prakoso wrote: > >>>public bool UpdateData(string tableName, > >> Dictionary fields, List whereKeys) > >>>{ > >>>... > >>>using (SQLiteTransaction transaction = > >> conn.BeginTransaction()) > >>>{ > >>>... > >>>rowsUpdated = cmd.ExecuteNonQuery(); > >>>transaction.Commit(); > >> > >> Using one transaction for each statement is slow. > >> (Re-opening the database doesn't help either.) > >> > >> You should use a single transaction around all update statements. > >> > >> > >> Regards, > >> Clemens > >> ___ > >> sqlite-users mailing list > >> sqlite-users at mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.
Chris Prakoso wrote: > public bool UpdateData(string tableName, Dictionary > fields, List whereKeys) > { > ... > using (SQLiteTransaction transaction = > conn.BeginTransaction()) > { > ... > rowsUpdated = cmd.ExecuteNonQuery(); > transaction.Commit(); Using one transaction for each statement is slow. (Re-opening the database doesn't help either.) You should use a single transaction around all update statements. Regards, Clemens
[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.
Ok. Got it. Now, if only I can get that multiple rows update working on my code, it would be perfect. Thanks a lot, Chris On Tue, Feb 9, 2016 at 12:07 PM, Clemens Ladisch wrote: > Chris Prakoso wrote: > > public bool UpdateData(string tableName, > Dictionary fields, List whereKeys) > > { > > ... > > using (SQLiteTransaction transaction = > conn.BeginTransaction()) > > { > > ... > > rowsUpdated = cmd.ExecuteNonQuery(); > > transaction.Commit(); > > Using one transaction for each statement is slow. > (Re-opening the database doesn't help either.) > > You should use a single transaction around all update statements. > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.
Chris Prakoso wrote: > I've been testing the performance of my Insert/Update using > Transaction and without, and I found that it is quicker when I don't > use it. Show the code. Regards, Clemens
[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.
Here they are: public bool UpdateData(string tableName, Dictionary fields, List whereKeys) { bool result = false; string sql = ""; List fieldList = new List(); List whereKeyList = new List(); int rowsUpdated = 0; using (SQLiteConnection conn = new SQLiteConnection(this.ConnectionString)) { try { conn.Open(); SQLiteCommand cmd = new SQLiteCommand(conn); using (SQLiteTransaction transaction = conn.BeginTransaction()) { // Build a list of fields need to be updated if (fields.Count > 0) { foreach (KeyValuePair kvp in fields) { cmd.Parameters.AddWithValue(kvp.Key, kvp.Value); fieldList.Add(kvp.Key); } } sql = "update " + tableName + " set " + this.BuildUpdateFieldList(fieldList) + " where " + this.BuildWhereClause(whereKeys); cmd.CommandText = sql; rowsUpdated = cmd.ExecuteNonQuery(); transaction.Commit(); } if (rowsUpdated > 0) result = true; } catch (System.Exception ex) { this.UpdateStatusMessage(ex.Message); } } return result; } On Tue, Feb 9, 2016 at 11:13 AM, Clemens Ladisch wrote: > Chris Prakoso wrote: > > I've been testing the performance of my Insert/Update using > > Transaction and without, and I found that it is quicker when I don't > > use it. > > Show the code. > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.
Hi all, I've been testing the performance of my Insert/Update using Transaction and without, and I found that it is quicker when I don't use it. Anybody has an insight on this? Thanks a lot, Chris
[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.
So are you saying you are just reading data from your SQLite db using C# and just need to insert using the SQLite command? -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Chris Prakoso Sent: Tuesday, February 9, 2016 7:56 AM To: SQLite mailing list Subject: Re: [sqlite] C# + SQLite - Update/Insert using Transaction is slower than without. Steven, I don't use any back-end, the code I pasted here IS my back-end. I opted for direct SQLite connection. So I don't use EF6 nor Linq. Chris On Tue, Feb 9, 2016 at 1:47 PM, Steven M. McNeese < steven.mcneese at freedomparkdfw.com> wrote: > Chris, > > What are you using in c# for SQLite back end? Ado.net? Linq. Let me > know and I can help you with bulk inserts. > > Sent from my iPhone > > > On Feb 9, 2016, at 6:13 AM, Chris Prakoso wrote: > > > > Ok. Got it. > > Now, if only I can get that multiple rows update working on my code, > > it would be perfect. > > > > Thanks a lot, > > Chris > > > >> On Tue, Feb 9, 2016 at 12:07 PM, Clemens Ladisch > >> > wrote: > >> > >> Chris Prakoso wrote: > >>>public bool UpdateData(string tableName, > >> Dictionary fields, List whereKeys) > >>>{ > >>>... > >>>using (SQLiteTransaction transaction = > >> conn.BeginTransaction()) > >>>{ > >>>... > >>>rowsUpdated = cmd.ExecuteNonQuery(); > >>>transaction.Commit(); > >> > >> Using one transaction for each statement is slow. > >> (Re-opening the database doesn't help either.) > >> > >> You should use a single transaction around all update statements. > >> > >> > >> Regards, > >> Clemens > >> ___ > >> sqlite-users mailing list > >> sqlite-users at mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-user > >> s > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.
Chris, What are you using in c# for SQLite back end? Ado.net? Linq. Let me know and I can help you with bulk inserts. Sent from my iPhone > On Feb 9, 2016, at 6:13 AM, Chris Prakoso wrote: > > Ok. Got it. > Now, if only I can get that multiple rows update working on my code, it > would be perfect. > > Thanks a lot, > Chris > >> On Tue, Feb 9, 2016 at 12:07 PM, Clemens Ladisch >> wrote: >> >> Chris Prakoso wrote: >>>public bool UpdateData(string tableName, >> Dictionary fields, List whereKeys) >>>{ >>>... >>>using (SQLiteTransaction transaction = >> conn.BeginTransaction()) >>>{ >>>... >>>rowsUpdated = cmd.ExecuteNonQuery(); >>>transaction.Commit(); >> >> Using one transaction for each statement is slow. >> (Re-opening the database doesn't help either.) >> >> You should use a single transaction around all update statements. >> >> >> Regards, >> Clemens >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users