Suppir Posted February 9, 2010 Share Posted February 9, 2010 (edited) Hello. Check this code: #include <Timers.au3> #include <SQLite.au3> #include <SQLite.dll.au3> Global $DB Global $starttime = _Timer_Init() _SQLite_Startup () $DB = _SQLite_Open("sqlite.db") _SQLite_Exec($DB, "CREATE TABLE TABLE1 (Text);") For $i = 1 to 1000 Step 1 _SQLite_Exec($DB, "INSERT INTO TABLE1 VALUES ('Some text');") Next MsgBox(0, "", _Timer_Diff($starttime)) On my PC it takes about 80 seconds. It too slow for writing only 20 kb of data in database. Where is the problem and how to do it faster? Thanks. Edited February 9, 2010 by Suppir Link to comment Share on other sites More sharing options...
whim Posted February 9, 2010 Share Posted February 9, 2010 I would start off by timing DB creation and data insertion separately - maybe it's just the creation that takes a long time ? Link to comment Share on other sites More sharing options...
Suppir Posted February 9, 2010 Author Share Posted February 9, 2010 (edited) I would start off by timing DB creation and data insertion separately -maybe it's just the creation that takes a long time ?No, the base is created in a moment, and then veeeery slowly grows up to 20 kb. I think the problem in slow INSERT. Edited February 9, 2010 by Suppir Link to comment Share on other sites More sharing options...
Suppir Posted February 9, 2010 Author Share Posted February 9, 2010 OK, I've got it. I should write this way:#include <Timers.au3> #include <SQLite.au3> #include <SQLite.dll.au3> Global $DB _SQLite_Startup () $DB = _SQLite_Open("sqlite.db") _SQLite_Exec($DB, "CREATE TABLE TABLE1 (Text);") _SQLite_Exec($DB, "BEGIN;") Global $starttime = _Timer_Init() For $i = 1 to 1000 Step 1 _SQLite_Exec($DB, "INSERT INTO TABLE1 VALUES ('Some text');") Next _SQLite_Exec($DB, "COMMIT;") MsgBox(0, "", _Timer_Diff($starttime))Time = 0,3 sec. It 270 times faster then first code.In this way we starting transaction, then putting inside data, then closing transaction. Link to comment Share on other sites More sharing options...
PsaltyDS Posted February 9, 2010 Share Posted February 9, 2010 OK, I've got it. I should write this way: #include <Timers.au3> #include <SQLite.au3> #include <SQLite.dll.au3> Global $DB _SQLite_Startup () $DB = _SQLite_Open("sqlite.db") _SQLite_Exec($DB, "CREATE TABLE TABLE1 (Text);") _SQLite_Exec($DB, "BEGIN;") Global $starttime = _Timer_Init() For $i = 1 to 1000 Step 1 _SQLite_Exec($DB, "INSERT INTO TABLE1 VALUES ('Some text');") Next _SQLite_Exec($DB, "COMMIT;") MsgBox(0, "", _Timer_Diff($starttime)) Time = 0,3 sec. It 270 times faster then first code. In this way we starting transaction, then putting inside data, then closing transaction. Nice, learned something today! Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
jchd Posted February 9, 2010 Share Posted February 9, 2010 I didn't catch this thread before.As a rule of thumb, one should always wrap bulk inserts inside a transaction. SQLite is ACID (look here) and that means that individual inserts need to be committed to disk individually. That translates into several to many writes for a single insert. Typical rate for stock IDE/SATA 7200 rpm disk is about 12 TPS (transaction per second)or lower.By default, SQLite uses auto-commit: each operation is wrapped inside an internally-generated transaction.Now when you group your inserts into an explicit transaction, the required writes are merged and only applied by the time the COMMIT is executed. The effect is a dramatic improvement in speed, just as you experienced.As a sidenote, if ever your database is shared and if other process(es) are possibly reading/writing the database asynchronously, then you should use BEGIN IMMEDIATE / COMMIT. This has to do with the type of locks that are created / held, and directly effects the moments the lock is applied.Lastly, there is little difference for a memory-based database as the write rate is no more bounded by disk rotation speed. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now