Re: REALSQLDatabase speed

Tue, 16 Jan 2007 11:00:50 -0800 

Hi Trausti,

On Jan 16, 2007, at 11:33 AM, Trausti Thor Johannsson wrote:


You seem to be searching for each record before you insert it,
Yes - searching in the paths datatable for each "thePath" as we split the path off of the file to reduce the size of the files datatable. This means we only store /usr/lib/ once in the paths datatable and then store that entry's ID in the files datatable for the files in / usr/lib. While it's not much of a saving for /usr/lib, if the path looks like:
"/Volumes/RAID 1/Users/tjones/Developer/src/RB Projects/Server Console/"
and contains further folders and hundreds of files, you can see how the space saved could add up quickly. 


a much much quicker way would be to use a dictionary, something like


You are speaking of this code?:
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
If so, I've just removed the lookup and sure enough the performance jumps dramatically. Based on your feedback: 


dim d as dictionary

d = new dictionary
I've not worked with the Dictionary class before. So a more specific code section to add the string to the dictionary would look like this (based on my use of "thePath" as the path string): 

        d.value(thePath) = 1


then you just do before you insert the record


        if Not d.haskey(thePath) then
                curRecord = Nil
                curRecord = New DatabaseRecord
                curRecord.Column("pathname") = thePath
                App.CatalogDB.InsertRecord("paths", curRecord)
        end if

Does that match what you're describing?

Thanks,

Tim
--
Tim Jones
[EMAIL PROTECTED]


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>



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

Reply via email to