You seem to be searching for each record before you insert it, a much
much quicker way would be to use a dictionary, something like
(pseudo code)
dim d as dictionary
d = new dictionary
for each record
d.value(string) = 1
end loop
then you just do before you insert the record
if d.haskey(string) then
do nothing
else
commit record
end if
Trausti
On Jan 16, 2007, at 1:04 AM, 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:
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>
