Taking out the Slow Parts

Sage Timberline Office, like many accounting systems, allows you to customize the captions that appear on-screen for most fields. To make it easier for the user, it makes sense for Stonefield Query for Sage Timberline Office, the newest member in the Stonefield Query family of products, to display the user's own captions. However, one slight complication is that they aren't stored in a manner that's easily readable. Instead, we have to call a COM object to retrieve the captions. This object returns XML specifying the name of each field and its caption. Given that there are tens of thousands of fields in Timberline, this XML can be over 1 MB in size. Here's what the XML looks like:

<captions>
  <newrecord>
    <tablename>SomeTableName</tablename>
    <fieldname>SomeFieldName</fieldname>
    <fieldcaption>Caption for Field</fieldcaption>
  </newrecord>
  ...
</captions>

We want to use this XML to update the Stonefield Query data dictionary. Our first attempt used XMLTOCURSOR() to put the XML into a cursor, which we then process. It took somewhere around 30 seconds, which seems like forever because it happens every time the user starts Stonefield Query. XMLTOCURSOR() uses the XML DOM object, which gets exponentially slower as the XML gets larger.

The next approach was to manually parse the XML using STREXTRACT(). STREXTRACT() is the perfect function for parsing XML because it was designed to find text between delimiters. If you know the XML structure, it's really easy to use. Here's the code we used:

create cursor __CAPTIONS (FIELDNAME C(119), CAPTION C(60))
for lnI = 1 to occurs('<tablename>', lcXML)
  lcTable   = strextract(lcXML, '<tablename>',    '</tablename>',    lnI)
  lcField   = strextract(lcXML, '<fieldname>',    '</fieldname>',    lnI)
  lcCaption = strextract(lcXML, '<fieldcaption>', '</fieldcaption>', lnI)
  insert into __CAPTIONS values (lcTable + '.' + lcField, lcCaption)
next lnI

Interestingly, this turned out to be way slower than XMLTOCURSOR() (in fact, I stopped it after a couple of minutes, so I don't know how long it would've taken).

It occurred to me while looking at the XML that if we could convert it into comma-delimited text, we could use APPEND FROM to suck it into the cursor. Here's the code for that:

#define ccCRLF chr(13) + chr(10)
lcXML  = strtran(lcXML, '<tablename>')
lcXML  = strtran(lcXML, '</tablename>' + ccCRLF + '<fieldname>', '.')
lcXML  = strtran(lcXML, '</fieldname>' + ccCRLF + '<fieldcaption>', ',"')
lcXML  = strtran(lcXML, '</fieldcaption>' + ccCRLF + '</newrecord>' + ;
  ccCRLF + '<newrecord>', '"')
lcXML  = strtran(lcXML, '</fieldcaption>' + ccCRLF + '</newrecord>' + ;
  ccCRLF + '</vfpdata>', '"')
lcTemp = forcepath(sys(2015) + '.txt', sys(2023))
strtofile(substr(lcXML, 91), lcTemp)
create cursor __CAPTIONS (FIELDNAME C(119), CAPTION C(60))
append from (lcTemp) delimited
erase (lcTemp)

This took 0.7 seconds, 42 times faster than using XMLTOCURSOR().

But still, I wondered why the STREXTRACT() approach was so slow; my experience is that the VFP text handling functions are blindingly fast. I wondered if it had to do the with fourth parameter, the one that specified the occurrence. I rewrote the code to this:

create cursor __CAPTIONS (FIELDNAME C(119), CAPTION C(60))
lnLines = alines(laLines, lcXML)
for lnI = 1 to lnLines
  lcLine = laLines[lnI]
  do case
    case '<TableName>' $ lcLine
      lcTable = strextract(lcLine, '<TableName>', '</TableName>')
    case '<FieldName>' $ lcLine
      lcField = strextract(lcLine, '<FieldName>', '</FieldName>')
    case '<FieldCaption>' $ lcLine
      lcCaption = strextract(lcLine, '<FieldCaption>', '</FieldCaption>')
      insert into __CAPTIONS values (lcTable + '.' + lcField, lcCaption)
  endcase
next lnI

This took 0.4 seconds. Wow! Nearly two orders of magnitude improvement over the initial attempt. It really does pay to take out the slow parts!