slow filter on a flowfield

ccorreia

In GL Entry I added a new flowfield (Comment Text(50))
The calcformula:
Lookup("IC Comment Line".Comment WHERE (Transaction No.=FIELD(Entry No.),Table Name=CONST(G/L Entry),Transaction Source=CONST(Created)))

If I go to form 20 or table 17 and make a filter <>'' on the new field Comment, I get the window (enu.jpg) and takes some time to get the result filtered.

In G/l Entry already exists a flowfield that when searched there's no problem. The "G/L Account Name", when searched don't produces the window that appears in my new field.

Some idea in what is missing?

(NAV 5.0 SP1 and SQL Database)

kriki

NAV has to scan all records of G/L Entry and for each record has to do an extra read to get the description, then NAV can decide if it needs the G/L Entry record or not.
It is normal that it is slow.

matttrax

A boolean variable (the EXIST FlowField) is only 1 bit. It's a 1 or 0, plain and simple. Comparison on that is pretty fast. Your text variable can be 50 bytes, or 400 bits. Each one of which may have to be compared. So your Text filter could take as much as 400 times as long as your boolean filter. And that's just for direct comparison, not including wild cards and capitalization. I know that's not perfect Big O, but you get the point.

Add the fact that it has to lookup the value for every single record in the main table, then do a comparison to see if it falls within the filter, then stick it in the results set, and yeah...it'll be pretty slow.

matteo_montanari

matttrax wrote:

A boolean variable (the EXIST FlowField) is only 1 bit. It's a 1 or 0, plain and simple. Comparison on that is pretty fast. Your text variable can be 50 bytes, or 400 bits. Each one of which may have to be compared. So your Text filter could take as much as 400 times as long as your boolean filter. And that's just for direct comparison, not including wild cards and capitalization. I know that's not perfect Big O, but you get the point.

Add the fact that it has to lookup the value for every single record in the main table, then do a comparison to see if it falls within the filter, then stick it in the results set, and yeah...it'll be pretty slow.

Hi

A boolean Value is a 1 Byte value, alligned to 4 Byte (32bit) boundary

Matteo

matttrax

Eto wrote:

A boolean Value is a 1 Byte value, alligned to 4 Byte (32bit) boundary

Ashamed to say I haven't looked at how much space variable types take up in about 10 years. :oops: Who would have thought you need 4 bytes to store a 1 or 0 now.

Either way a boolean is smaller and faster than a large text field.

ccorreia

Thanks for all answers!
I'm more enlighted