Nav2009 vs Nav360 Technical Upgrade Speed Test SQL

TimSimmonds

Hi,

We're planning to do a technical upgrade from 360 (on SQL 2002) to Nav2009 (SQL2008) - using the classic client.

We're ran through some performance tests and although generally Nav2009 is faster (without any code changes I hasten to add) we do have an issue with one of our planning dataports that extract data.

The dataport has some 15 data items, with code OnBeforeExport and OnAfterExport on most of them. I've isolated the particular dataitem that appears to be causing much of the slowdown on Nav2009.

There is a 80-90% speed difference between 360 and Nav2009. Hardware/SQL is configured Optimally on the Nav2009 SQL server and the tests were run as a single user.

Running the client monitor (albiet the 360 version of the DEBUG objects - not sure if there are any newer ones for Nav2009???) shows that the same number of transaction appear to run. I did spot that for a couple of the reads, Nav2009 was issuing 2 server commands when Nav360 was only issuing one.

Example:

Nav2009
IF NOT SpecRecipe.FIND('-') THEN ....
SELECT TOP 1 NULL FROM "NAV2009..... WITH (READUNCOMMITTED) WHERE .....
ID: 151886;Reused: 39;Prepared;Default;Top;Rows: 1;UpdateNoLocks Compute Scalar[2,1];Top[3,2];Clustered Index Seek...
IF SpecRecipe.FIND('-') THEN ...
SELECT TOP 1 NULL FROM "NAV2009.... WITH (READUNCOMMITTED) WHERE .....
ID: 151886;Reused: 40;Prepared;Default;Top;Rows: 1;UpdateNoLocks Compute Scalar[2,1];Top[3,2];Clustered Index Seek...

Nav360
IF NOT SpecRecipe.FIND('-') THEN ...
SELECT TOP 1 NULL FROM "360_Test.... WITH (READUNCOMMITTED) WHERE ....
ID: 9072;Reused: 1954;Prepared;Default;Top;UpdateNoLocks Top[2,1];Clustered Index Seek...
IF SpecRecipe.FIND('-') THEN ...
ID: 3415;Reused: 3408;Prepared;Default;Firehose;Top;Cached;UpdateNoLocks No



I tried also using the newer functions within the Nav2009 version (FINDFIRST,FINDSET ect) but the speed tests came out the same.

I realise that we could restructure the business logic totally to improve performance...but as a simple side by side test it doesn't make sense why there is such a big difference in timings.

All very strange... If anyone has any ideas of what could be the issue or any other suggestions for improvement it would be much appreciated.

Cheers....

Procat

I'm not sure why it would matter for a test user, but have you granted References and Show Plan to $ndo$shadow?
Another thing to check is Index hinting, you'll have to search the forum for more info on that.

Slawek_Guzek

TimSimmonds wrote:

Hi,
We're planning to do a technical upgrade from 360 (on SQL 2002) to Nav2009 (SQL2008) - using the classic client..

A little off-topic but be aware that all NAV clients versions up to NAV5 SP1 inclusive were case in-sensitive, while NAV2009 client is case sensitive ('a' is not the same as 'A' in simple text comparisons).

There are few places in NAV2009 where objects has minor code updates (like changing function parameter from Text to Code) due to case-sensitivity. Some objects from NAV3.6 may not work in NAV2009 client.

Regards,
Slawek

TimSimmonds

Thanks for the tips.
I thought the case sensitivity was set at database level though... we've done some fairly indepth functionality testing and haven't spotted any problems with this but I'll certainly have a look into it.

Cheers...

rhpnt

I did spot that for a couple of the reads, Nav2009 was issuing 2 server commands when Nav360 was only issuing one.

Example:

Nav2009
IF NOT SpecRecipe.FIND('-') THEN ....
SELECT TOP 1 NULL FROM "NAV2009..... WITH (READUNCOMMITTED) WHERE .....
ID: 151886;Reused: 39;Prepared;Default;Top;Rows: 1;UpdateNoLocks Compute Scalar[2,1];Top[3,2];Clustered Index Seek...
IF SpecRecipe.FIND('-') THEN ...
SELECT TOP 1 NULL FROM "NAV2009.... WITH (READUNCOMMITTED) WHERE .....
ID: 151886;Reused: 40;Prepared;Default;Top;Rows: 1;UpdateNoLocks Compute Scalar[2,1];Top[3,2];Clustered Index Seek...

Nav360
IF NOT SpecRecipe.FIND('-') THEN ...
SELECT TOP 1 NULL FROM "360_Test.... WITH (READUNCOMMITTED) WHERE ....
ID: 9072;Reused: 1954;Prepared;Default;Top;UpdateNoLocks Top[2,1];Clustered Index Seek...
IF SpecRecipe.FIND('-') THEN ...
ID: 3415;Reused: 3408;Prepared;Default;Firehose;Top;Cached;UpdateNoLocks No

Where do you spot those 2 server commands in 2009? I only see that in v6 the SpecRecipe has some data that is obviously missing in v36. Please correct me if I'm wrong.

TimSimmonds

Hi rhpnt,

The code I extracted from the client monitor is taken from same transaction. ie the same records are being considered at this time. There are lots of repeats of the same commands as our program loops around a set of Item records and then looks for an associated SpecRecipe record. Each time, 2 SQL commands are issued by Nav2009 client.. but only 1 command on the Nav360 client.

Cheers...

rhpnt

I understand that. But looking at your paste there is no SELECT statement after the SpecRecipe.FIND for v36 but for v6 it is. That means that in the v6 db there is some data that is missing in v36, otherwise there would also be no SELECT statement after SpecRecipe.FIND in v6.

Sog

looking at the code, the specrecipe.find('-') dataset is reused in 360 while it is not in nav2009
The if not find('-') implies that nothing will be done with the first dataset, so I would suggest using a "if specrecipe.isempty then"
This will speed it up but will still make 2 roundtrips to the db.

- edit -
Oh and if the dataset is smaller then 500 records, you should use findset, but if you only need the first one then uses findfirst. This will improve the speed of the NAV 2009, maybe not as fast as the 360, but the difference will be smaller.

TimSimmonds

understand that. But looking at your paste there is no SELECT statement after the SpecRecipe.FIND for v36 but for v6 it is. That means that in the v6 db there is some data that is missing in v36, otherwise there would also be no SELECT statement after SpecRecipe.FIND in v6.

There lies our problem... maybe I'm using client monitor wrong.. but the transaction detail is showing up differently ..one version vs another for particular sections of the code. I was expecting them to be the same at least.... but strangley they are not. :?

looking at the code, the specrecipe.find('-') dataset is reused in 360 while it is not in nav2009

Do you know why this would be the case? Is it just that Nav2009 isnt as fast as 360? I even tried the new functions (FINDFIRST,FINDSET ect) and it didn't help the speed either.

TimSimmonds

Hi,

I thought I'd reply to my own message incase anyone else came across a similiar problem...

I did some further investigation on the problem dataport. Where I initially thought was the major cause of slowdown was not correct, although it certainly wouldn't of helped (interpretation problems reading the Client Monitor output I suppose) :oops: Instead I found a "calcfield" command that was the major factor.

The short answer is, as I found, is that Nav2009 definitely does something different with how Calcfields are processed and suffers if no data is there to find (which is the case with the majority of the records being processed in my example).

Replacing the calcfield command with code improved performance considerably, although careful selection of which "find" command to use should be made based on what you are likely to find. With my example, I found that the FIND(-) command was the optimal one to use, above any of the new Nav2009 FIND commands.

Cheers...