NAV ODBC Driver 5.0SP1 Speed Issues

Jonathan2708
Jonathan2708 Member Posts: 552
edited 2008-11-17 in NAV/Navision Classic Client
Hi,

I have just done a technical upgrade of a Navision 3.70 native database to client version 5.0SP1. The basic application is working fine, however we are noticing much slower speeds running queries through the new ODBC driver. The DSNs took a bit of re-configuring due to changes in the new driver (from Simba Technologies) and there is a new limitation on max number of fields which I managed to get round with a registry change. But the speed seems to be a real issue.

Has anybody else noticed this? Any help appreciated,

Jonathan

Answers

  • AlexWiley
    AlexWiley Member Posts: 230
    What are you using to track the performance issue back to ODBC? Were there any server upgrades with the version change? Lots of things can change system performance and if you are coming into the new system with options you weren't using on 3.7 they could also be the cause of the slow down. Some of these include external utilities (FRx, Crystal, COGNOS), system options (automatic cost posting, change log), and of course additional users.
  • Jonathan2708
    Jonathan2708 Member Posts: 552
    For info, I found the slowdown of particular ODBC queries was due to the nature of joins in the query. The original join was simply :

    "Sales Line"."Document No_" = "Sales Header"."No_"

    This worked fine on the 3.70 driver, but in version 5 performed extremely slowly. Obviously this join doesn't reflect the true relationship between these 2 tables but it didn't matter for this query. I then changed the query for version 5 driver to have the full join as follows :

    "Sales Line"."Document Type" = "Sales Header"."Document Type"
    "Sales Line"."Document No_" = "Sales Header"."No_"

    This instantly gave a dramatic increase in speed. I guess the new query planner in the version 5 driver depends on the correct relationships used in the joins.

    I've found a variety of quirks with the version 5 (Simba) ODBC driver that were not a problem in 3.70, such as :

    - By default doesn't support >255 fields in a table (need to edit registry to fix this)
    - Blank dates are now returned blank as opposed to 01/01/1900 or 01/01/1753 previously
    - Problem using field captions on certain tables


    Jonathan

Categories