Xwolf Posted January 22, 2009 Share Posted January 22, 2009 It was too slow to insert data. Can somebody help me speed it up. Thanks in advance for any help. Code #include <sqlite.au3> #include <sqlite.dll.au3> _SQLite_Startup() _SQLite_Open("abc.db3") _SQLite_Exec(-1,"Create table abc(id)") For $i=1 To 100 Step 1 _SQLite_Exec(-1,"Insert into abc values ('1')") ConsoleWrite($i & @CRLF) Next _SQLite_Close ("abc.db3") _SQLite_Shutdown () Link to comment Share on other sites More sharing options...
KaFu Posted January 22, 2009 Share Posted January 22, 2009 (edited) I think enclosing the loop with a TRANSACTION statements should improve speed... also try to skip the consolewrite ... Additionally you can set a Pragma command directly after the creation of the DB which is said (?) to speed up the DB. Look-up sqlite.org docu for explanation... #include <sqlite.au3> #include <sqlite.dll.au3> _SQLite_Startup() _SQLite_Open("abc.db3") _SQLite_Exec(-1, "PRAGMA synchronous = OFF;") _SQLite_Exec(-1,"Create table abc(id)") _SQLite_Exec(-1, "BEGIN TRANSACTION") For $i=1 To 100 Step 1 _SQLite_Exec(-1,"Insert into abc values ('1')") Next _SQLite_Exec(-1, "COMMIT TRANSACTION") _SQLite_Close ("abc.db3") _SQLite_Shutdown () That gave me +>14:00:17 AutoIT3.exe ended.rc:0 +>14:00:18 AutoIt3Wrapper Finished >Exit code: 0 Time: 1.968 and don't forget that this included the creation of the DB and the shutdown. Edited January 22, 2009 by KaFu OS: Win10-22H2 - 64bit - German, AutoIt Version: 3.3.16.1, AutoIt Editor: SciTE, Website: https://funk.eu AMT - Auto-Movie-Thumbnailer (2022-Nov-26) BIC - Batch-Image-Cropper (2023-Apr-01) COP - Color Picker (2009-May-21) DCS - Dynamic Cursor Selector (2024-Feb-16) HMW - Hide my Windows (2018-Sep-16) HRC - HotKey Resolution Changer (2012-May-16) ICU - Icon Configuration Utility (2018-Sep-16) SMF - Search my Files (2023-Jun-03) - THE file info and duplicates search tool SSD - Set Sound Device (2017-Sep-16) Link to comment Share on other sites More sharing options...
Xwolf Posted January 22, 2009 Author Share Posted January 22, 2009 Thank you very much! KaFu This is my result when using your code. +>21:21:10 AutoIT3.exe ended.rc:0 +>21:21:11 AutoIt3Wrapper Finished >Exit code: 0 Time: 2.174 I will think about your code. Thanks again for your help. Link to comment Share on other sites More sharing options...
KaFu Posted January 22, 2009 Share Posted January 22, 2009 Was just curious what measured effect the COMMIT has... #include <sqlite.au3> #include <sqlite.dll.au3> _SQLite_Startup() _SQLite_Open("abc.db3") _SQLite_Exec(-1, "PRAGMA synchronous = OFF;") _SQLite_Exec(-1,"Create table abc(id)") $begin = TimerInit() _SQLite_Exec(-1, "BEGIN TRANSACTION") For $i=1 To 1000 Step 1 _SQLite_Exec(-1,"Insert into abc values ('1')") Next _SQLite_Exec(-1, "COMMIT TRANSACTION") ConsoleWrite(@crlf & "With COMMIT " & round(TimerDiff($begin),0) & " ms" & @crlf) $begin = TimerInit() For $i=1 To 1000 Step 1 _SQLite_Exec(-1,"Insert into abc values ('1')") Next ConsoleWrite(@crlf & "Without COMMIT " & round(TimerDiff($begin),0) & " ms" & @crlf & @crlf) _SQLite_Close ("abc.db3") _SQLite_Shutdown () Result With COMMIT 116 ms Without COMMIT 682 ms +>14:54:30 AutoIT3.exe ended.rc:0 +>14:54:31 AutoIt3Wrapper Finished >Exit code: 0 Time: 2.167 Clearly shows the speed improvement of the commit... and that most time is spend creating db, initiated connection and shut it down afterwards. The insert part seems to be by far the fastest, note that I increased the inserts to 1000! OS: Win10-22H2 - 64bit - German, AutoIt Version: 3.3.16.1, AutoIt Editor: SciTE, Website: https://funk.eu AMT - Auto-Movie-Thumbnailer (2022-Nov-26) BIC - Batch-Image-Cropper (2023-Apr-01) COP - Color Picker (2009-May-21) DCS - Dynamic Cursor Selector (2024-Feb-16) HMW - Hide my Windows (2018-Sep-16) HRC - HotKey Resolution Changer (2012-May-16) ICU - Icon Configuration Utility (2018-Sep-16) SMF - Search my Files (2023-Jun-03) - THE file info and duplicates search tool SSD - Set Sound Device (2017-Sep-16) Link to comment Share on other sites More sharing options...
Xwolf Posted January 22, 2009 Author Share Posted January 22, 2009 (edited) Yes KaFu I am also confusing about "atomic commit". Edited January 22, 2009 by Xwolf Link to comment Share on other sites More sharing options...
petenyc1 Posted April 6, 2020 Share Posted April 6, 2020 Who would think that a programming post from 11 years ago could help someone. But it did. This improved the speed by 100x. Thanks! Link to comment Share on other sites More sharing options...
jchd Posted April 6, 2020 Share Posted April 6, 2020 If you need to further improve the insert speed, you can group multiple row values in each insert statement, all grouped inside a transaction. insert into mytable values (1), (2), (3), ..., (100) This cuts down by 100 (here) the number of times that the engine has to compile the statement and do its house keeping. For instance, you would only perform 10 inserts instead of 1000 in the example above. 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...
petenyc1 Posted April 6, 2020 Share Posted April 6, 2020 First jchd thank you so much for your help. Doing a project in Sqlite and most of the helpful posts are yours. Especially in your signature the tutorials and help sites. Thank you!!!!!!! Adding barcodes to our hospital supplies. I downloaded a FDA database with 1.25m entries. Loading into Sqlite. First 250,000 took maybe 10-15 hours. I made the changes above and the last 1,000,000 took 30mins!!! I'm going to add your changes and see how quick I can get it done. Then the hospital gave me a list of 40,000 supplies that they use. Going to go through list and search the sql table with the model number. Will get the barcode ID (GTIN) and add to my list. Purchasing will upload back and bam we'll have the ability to enter supplies, implants with barcodes. Will be a huge time saver. Again thank you!!!!!! Link to comment Share on other sites More sharing options...
jchd Posted April 6, 2020 Share Posted April 6, 2020 For really massive changes to a table, it's often beneficial to drop all indices to said tables first, perform changes and only then rebuild indices. Of course your mileage will vary depending on actual context and schema. There are many possibilities to fine-tune a specific schema for a given use case and it's hard to give generic advices beyond basic guidelines. 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