slow salesline entry = 200ms

roynes

What is normal respons time when entring a new salesline?
We have some code that enters 10-20 orderline, this takes 5 sec. I need to find i way to make this faster.
I can not find any modifactions in stadnard NAV 5.0, causing this responstime.
I need to decide if we will try to modify the code, or tune the SQL server?

This Code excample, use 200ms, it use only temporary tables, most off the time is used on the VALIDATE("No.",Item."No.");

WITH TempSalesLine DO BEGIN
SetSalesHeader(TempSalesHeader);
INIT;
"Line No." := 10000;
"Document Type" := TempSalesHeader."Document Type";
"Document No." := TempSalesHeader."No.";
VALIDATE(Type,Type::Item);
VALIDATE("No.",Item."No.");
VALIDATE(Quantity,1);
INSERT;
END;

matttrax

roynes wrote:

most off the time is used on the VALIDATE("No.",Item."No.");

You ran client monitor to verify this?

roynes

I added log statement, betwen all the lines, and i can confirm the respons time varries between 150 and 250 ms.
does anyone know what is normal excecution time for entering a salesline?
/roynes

ara3n

you need to find out why it takes that long.

As mentioned use codecoverage, query profiler to find out if there is a problem.

roynes

Hi And thanks for the answer.
But what i realy ask for now, is, what is normal responstimes when entereing an orderline?

I have also used codecoverage, i end up with some NEXT, FINDSET etc each off them with 15ms, and this end up in 150 to 250ms, today i have done the same test on a native/local databae and i got around the same result. so i think this is a normal respons time. Is there anyone who can confirm this?

Thanks
/roynes

Belias

you said nav 5.0 so SIFT should still exists. if so, disable their maintenance (key design window), because they're degrading write performance.
(this is applicable if you're on sql, obviously)

DenSter

Belias wrote:

you said nav 5.0 so SIFT should still exists. if so, disable their maintenance (key design window), because they're degrading write performance.
(this is applicable if you're on sql, obviously)

You can't just disable all SIFT, that's not the right thing to do.

Belias

either on tables like sales lines? there shouldn't be a lot of records for this table in a "normal" database. :-k

DenSter

No I would not ever tell people to simply turn off SIFT for any table. It's not going to make a difference here anyway, because he's working with temporary tables

Belias

DenSter wrote:

No I would not ever tell people to simply turn off SIFT for any table. It's not going to make a difference here anyway, because he's working with temporary tables

first: #-o oh my! i didn't noticed it was a temptable ](*,)
second: i would disable them, instead...what's the point in maintain SIFT for a small amount of data?
the correct procedure would be to make an average of the number of "sleeping" sales lines in the database, and if they're only a few, then disable SIFTs. I wouldn't have had to state "remove them" so quickly, without analyzing...sorry for the incomplete suggestion

DenSter

Belias wrote:

the correct procedure would be to make an average of the number of "sleeping" sales lines in the database, and if they're only a few, then disable SIFTs. I wouldn't have had to state "remove them" so quickly, without analyzing...sorry for the incomplete suggestion

Right, now you're adding that you would do some analysis before doing this, which is a much better answer :thumbsup:

I have customers with hundreds of thousands of lines in the sales line table, and telling them to simply turn off all SIFT would not be the right thing to do. You have to look at how the table is used before giving any advice.

garak

1. is there some customized code?
2. all mainten. jobs on sql are running?
3. Your Hardware is ok?
4. is it only the Sales line who is "slow"
5. Did you check with the SQL profiler which statements are so slow?
6. viewtopic.php?f=34&t=32657
7. If you find out, that some SIFTS or Keys are the problem, you can optimize that
8. if you find out, that the SQL Server use a wrong index u can test with indexhint or recompile (<- the cpu hase here more to do)

Regards

David_Singleton

Belias wrote:

DenSter wrote:

No I would not ever tell people to simply turn off SIFT for any table. It's not going to make a difference here anyway, because he's working with temporary tables

first: #-o oh my! i didn't noticed it was a temptable ](*,)
second: i would disable them, instead...what's the point in maintain SIFT for a small amount of data?
the correct procedure would be to make an average of the number of "sleeping" sales lines in the database, and if they're only a few, then disable SIFTs. I wouldn't have had to state "remove them" so quickly, without analyzing...sorry for the incomplete suggestion

Now you understand why I said in the other thread, that you can't base analysis on one small piece of data, you need to gather a significant amount of stats and averages, as well as looking at peaks that may ruin the theory.

Belias

David Singleton wrote:

Belias wrote:

DenSter wrote:

No I would not ever tell people to simply turn off SIFT for any table. It's not going to make a difference here anyway, because he's working with temporary tables

first: #-o oh my! i didn't noticed it was a temptable ](*,)
second: i would disable them, instead...what's the point in maintain SIFT for a small amount of data?
the correct procedure would be to make an average of the number of "sleeping" sales lines in the database, and if they're only a few, then disable SIFTs. I wouldn't have had to state "remove them" so quickly, without analyzing...sorry for the incomplete suggestion

Now you understand why I said in the other thread, that you can't base analysis on one small piece of data, you need to gather a significant amount of stats and averages, as well as looking at peaks that may ruin the theory.

Be sure, I've made the link! (and i'll made for the others, too)
http://www.mibuso.com/forum/viewtopic.php?f=33&t=37065&start=15
Thanks for the lesson

kriki

[Topic moved from 'Navision Attain' forum to 'NAV/Navision' forum]

David_Singleton

roynes wrote:

I added log statement, betwen all the lines, and i can confirm the respons time varries between 150 and 250 ms.
does anyone know what is normal excecution time for entering a salesline?
/roynes

Hi Roynes,
I drove to a customer site last week and the drive there took 1 hour and 50 minutes. I am not sure if this is too long. What is the average time it normally takes to drive to a customer site.

PS: if it helps I am driving a Volkswagen Golf.

ara3n

roynes wrote:

What is normal respons time when entring a new salesline?
We have some code that enters 10-20 orderline, this takes 5 sec. I need to find i way to make this faster.
I can not find any modifactions in stadnard NAV 5.0, causing this responstime.
I need to decide if we will try to modify the code, or tune the SQL server?

This Code excample, use 200ms, it use only temporary tables, most off the time is used on

Restore a Cronus unmodified database and test your code.
Then compare that to your numbers.
If you get the same numbers on same hardware, then it's not related to any mods.
It's how nav will work based on the hardware you are running. What is your hardware configuration?

If it takes a lot less, then there are two things that could be the cause. Cronus is much smaller database, so you need to enter and post as many transaction to get it to your db size. If your code takes the same amount on same size as cronus db then to make it faster, you need to look at tunning sql and looking at better hardware. If it takes less time with Cronus db, then you would need to look at your code modification.

garak

David Singleton wrote:

PS: if it helps I am driving a Volkswagen Golf.

#-o :-)