is there more effective/faster way convert sqlbase to mysql?

[E1M1]

Does anyone know why this script slows down after reaching 200k th line? As I have arrays not strings, it should have constant speed but for some reason it slows down with very large files (file I am trying to convert has over 660k lines)

Here's random sqlbase that came up with googling to test this script with: http://www.kalkyl.com/downloads/AME/ame2006.txt

Also when you notice conversion errors, let me know

expandcollapse popup

#include <Array.au3>

$data_types = ""
$data = FileRead("in.sql")
$lines = StringSplit($data, @LF)
MsgBox(0,0,$lines[0])
$function = ""
$lasttable = ""

Dim $sql[1]


For $i = 1 To $lines[0]
    if(mod($i,100)= 0) Then
        ConsoleWrite("line: "&$i&" "&$i/$lines[0]&"% "&@CRLF)
    EndIf
    $do = true
    if StringLeft($lines[$i],2) = "//" or StringLeft($lines[$i],3) = '{@}' then $do = false
    if $do Then
        If StringInStr($lines[$i], "CREATE TABLE", 1) Then
            $function = "CREATE TABLE"
        ElseIf StringInStr($lines[$i], "$DATATYPES", 1) Then
            $function = "INSERT INTO"
        EndIf
        Switch $function
            Case "CREATE TABLE"
                CreateTable($lines[$i])
            Case "INSERT INTO"
                InsertInto($lines[$i])
        EndSwitch
    EndIf
Next

$sql_str = _ArrayToString($sql, @LF)

Func CreateTable($text)
    If StringInStr($text, "PCTFREE", 1) Then
        Return
    ElseIf StringInStr($text, "/", 1) Then
        $sql[UBound($sql) - 1] = StringReplace($sql[UBound($sql) - 1], ")", @LF & ") ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;", -1)
        $function = "none"
        ReDim $sql[UBound($sql) + 1]
        $sql[UBound($sql) - 1] = @LF
        Return
    EndIf
    If StringInStr($text, "CREATE TABLE", 1) Then
        $lasttable = StringTrimRight(StringTrimLeft($text, StringLen("CREATE TABLE ")), 2)
        $lasttable = StringReplace($lasttable, "SYSADM.", "")
        $text = "DROP TABLE IF EXISTS " & $lasttable & ";" & @LF & $text
    EndIf
    $text = StringReplace($text, "SYSADM.", "")
    $text = StringReplace($text, "INTEGER", "INT(11)")
    ReDim $sql[UBound($sql) + 1]
    $sql[UBound($sql) - 1] = $text
EndFunc   ;==>CreateTable

Func InsertInto($line)
    If StringInStr($line, "$DATATYPES ") Then
        $data_types_string = StringTrimLeft($line, 11)
        $data_types = StringSplit($line, ",")
        ReDim $sql[UBound($sql) + 1]
        $sql[UBound($sql) - 1] = "INSERT INTO " & $lasttable & " VALUES "
        Return
    ElseIf StringLeft($line,1) = "/" Then
        $function = "none"
        $sql[UBound($sql) - 1] = StringTrimRight($sql[UBound($sql) - 1], 1) & ";" & @LF
        Return
    EndIf
    $occurrence = 0
    While 1
        $occurrence += 1
        $str_start = StringInStr($line, '"', 0, $occurrence)
        If @error Or $str_start = 0 Then ExitLoop
        $occurrence += 1
        $str_end = StringInStr($line, '"', 0, $occurrence)
        $rep = substr($line, $str_start, $str_end)
        If StringInStr($rep, "{COMMA}", 1) Then
            $line = ReplaceBetween($line, $str_start, $str_end, $rep)
        EndIf
    WEnd

    $arr = StringSplit($line, ",")

    If $arr[0] - 1 = $data_types[0] Then
        $line = ArrayToLine($arr)
        $line = "(" & $line & "),"
            ReDim $sql[UBound($sql) + 1]
    $sql[UBound($sql) - 1] = $line
    EndIf
EndFunc   ;==>InsertInto

func ArrayToLine($array)
    for $i = 1 To $array[0]
        if StringLeft($array[$i],1) <> '"' then
            $array[$i] = '"'&$array[$i]&'"'
        EndIf
    Next
    return _ArrayToString($array,",",1,$array[0]-1)
EndFunc

Func substr($str, $start, $end)
    $str = StringTrimLeft($str, $start)
    $str = StringLeft($str, ($end - $start) - 1)
    $str = StringReplace($str, ",", "{COMMA}")
    Return $str
EndFunc   ;==>substr

Func ReplaceBetween($str, $start, $end, $replace)
    $left = StringLeft($str, $start)
    $right = StringTrimLeft($str, $end - 1)
    Return $left & $replace & $right
EndFunc   ;==>ReplaceBetween

FileDelete("out.sql")
FileWrite("out.sql",$sql_str)

[Bowmore]

It probably due to you using Redim $sql[uBound($sql) + 1] everytime you add a new row of data.

RE Dim is slow and gets slower the larger your array gets. Try something like this

Func _addLineTo Array(ByRef $sql, $sDataLine, $fFinalRow = False)
Static $iRowsAdded = 1

if $iRowsAdded >= UBound($sql) Then
    Redim $sql[UBound($sql) + 50000]
EndIf

$sql[$iRowsAdded] = $sDataLine
$iRowsAdded +=1

if $fFinalRow Then
    ; Not going to add any more data so
    ; Resize the array to be just large enough to hold the data
    Redim $sql[$iRowsAdded]
EndIf

EndFunc