[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Chris Prakoso]

Here they are:

        public bool UpdateData(string tableName, Dictionary<string,object>
fields, List<string> whereKeys)
        {
            bool result = false;
            string sql = "";
            List<string> fieldList = new List<string>();
            List<string> whereKeyList = new List<string>();
            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<string, object> 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 <clemens at ladisch.de> 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
>