Looking for very fast StringReplace

[supersonic]

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

[jguinch]

can you give us an example of input string and output string ?

 

[supersonic]

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

[supersonic]

A 2D array is about [40000+][100+]...

 

[Gianni]

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?

[Gianni]

deleted a duplicated post

Edited January 30, 2017 by Chimp
deleted a duplicated post

[Juvigy]

Use  StringRegExpReplace() and StringRegExp()

[supersonic]

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

[iamtheky]

Lets see the ASM.

All tests, everywhere in the forum, except for the one you just ran, have ASM winning by a large margin.

[jchd]

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.

[supersonic]

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.

[supersonic]

jchd,

first putting together the SQLite insert/replace statement and afterwards escaping the single quotes (StringReplace w/ case-sense) helped me out. Thank you.

[jguinch]

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)

 

[supersonic]

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

[jguinch]

You can speed up the script by removing the ternary operator

[supersonic]

I'll give it a try...

[supersonic]

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...

[junkew]

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.

[dmob]

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?? 

[jguinch]

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 ";"