FYI better if you put it into try/catch or using block, i.e.:
using (SqliteTransaction trans = connection.BeginTransaction())
{
...
trans.Commit();
}
If Commit isn't called then Dispose will rollback.
Anyway, for real speed improvement try DbDataAdapter thing.
Miha
From: John Murray [mailto:j...@murray.gb.com]
Sent: Monday, August 06, 2012 10:48 AM
To: Miha Markic; monodroid@lists.ximian.com
Subject: RE: [mono-android] sqlite optimisation
Whoa - brilliant Miha - you have inadvertently given me the answer
Instead of just putting in
connection.BeginTransaction();
I need to create a var to hold the instance and then use that instance to
commit after the loop thus
Var thisinstance =
connection.BeginTransaction();
for (int go = 0; go < selstr2.Count; go++)
{
//cc2 is the connection.command
cc2.CommandText = selstr2[go]; //array selstr contains
insert statements
cc2.ExecuteNonQuery();
}
thisinstance.Commit();
This delivers a significant speed improvement over the previous where it was
committing each round of the loop. I had tried all flavours of commit until
you mentioned that the begintransation() instance had a commit method
The above works beautifully
Thanks
John Murray
From: Miha Markic [mailto:m...@rthand.com]<mailto:[mailto:m...@rthand.com]>
Sent: 05 August 2012 14:01
To: j...@murray.gb.com<mailto:j...@murray.gb.com>; Discussions related to Mono
for Android
Subject: RE: [mono-android] sqlite optimisation
Hi John,
BeginTransaction() returns you a SqliteTransaction instance which has both
Commit() and Rollback() methods.
However, I doubt that transaction will speed up inserts. Instead, you might
take a look at UpdateBatchSize property of the DbDataAdapter class.
http://msdn.microsoft.com/en-us/library/aadf8fk2.aspx
I suppose it works as well in M4A.
HTH,
Miha
From:
monodroid-boun...@lists.ximian.com<mailto:monodroid-boun...@lists.ximian.com>
[mailto:monodroid-boun...@lists.ximian.com] On Behalf Of John Murray
Sent: Sunday, August 05, 2012 1:13 PM
To: monodroid@lists.ximian.com<mailto:monodroid@lists.ximian.com>
Subject: [mono-android] sqlite optimisation
I am using sqlite in monodroid - I am doing a bulk insert from an external
source
I have read there is no inbuilt way of importing a whole xml file into the
sqlite database
But one can optimise a loop which inserts line by line by using
connection.BeginTransation()
Which is supposed to delay the commit until the loop is over then use
setTransationSuccessful()
However in the monodroid library mono.data.sqlite.dll does not seem to contain
the 'setTransactionSuccessful' method
Nor themethod endTransation())
Given the insert loop below can anyone tell me if I am barking up the wrong
tree trying to stall the committing of inserts in this way? Is there any other
way to speed up a large insert loop like this?
If there is a method in Mono.Data.Sqlite for 'begintransaction()' where is the
obverse 'endtransation' or 'settransationsuccessful()'
As you can see from code below I have also tried various forms of commit.
//==================================
// connection.BeginTransaction();
// connection is a straightforward sqlite connection the above code line is
available in monodroid
// however without some commit or endtransation nothing gets written to the
database
for (int go = 0; go < selstr2.Count; go++)
{
//cc2 is the connection.command
cc2.CommandText = selstr2[go]; //array selstr contains
insert statements
cc2.ExecuteNonQuery();
}
//connection.setTransationSuccessful();
//cc2.Transaction.Commit();
connection.Close();
//===========================================
I'd be grateful for any help at all -
John Murray
_______________________________________________
Monodroid mailing list
Monodroid@lists.ximian.com
UNSUBSCRIBE INFORMATION:
http://lists.ximian.com/mailman/listinfo/monodroid
