Upgrade to 5 SP1 no objects only executables

tro#1

I would appreciate if you could give me your oppinion on following:

We work with Navision 4. SP 2 right now.
Sequal Server 2005

We do not think about an upgrade to 5SP1 because of many individual modification.

But we were told that we could the an upgrade to 5 SP on the technical side only. So the Navision objects would still be on 4SP2 but the client would show 5SP1.

It would be necessary to get a new license, to convert the database and to install new clients.

We would hope that the performance improves because of the new client.

What is your oppinion on this?

Thank you very much

kine

You can do that... do not forget that experiences with NAV 5.00SP1 are fresh, SP1 is released just a month...

tro#1

The only reason why we would do so is because of the performance. Does this make sense? Any experience on that?

diptish.naskar

Hi,

I don't think it will be a good idea to do the same, if you want to work out on the performance related issues then go for the performance tuning rather than going for the 'technical upgrade'. However you will be spending the money for the new licences irrespctive of knowing what performance benefits its going to give you at the end of the day so why not spend the same in performance tuning(if at all it is required).

However, I would suggest if you are really thinking of an upgrade then do please go for a proper upgrade rather than technical upgrade to 5.0, so that you can get the new features also of 5.0.

kine

The new SIFT implementation can gain some performance. But any technical optimalization without optimalizing Indexes will not gain too much. First try to opt the indexes and after that you can upgrade. Or you can upgrade to 5SP1 but connect it to Index optimalization (best in two steps with some short delay between them to see the effect of each step).

Marije_Brummel

Not here.

Dutch release is on May 7th,

Come back on may 8th, then I'll have experience.

DenSter

Actually, from 4.0 SP2 to 5.0 SP1 there are quite a few performance related modifications in the executables, especially on SQL Server, so it would actually be a good idea to do a technical upgrade.

It won't hurt to get your license updated, because you'll still be able to run the 4.0 version. You'll have to do it in cooperation with yourt NAV partner though, and run it on a test system first to evaluate.

ara3n

make sure you test your license with the new client.
I've been asking a new license for another client, who will do the same exe upgrade, and so far I've received 2 licenses, and they all have issues, So this is my 3rd request for a license file.

DenSter

ALWAYS test EVERYTHING before you put it in production

ara3n

DenSter wrote:

ALWAYS test EVERYTHING before you put it in production

It's not just testing, but the wasted time of going back and forth for days waiting on a new license. It shouldn't be that hard to cut a license and get it right the first time.

DenSter

Maybe one day I will tell you all about how much effort it took to get all verticals on one license, only to find out a year later that it STILL wasn't done, and I had to go through the whole thing again.

To be fair to MS though, the way that this licensing is set up internally is not very clear, to say the least.

ara3n

Security by obscurity..

bbrown

You will probably gain some performance with a technical upgrade (it's probably worth it). However some features like Bulk Insert may require rethinking some of you code before you see an improvement. This also applies to the use of the newer FIND commands. Even though they were available back in 4.0 SP2 they were not implemented in much of the base product.

SobyOne

Don't forget...

There is a specific feature that allows you to force SQL to use a key in a table when a specific sort order is requested from the Dynamics NAV C/AL code.

This will only affect customers that have more data in one query than will fit in the cache RAM for the SQL server (e.g., 1 out of about 2000 installs).

Slawek_Guzek

In my opinion you WILL gain performance mainly because 5.0 SP1 removes SIFT tables and uses Indexed Views. So:

1. all SIFT maintenance was done by SQL triggers, now it is just index calculation - much faster than trigger code

2. SIFTs were calculated at several buckets levels, (for example for years,months,days when DATE type was present in the SIFTed index) while corresponding Indexed View has only one (most detailed) 'level' - reading might be a bit slower, but writing will be faster - less data to calculate

3. Much less locking due to eliminating many SIFT table writes

4. less tables, smaller database, less I/O, more efficient use of SQL server memory

5. bulk inserts (it has nothing to do with SQL BULK INSERT command - it is just about grouping inserts and updates by Client executable) further shorten time of all table locks

But - as aran said - get and double check new licence before updating production environment...

Slawek_Guzek

Small update,

I've done some tests and found area which preforms slower on 5.0SP1 than on 4.0SP3, much slower

I have a customer with 23GB database, he has quite a few Analysis Reports defined. Item Analysis Entry table has roughly 3.2 mln records.

Client monitor shows that reading from underlying SIFT table under 4.0SP3 takes on average 15msec (pure 4.0SP3 client, no updates), while reading from Indexed View takes on average 80 msec. Moreover displaying the same Analytic Report under 4.0SP3 had generated 74k reads, while on 5.0SP1 85k reads.

Total time opening and browsing the report has increased almost 5 times

girish.joshi

Very interesting result, Slawek.

Quick Question: have they adjusted the clustered indicies on the Ledger Entry Dimension table?

But basically, the situation you are describing is precisely the situation SIFT was originally designed to solve. At some point, if the analysis view is big enough, SIFT style ( or datawarehouse style) calculations will be superior.

So here's an open question - how much longer does it take to create and use cubes in SQL2K5 vs. Analysis Views in NAV.

In my experience, the maintenance of the cube is faster through SQL, and the report is ultimately more usable, however it takes much longer to create the report in SQL.

Slawek_Guzek

Hi,

No, Ledger Entry Dimensions table s untouched, besides there is no SumIndexField defined there. Table 7154 (Item Analysis View Entry) has 3 extra indexes with some SumIndexField defined

I've also done some tests under SSMS with one of query generated by 5.0SP1. I've got VERY weird results:

1. SIFT view defined for primary key for table 7154 has exactly the same number of rows as the table

2. Query run against indexed view summing 52 records out of 3.2m, returned result after 0.2sec

3. Execution plan for above query showed Clustered Index Seek on view's index

4. After dropping index on view the same query (with minor modification - hint with(NOEXPAND) had to be removed) returned result after 0.1 sec (50% faster with no clustered index)

5. The same query with removed 'with(NOEXPAND)' hint, but with existing clustered index left, run for 0.1 sec (like query on non-indexed view)

](*,)

I'm going to play with additional indexes on table 7154 and then redo the tests on 4.0SP3, 5.0PU1 and 5.0SP1

girish.joshi

You're right there aren't any on the Ledger Entry Dimension table. For some reason (crazy) I thought it was being used to determine how to sum the analysis entries.

But what I was getting as still the same issue - is the SQL index being used on the clustered key have the most selective field first (most likely source no.)?

Slawek_Guzek

Hi,

By default there is no SQL index defined for primary key on T7514, so order of fields in clustered index of SQL table and associated SIFT view reflects the order of fields in primary key on T7514, starting with Analysis Area, Analysis View Code, and so on.

In my case indeed Source No. is the most selective field, then Posting Date, so I've defined SQL index for primary key for T7514 starting from Source No.,Posting Date. The trouble is that clustered index on T7514 was recreated according to new field order, but clustered index on SIFT view was not changed. It was recreated, but order of fields was exactly as defined on table primary key (not as in defined SQL index for primary key)

I've recreated clustered index on the SIFT view on SQL level with different field order and I've got at least 10x speed gain, but every time I've save the table in the NAV the SIFT view index has been recreated and reverted to the original.

Now the question is - is there any method at NAV level (not at SQL level) to force different field order on SIFT view index, except of changing fields order on the table key ?

Regards,
Slawek