supersonic Posted January 30, 2017 Share Posted January 30, 2017 (edited) Hello - For a SQLite insert statement I'm passing a 2D array through two For/Next loops - code snippet: Local $sInsertInto2 = "" For $i = 0 To $iCount - 1 $sInsertInto2 &= " (" For $j = 0 To UBound($aTmp, 2) - 2 $sInsertInto2 &= "'" & StringReplace($aTmp[$i][$j], "'", "''") & "', " Next $sInsertInto2 &= "'" & StringReplace($aTmp[$i][$j], "'", "''") & "')" & ((($iCount - 1) = $i) ? (";") : ("," & @CRLF)) Next This works great but due to StringReplace() it is very slow. StringReplace() is used to "escape" single quotes (') and it is only required if a single quote actually is present... Somewhere on the forum there is a faster example by UEZ but I can't find it... Anyone any idea? Edited January 30, 2017 by supersonic Link to comment Share on other sites More sharing options...
jguinch Posted January 30, 2017 Share Posted January 30, 2017 can you give us an example of input string and output string ? Spoiler Network configuration UDF, _DirGetSizeByExtension, _UninstallList Firefox ConfigurationArray multi-dimensions, Printer Management UDF Link to comment Share on other sites More sharing options...
supersonic Posted January 30, 2017 Author Share Posted January 30, 2017 (edited) Input: "'AutoIt'Is'Great!'" Output: "'AutoIt''Is''Great!'" This is not the whole SQLite statement but it is the way a string is transformed IF there is a single quote (red text)... Edited January 30, 2017 by supersonic Link to comment Share on other sites More sharing options...
supersonic Posted January 30, 2017 Author Share Posted January 30, 2017 A 2D array is about [40000+][100+]... Link to comment Share on other sites More sharing options...
Gianni Posted January 30, 2017 Share Posted January 30, 2017 1 hour ago, supersonic said: ..... due to StringReplace() it is very slow. StringReplace() is used to "escape" single quotes (') and it is only required if a single quote actually is present .... have a look to the _SQLite_FastEscape() function instead P.S. 1 hour ago, supersonic said: Somewhere on the forum there is a faster example by UEZ but I can't find it... Anyone any idea? maybe you mean this link? Chimp small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt.... Link to comment Share on other sites More sharing options...
Gianni Posted January 30, 2017 Share Posted January 30, 2017 (edited) deleted a duplicated post Edited January 30, 2017 by Chimp deleted a duplicated post Chimp small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt.... Link to comment Share on other sites More sharing options...
Juvigy Posted January 30, 2017 Share Posted January 30, 2017 Use StringRegExpReplace() and StringRegExp() Link to comment Share on other sites More sharing options...
supersonic Posted January 30, 2017 Author Share Posted January 30, 2017 (edited) Chimp & Juvigy, thank you. The UDF _SQLite_FastEscape() uses StringReplace() w/ case-sense. Using case-sense speeds up things a little bit (22s : 16s). Using SRER seems to as fast as SR w/ case-sense (17s). Using ASM from UEZ is very slow due to DllCall()... With other words: Still any chance to speed it up? Edited January 30, 2017 by supersonic Link to comment Share on other sites More sharing options...
iamtheky Posted January 30, 2017 Share Posted January 30, 2017 Lets see the ASM. All tests, everywhere in the forum, except for the one you just ran, have ASM winning by a large margin. ,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-. |(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/ (_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_) | | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) ( | | | | |)| | \ / | | | | | |)| | `--. | |) \ | | `-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_| '-' '-' (__) (__) (_) (__) Link to comment Share on other sites More sharing options...
jchd Posted January 30, 2017 Share Posted January 30, 2017 4 hours ago, supersonic said: A 2D array is about [40000+][100+]... A really large number of columns! Are you using a transaction to group your inserts? If that isn't good enough, you can try binding values (but then again 100s of DllCalls) Then if the source is a text file, try escaping the whole file first! If the source is a [valid] .csv, use the CLI (sqlite3.exe) to import it. 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...
supersonic Posted January 30, 2017 Author Share Posted January 30, 2017 jchd, yes, many columns... It is a script to import tables from a Progress DB. I fetch the data via ODBC in steps (e. g. 1000). This works really fine. And I use transaction which greatly improves performance. Parsing through the source data array index by index seems to be always slow. So the next thing I try is to put the SQLite statement together and afterwards double the single quotes for char. escaping. Link to comment Share on other sites More sharing options...
supersonic Posted January 30, 2017 Author Share Posted January 30, 2017 jchd, first putting together the SQLite insert/replace statement and afterwards escaping the single quotes (StringReplace w/ case-sense) helped me out. Thank you. Link to comment Share on other sites More sharing options...
jguinch Posted January 30, 2017 Share Posted January 30, 2017 You can also put the whole content in a variable and process the replacement in one shot (with a few steps) : Local $sInsertInto2 = "" For $i = 0 To $iCount - 1 $sInsertInto2 &= " (" & @CRLF For $j = 0 To UBound($aTmp, 2) - 1 $sInsertInto2 &= $aTmp[$i][$j] & @CRLF Next $sInsertInto2 &= ")" & @CRLF Next $sInsertInto2 = StringReplace($sInsertInto2, "'", "''") $sInsertInto2 = StringRegExpReplace($sInsertInto2, "\R\K(?!\h*[()])(\V+)", "'$1'") $sInsertInto2 = StringRegExpReplace($sInsertInto2, "'\K\R(?=')", ", ") $sInsertInto2 = StringReplace($sInsertInto2, @CRLF, "") $sInsertInto2 = StringRegExpReplace($sInsertInto2, "\)\K(?=\h+\()", ", " & @CRLF) $sInsertInto2 &= ";" ConsoleWrite($sInsertInto2 & @CRLF) Spoiler Network configuration UDF, _DirGetSizeByExtension, _UninstallList Firefox ConfigurationArray multi-dimensions, Printer Management UDF Link to comment Share on other sites More sharing options...
supersonic Posted January 31, 2017 Author Share Posted January 31, 2017 (edited) jguinch, that's nearly how I did it: For $i = 0 To $iCount - 1 $sInsertInto2 &= " (" For $j = 0 To UBound($aTmp, 2) - 2 $sInsertInto2 &= "`" & $aTmp[$i][$j] & "`, " Next $sInsertInto2 &= "`" & $aTmp[$i][$j] & "`)" & ((($iCount - 1) = $i) ? (";") : ("," & @CRLF)) Next $sInsertInto2 = StringReplace($sInsertInto2, "'", "''", 0, 1) $sInsertInto2 = StringReplace($sInsertInto2, "`", "'", 0, 1) That's about 4-5 times faster. Setting StringReplace() to case-sensitive also speeds things up. Good enough Thank you. Edited January 31, 2017 by supersonic Link to comment Share on other sites More sharing options...
jguinch Posted January 31, 2017 Share Posted January 31, 2017 You can speed up the script by removing the ternary operator Spoiler Network configuration UDF, _DirGetSizeByExtension, _UninstallList Firefox ConfigurationArray multi-dimensions, Printer Management UDF Link to comment Share on other sites More sharing options...
supersonic Posted January 31, 2017 Author Share Posted January 31, 2017 I'll give it a try... Link to comment Share on other sites More sharing options...
supersonic Posted January 31, 2017 Author Share Posted January 31, 2017 jguinch, indeed, ternary ops consume some time... Like running afterwards some SRs / SRERs At the end there is a difference of about +/- 0,1-0,2 seconds... Link to comment Share on other sites More sharing options...
junkew Posted January 31, 2017 Share Posted January 31, 2017 probably checking first if a ' is in the string will speed up also as checking for a single character existence is much quicker then to use the heavier call to stringreplace directly. FAQ 31 How to click some elements, FAQ 40 Test automation with AutoIt, Multithreading CLR .NET Powershell CMDLets Link to comment Share on other sites More sharing options...
dmob Posted January 31, 2017 Share Posted January 31, 2017 10 hours ago, jguinch said: You can speed up the script by removing the ternary operator I thought ternary operator is faster than If.. Else.. EndIf?? Link to comment Share on other sites More sharing options...
jguinch Posted January 31, 2017 Share Posted January 31, 2017 When I say "remove the ternary operator" I want to say : find a way to remove the condition. I think I had the same result in #13, I did not use any condition to put either "," or ";" Spoiler Network configuration UDF, _DirGetSizeByExtension, _UninstallList Firefox ConfigurationArray multi-dimensions, Printer Management UDF 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