Filtering String Length Limitations

chrisdf
chrisdf Member Posts: 82
in NAV Three Tier
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.

Comments

  • Juhl
    Juhl Member Posts: 724
    Its limitless, more or less.
    It max out at 2gb of data i think.
    Follow me on my blog juhl.blog
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,692
    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



    Slawek Guzek - www.yitron.co.uk
    Business Central, MS SQL Server, Wherescape RED;
  • chrisdf
    chrisdf Member Posts: 82
    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
    Juhl Member Posts: 724
    CU11 has some fixes for slowness in Item Attribute filtrering. Check it out.
    Follow me on my blog juhl.blog
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,692
    edited 2017-11-07
    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 :)
    Slawek Guzek - www.yitron.co.uk
    Business Central, MS SQL Server, Wherescape RED;

Categories