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

2016-02-09 Thread Chris Prakoso 
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<string,object> 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.

2016-02-09 Thread Chris Prakoso 
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.

2016-02-09 Thread Clemens Ladisch 
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.

2016-02-09 Thread Chris Prakoso 
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.

2016-02-09 Thread Clemens Ladisch 
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.

2016-02-09 Thread Chris Prakoso 
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.

2016-02-09 Thread Chris Prakoso 
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.

2016-02-09 Thread Steven M. McNeese 
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<string,object> 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.

2016-02-09 Thread Steven M. McNeese 
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