The below code is to split a mysql file into a file that MSSQL can
understand ( only the DDL should be manually modified )
It does work however it is verry verry slow , i tried it on a AMD 64 3400 +
machine with 1,5 GB of physical memory installed and after 12 hours i had
SQL files worth of 150 MB
while the source file is 7 GB
is .Net really so slow on IO or am i missing something here
i compiled the executable ofcourse with optimizations and use framework 1.1
I tried to optimize the code by highing up the linecount var to 100.000 or
even a million but it looks like the higher this var gets the slower the app
becomes
i also tried to do the replace function on the sline var but this also has
no noticable effect
annyone some tips that might help me ?? i have the idea that the read is
terrible slow , another thingy that is strange is that when i used the
larger amounts of linecounts i saw the memory allocation switching between
+ _ 120 and 300 MB what i would like to see is that it took the hole + 1
GB of physical memory that is free on the machine ( it does take 99% CPU
also strange that systems is still a bit responsive but i see this as a pro
:-) )
Dim sread As New StreamReader("D:\rawmysqldump.sql") ' this is a file of 7
GB
Dim sline As String = ""
Dim sbodyDDL As String
Dim sBody As String = ""
Dim lineCount As Long
Dim dumpcount As Long
Label1.Text = "0"
Dim swrite As StreamWriter
Do Until sread.Peek = -1
sline = Trim(sread.ReadLine)
If sline.Length > 6 AndAlso sline.Substring(0, 6) = "INSERT"
Then
sBody = sBody.Concat(sBody, sline, vbCrLf)
lineCount += 1
If lineCount = 10000 Then ' generate a new sql file every
10000 records
sBody = sBody.Replace("`", "")
sBody = sBody.Replace("""", "'")
dumpcount += 1
swrite = New StreamWriter("D:\dump\" &
dumpcount.ToString & ".sql")
swrite.Write(sBody)
swrite.Close()
sBody = ""
lineCount = 0
Label1.Text = dumpcount.ToString
Label1.Refresh()
End If
Else
sbodyDDL = sbodyDDL.Concat(sbodyDDL, sline, vbCrLf)
TextBox1.Text = sbodyDDL
TextBox1.Refresh()
End If
Loop
If sBody.Length > 0 Then
dumpcount += 1
swrite = New StreamWriter("D:\dump\kamm" &
dumpcount.ToString & ".sql")
sBody = sBody.Replace("`", "")
sBody = sBody.Replace("""", "'")
swrite.Write(sBody)
swrite.Close()
sBody = ""
dumpcount = dumpcount + lineCount
Label1.Text = dumpcount.ToString
Label1.Refresh()
Application.DoEvents()
End If
swrite = New StreamWriter("D:\kamdump\DDL.sql")
swrite.Write(sbodyDDL)
swrite.Close()
MsgBox(" ready ! ")