Been messing around with some lists of words.
Imported them into a sqlite database and doing some processing.
Words 49500
No Transaction 118.5 Seconds
In Transaction 7.7 Seconds
Words 262000
No Transaction 395.8
In Transaction 38.9
(I got distracted from my original goal to look into the speed with and without a transaction)
-Bob Gordon
Can’t really comment about this without more information. What “processing” are you doing?
Jay,
The importing and processing are really not the issue. The point is that using a transaction can make a big difference in performance.
I didn’t realize how big a difference until I timed it. Thought it was interesting enough to share.
-Bob
@Bob Gordon Well that is a known limitation. If you don’t explicitly use transactions, one will be created for each INSERT, but the number of transactions per second is limited (because of hard-drive stuff, if I remember well) to ca. 60 per second. It you want to import data as fast as possible, this limit is too low and you should use transactions.
Usually, when importing a large amount of data into a DB, I start with BEGIN TRANSACTION and I count the values inserted. Every 200 values or so (if count MOD 200=0), I close the transaction and re-open one. At the end of the loop, I end the transaction. With a limit of 60 transactions per second, that would allow for 60*200=12000 INSERT per second.
Stephane,
I’ll try what you do (tomorrow) and report the times.
[quote=489925:@Bob Gordon]Jay,
The importing and processing are really not the issue. The point is that using a transaction can make a big difference in performance.
I didn’t realize how big a difference until I timed it. Thought it was interesting enough to share.
-Bob[/quote]
That is not specific to XOJO, it is one of the common techniques to optimize a bulk import process regardless of the programming language used.
@James Dooley Oh I agree. This has nothing to do with Xojo