Select with lineitemarray is very slow

Panorama X
#1

The single line of code

select lineitemarray("PartNumber",";") contains lvPart

selects every record that contains lvPart in any of the PartNumber line item fields. The selection takes half a second in Pan6, but it takes 20 seconds in PanX, for a database with ~10,000 records. It seems that the time increases proportionally with the number of records.

@admin, Is there an optimization issue going on here?

I have a workaround that takes 2-3 seconds consistently, regardless of the number of records, I’m just wondering what is wrong with the code above.

#2

Yes, of course. Panorama doesn’t use indexing, so the search time will increase linearly with the number of records. In this example the search time will also increase with the number of line item fields. This has always been the case since Panorama 1.0, or even OverVUE 1.0 in 1984.

Panorama 6 stored all text in ASCII. Because in ASCII each character is guaranteed to be exactly one byte, text processing is super fast.

Panorama X, like all modern programs, uses Unicode for text storage. Unicode is a complicated format where each character can take up anywhere from 1 to 6 bytes. When using ASCII a character can be compared with a single CPU instruction, in Unicode it can take dozens of CPU instructions to compare one character. Unfortunately there is no way around this (keep in mind that all Apple API’s use Unicode).

#3

Hi Jim, I’m aware of this, and I’ve noticed the speed difference with other functions, however, the combination of select with lineitemarray seemed to be something else, not just ASCII vs Unicode related.

I’ve changed my code to scan each lineitem in a loop as a work around and it is acceptable. Thank you for your comments.

#4

You may very likely be correct. Currently this function uses a regular expression to determine the line item fields, and when used with the select statement this is going to happen over and over again for every record. So I think it might be significantly faster to hand code like this:

select (PartNumber1+";"+PartNumber2+";"+ ... +";"+PartNumber9) contains lvPart

Of course the last number is going to vary depending on how many line item fields you actually have in your database.

Or, here’s a version that will automatically build the formula needed for any number of line item fields, then uses the execute statement to run the selection. This should be just as fast as the hand coded solution above.

let xfields = replace(arraystrip(arrayfilter(dbinfo("lineitemfields",""),cr(),{?(import() regexmatch "^PartNumber[0-9]+$",import(),"")}),cr()),cr(),{+";"+})
execute "select ("+xfields+") contains "+quoted(lvPart)

I would be interested in how much faster this works for you.

Longer term, I’ve made a note to look into this. The solution’s I’ve given above are going to be as fast as it can possibly be, I think, but it would be nice if a simple solution using lineitemarray( would have better performance.

#5

Both suggestions work almost as fast as select lineitemarray in Pan6. Thank you!

Thank you for looking further into this.