Extension performance issue

Hi guys

We've upgraded a customer to NAV 2018 with v2 extensions. The fields that used to be on the item table are now spread across 5 different extensions using TableExtensions. We're hitting a weird performance issue with sales orders - when a user looks up an item on the sales lines it takes around 4 seconds for the item list to appear. If we uninstall a specific extension it works really quickly though - the item list appears instantly. The TableExtension in this extension has got 1 field in it, a Boolean. As a test we created a brand new extension where this is the only object in it (and it's still only got the 1 field - no code, no dependencies), uninstalled the original version (at which point performance was good) and installed the new one - at which point it immediately slows back to 3 or 4 seconds.

There are no errors and the only thing in the Windows event log is a long running SQL statement which starts with SELECT TOP (@0) ISNULL("Item"."timestamp",@1) AS "timestamp", goes on to list every field on the table including the table extensions, and finishes with WHERE (ISNULL("Item"."No_",@2)>;=@24) ORDER BY "No_" ASC OPTION(OPTIMISE FOR UNKNOWN, FAST 50).

There are about 40 bespoke fields in total and we weren't hitting any table definition limits on the old database before the upgrade.

Has anybody seen anything similar?

Answers

  • Slawek_GuzekSlawek_Guzek Posts: 1,631Member
    That's the 'beauty' of underlying technical solution used by MSFT to implement extensions v2.

    Post the whole query here, or at least everything from FROM clause to the end, and record a record count from every table involved
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • NAVupNorthNAVupNorth Posts: 5Member
    Hi Slawek

    Sure, the query is in this file https://1drv.ms/t/s!AoVtfXAwsAhAkJJFXIbL4wI4OUj6mA (I couldn't paste the content here, it was being flagged as a security issue!)

    The record counts are:
    Item (and Item$<guid> table extensions) - 33,789
    Value Entry - 5,818,357
    Item Substitution - 19
    Stockkeeping Unit - 0
    BOM Component - 1,019

    The Boolean we get the performance issue after adding is called iShop Dummy

    Thanks
Sign In or Register to comment.