On Jan 15, 2007, at 5:04 PM, Tim Jones wrote:
Hi Folks,
I've stumbled my way through the setup of a REALSQL database and
all seems to be working --- except that it's slow as molasses in
January (in the Northern hemisphere :) ) while importing a large
flat text file.
I have a text file that contains approximately 120K lines that are
separated by vertical bar symbols (pipes - | ). I parse the data
out of each line using NthField. The result is over 45 minutes to
parse the flat file into two datatables on a G5 1.8GHz. Any
thoughts to speed this up? The relevant code is below:
No REALSQL Performance fans out there?
I've even taken this further worrying that the NthField manipulation
might be the issue and changed the line parsing to use an array and
the Split command, but the time is the same implying that the issue
is in the actual commit of the data to the database.
Tim
--
Tim Jones
[EMAIL PROTECTED]
Dim curRecord As DatabaseRecord
Dim rs As New RecordSet
Dim f As FolderItem
Dim fs As TextInputStream
Dim curline, thePath, theFile, theArchiveID, theLink As String
Dim theVol, theQFA, thePathID, lnCount As Integer
Dim theSize As Int64
f = GetFolderItem("/private/tmp/seriescat1.log",
FolderItem.PathTypeShell)
fs = f.OpenAsTextFile
lnCount = 0
While Not fs.EOF
curline = fs.ReadLine
// curline would look something like this:
// VL:c|193536|1|0|1514|/Developer/Applications/Xcode.app/
Contents/Resources/XcodeRefLib/releasenotes/
lnCount = lnCount + 1
If Left(curline, 13) = "archive ID = " Then
theArchiveID = NthField(curline, "= ", 2)
ElseIf Left(curline, 3) = "VL:" Then
theVol = Val(NthField(curline, "|", 3))
theSize = Val(NthField(curline, "|", 4))
theQFA = Val(NthField(curline, "|", 5))
// This gets the full file and path from the text file
thePath = NthField(curline, "|", 6)
// this gets the files path (dirname) and catches symlinks
If InStr(thePath, Chr(0)) <> 0 Then
thePath = NthField(thePath, Chr(0), 1)
theLink = NthField(thePath, Chr(0), 2)
Else
theLink = ""
End If
thePath = ReplaceAll(dirname(thePath), "'", "''")
theFile = ReplaceAll(filename(thePath), "'", "''")
Debug.Print "SQL Select: " + "select * from paths where
pathname='" + thePath + "'"
rs = App.CatalogDB.SQLSelect("select * from paths where
pathname='" + thePath + "'")
if rs.RecordCount = 0 Then
curRecord = New DatabaseRecord
Debug.Print "No matching records for " + thePath + " in
paths table"
EditField1.SelText = thePath + EndOfLine
curRecord.Column("pathname") = thePath
App.CatalogDB.InsertRecord("paths", curRecord)
rs = App.CatalogDB.SQLSelect("select * from paths where
pathname='" + thePath + "'")
thePathID = Val(rs.Field("pathid").Value)
Debug.Print "PathID " + Str(thePathID) + " = " + thePath
curRecord = Nil
Else
Debug.Print "Recordset count = " + Str(rs.RecordCount) + ",
" + "Lastrow was " + Str(app.CatalogDB.LastRowID)
thePathID = Val(rs.Field("pathid").Value)
Editfield1.SelText = "Path entry is " + Str(thePathID) +
EndOfLine
End If
curRecord = New DatabaseRecord
curRecord.IntegerColumn("pathid") = thePathID
curRecord.Column("filename") = theFile
curRecord.Column("archiveid") = theArchiveID
curRecord.IntegerColumn("volume") = theVol
curRecord.Int64Column("filesize") = theSize
curRecord.IntegerColumn("qfa_start") = theQFA
curRecord.Column("linkpath") = theLink
App.CatalogDB.InsertRecord("files", curRecord)
End If
// Tried both committing in the loop each time and outside in
batches.
If lnCount Mod 100 = 0 Then
App.CatalogDB.Commit
App.DoEvents(25)
End If
Wend
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
