Customer.GET slow

mariusswart
mariusswart Member Posts: 1
in NAV/Navision Classic Client
Can someone perhaps cast some light on why this simple Customer.GET could be slow - more than 500 ms?

NAV2009 classic, SQL Server.

Code is in Sales Header table

function: GetCust
IF NOT (("Document Type" = "Document Type"::Quote) AND (CustNo = '')) THEN BEGIN
IF CustNo <> Cust."No." THEN
Cust.GET(CustNo);

SQL:
SELECT "timestamp","No_","Name","Search Name","Name 2","Address","Address 2","City","Contact","Phone No_","Telex No_","Our Account No_","Territory Code","Global Dimension 1 Code","Global Dimension 2 Code","Chain Name","Budgeted Amount","Credit Limit

Answers

  • Belias
    Belias Member Posts: 2,998
    What if you do a cust.setcurrentkey("no.") before the get?
    Can you share all the sql statement please?
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • JoergR
    JoergR Member Posts: 13
    It could be because your SQL Indices are not up-to-date or messed up, so SQL must do a table scan instead of an index seek. Are you recurrently maintaining all indices?

    you can monitor the sql statement with management studio (exceution plan) to see what happens in real.
  • illug
    illug Member Posts: 21
    Are you sure this is the code that is actually slow and not something before/after?

    Have you tried running this code in isolation like on a Form.OnOpen with a hardcoded customer number?

    Which version of SQL server do you have? Using a setcurrentkey will not do anything in this case.
  • JoergR
    JoergR Member Posts: 13
    edited 2016-02-12
    Belias wrote: »
    What if you do a cust.setcurrentkey("no.") before the get?
    Can you share all the sql statement please?

    GET is always using the PK so no matter what key a user selected..

    Edit: sorry repost to illug's answer
  • Belias
    Belias Member Posts: 2,998
    JoergR wrote: »
    Belias wrote: »
    What if you do a cust.setcurrentkey("no.") before the get?
    Can you share all the sql statement please?

    GET is always using the PK so no matter what key a user selected..

    Edit: sorry repost to illug's answer

    Yup, it was just a doublecheck because...well...a slow GET it's a weird problem, and you need to do weird questions to get around it :)
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • kriki
    kriki Member, Moderator Posts: 9,121
    Some things to check:
    -Is there a blob in the customer table? Just the existence of a blob field in it can slow the select down. If the blob field is not used, change the datatype to boolean. Did that once for a customer and the performance increase was stellar...
    -Are you doing regular index rebuilds?
    -Is it always slow or just sometimes?
    -Do you have a MODIFY/INSERT/DELETE on the customer table in the same transaction? This means that the GET becomes a SELECT ... EXCLUSIVE LOCK. This might take longer for locking the record.
    -Copy the select statement to SSMS and run it using the actual execution plan. and also doing 
    set statistics io on;
set statistics time on;

your select;

set statistics io off;
set statistics time off;
    And post it in this topic so we can check it out. Be sure to get a select that is slow and not one that is fast.

    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Categories