Filtering String Length Limitations

chrisdf

There have been several threads in the past discussing filter string length limitations. The conclusion seemed to be that 250 (earlier versions) or 449? was a limit.

If you use the new Item Attributes functionality and filter the Item List using an Attribute filter, NAV creates a (very long) filter string that you can see on the Page.

A text variable is now unbounded - does this mean that the character limit on a filter string has "gone away"?

Does anyone know if this is the case and if so from what release of NAV (or CU) it is valid from?

Thanks for any information.

Juhl

Its limitless, more or less.
It max out at 2gb of data i think.

Slawek_Guzek

The Text lenght is still limited but the limit is quite high (2GB)

However there is another limitation, which arises from SQL Server as a platform, and the way how NAV builds the SQL queries.

If you use a pipe in the filter expression

SETFILTER(afield, 'A'|'B'|'C')

NAV prepares a parametrized SQL query executing sp_prepare, which has a WHERE clause looking like

(afield='%1' OR afield='%2' OR afield='%3') --plus another filters if any other has been set

The query is then executed by using sp_execute, and all individual values are passed as the parameters to the query.

Unfortunately, SQL Server has a hardcoded limit of maximum 2100 parameters which can be passed to a query. Therefore quite likely your filter will not be limited by the length of new text variables, but by the number of item numbers separated by the |

(Unless of course NAV coders were smart and coded some workaround for longer filters...)

Slawek

chrisdf

I have been experimenting further with a small test program (below). With this program I did find some limits and it seems to vary between CU versions of 2017 ..... in fact, it seemed to work better on CU5 than on CU8. With my particular database on CU8 the code below started to fall over when I got above 1200 items in my test. This was about 8615 characters in the filter string. When I increased the count to 1300 then the item record showed it only filtered on 1244 items.

I then switched to another database running CU5 and the same code seemed to work to much higher levels (19,000 + characters in the string).

So I really don't have a firm conclusion but there certainly are limits and it seems variable.

Not sure how NAV can filter Item Attributes like this??

Item.FINDFIRST;
filterstring := Item."No.";
FOR kounter := 1 TO 1300 DO BEGIN
Item.NEXT(1);
filterstring := filterstring + '|' + Item."No.";
END;

MESSAGE(FORMAT(STRLEN(filterstring)));

CLEAR(Item);
Item.SETFILTER("No.",filterstring);

MESSAGE(FORMAT(Item.COUNT));

Juhl

CU11 has some fixes for slowness in Item Attribute filtrering. Check it out.

Slawek_Guzek

chrisdf wrote: »

Not sure how NAV can filter Item Attributes like this??

Easy. You prepare your test database with 140+ items and all works like charm