[V4RB2] VText search speed

[Kem Tekinay]

On 4/25/05 5:16 AM, Ruslan Zasukhin at sunshine at public.kherson.ua wrote:

> It needs study more deep your db to say something.

I'm not sure what else I can say. Here is the constructor for the table:
  
  name = "MessageText"
  
  fldMessageType = new VString( "MessageType", 8, EVFlag.fNone )
  
  fldMessage = new VText( "Message", 1024, EVFlag.fIndexed +
      EVFlag.fIndexByWords )


Here is the constructor for the database:

  VDatabase( inStorageType )
  
  tblEmail = new VTbl_Email
  tblAttachment = new VTbl_Attachment
  tblHeader = new VTbl_Header
  tblAccount = new VTbl_Account
  tblMessageText = new VTbl_MessageText
  
  linkAccountEmails = new VBinaryLink( "linkAccountEmails", tblAccount,
      tblEmail, _
      EVLinkType.kOne, EVLinkType.kMany, EVOnDeletion.kCascade )
      linkEmailAttachments = new VBinaryLink( "linkEmailAttachments",
      tblEmail, _
      tblAttachment, EVLinkType.kOne, EVLinkType.kMany, _
      EVOnDeletion.kCascade )
 linkEmailHeaders = new VBinaryLink( "linkEmailHeader", tblEmail, _
      tblHeader, EVLinkType.kOne, EVLinkType.kMany, _
      EVOnDeletion.kCascade )
  linkEmailMessage = new VBinaryLink( "linkEmailMessage", tblEmail, _
      tblMessageText, EVLinkType.kOne, EVLinkType.kMany, _
      EVOnDeletion.kCascade )
  
  me.CollationAttribute( EVColAttribute.kStrength ) =
      EVColAttributeValue.kPrimary


Here is the search code:

  dim bs, bs1 as VBitSet
  dim d as Date
  dim dateStrStart, dateStrEnd as string
  dim li as new LocaleInfo
  dim startSecs, endSecs as double
  dim words(), word as string
  
  startSecs = microseconds
  
  term = term.trim
  
  if term = "" then
    zIterator = new My_VIterator( zEmailTbl.SelectAllRecords, zEmailTbl )
  else
    
    select case mnuSearchField.Text
    case "Subject"
      bs = zEmailTbl.fldSubject.FindContains( term )
    case "Content"
      if term.InStr( " " ) = 0 then
        bs1 = gDB.tblMessageText.fldMessage.FindContains( term )
      else
        while term.InStr( "  " ) > 0 //  double-spaces with single spaces
          term = term.ReplaceAll( "  ", " " )
        wend
    
        words = term.Split
        bs1 = gDB.tblMessageText.SelectAllRecords
        for each word in words
          bs1 = gDB.tblMessageText.fldMessage.FindContains( word, bs1 )
        next word
      end if
      
      bs = gDB.linkEmailMessage.FindLinked( bs1, gDB.tblMessageText,
          zEmailTbl )
    case "To"
      bs = zEmailTbl.fldAddressTo.FindContains( term )
    case "From"
      bs = zEmailTbl.fldAddressFrom.FindContains( term )
    case "Date Sent"
      if parseDate( term, d ) then
        d.TotalSeconds = d.TotalSeconds - li.LocationGMTDelta
        dateStrStart = d.V_DateTimeStr
        d.TotalSeconds = d.TotalSeconds + ( 24 * 60 * 60 )
        dateStrEnd = d.V_DateTimeStr
        bs = zEmailTbl.fldDateSentGMT.FindRange( true, dateStrStart,
            dateStrEnd, false )
      end if
    case "Date Received"
      if parseDate( term, d ) then
        d.TotalSeconds = d.TotalSeconds - li.LocationGMTDelta
        dateStrStart = d.V_DateTimeStr
        d.TotalSeconds = d.TotalSeconds + ( 24 * 60 * 60 )
        dateStrEnd = d.V_DateTimeStr
        bs = zEmailTbl.fldDateReceivedGMT.FindRange( true, dateStrStart,
            dateStrEnd, false )
      end if
    end select
    
    if bs = nil then bs = zEmailTbl.SelectNoneRecords
    zIterator = new My_VIterator( bs, zEmailTbl )
  end if
  
  endSecs = microseconds
  
  lbMail.Reset( zIterator.RecordCount )
  bs = nil
  bs1 = nil
  
  UpdateStatus
  fldSearchTime.Text = format( ( endSecs - startSecs ) / 1000000, "#,0.000"
       ) + " secs"


Also, I am using a 50 MB cache. Increasing it to 150 MB did not seem to make
a difference.

As I said, for the 42,000 records in the Email table, all the searches are
very fast, usually less than 0.2 seconds. For the 57,000 records in the
MessageText table, each single-word search takes about 2.5 seconds.

__________________________________________________________________________
Kem Tekinay                                                 (212) 201-1465
MacTechnologies Consulting                              Fax (914) 242-7294
545 Eighth Avenue, Suite 401                          Pager (917) 491-5546
New York, New York 10018                    http://www.mactechnologies.com

To join the MacTechnologies Consulting mailing list, send an e-mail to:
  mactechnologies_consulting-subscribe at yahoogroups.com