Customer.GET slow

mariusswart

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

Belias

What if you do a cust.setcurrentkey("no.") before the get?
Can you share all the sql statement please?

JoergR

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

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

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

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

kriki

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.