Performance degrade of a report after upgarde frm 3.7 to 5.1

dilla · 2010-03-11

Hi Experts,

I had a DB which was in 3.7 (SQL 2000), which was recently upgarded to 5.1(SQL 2005)

Now there is this customized report which was running without any issues in the 3.7 version, which now when imported into the 5.1 version without making any code or property changes has a severe performance issue. It is taking nearly 12 seconds to show an output, which when run in the earlier version i.e., 3.7, would take a few milli seconds to do the same.

This is the first time am working on a performance issue, cause of which am quite new to this world. I have Googled a lot and done some analysis and have also tried implementing a few, but nothing has worked till now. I would like to tell you my analysis b4 u can give your advice.

1) The DB size is pretty small, the tables used by this reports are "Vendor ledger entry" and "Vendor". But the report uses "CALFIELDS" because of which table "Detailed vendor ledger entry" is used for calculating the sum using CALCSUMS.The number of records in these tables are very very less, just about 40 to 50 records.

2) I tried optimizing the Table through the NAV client and also have tried, a lot of KEY combinations that would help in improving the performance but nothing has worked.I have tried changing the use of SETCURRENTKEY also.

3) I used clientmonitor to trace the report and found that, while performing the CALCSUMS the report is taking 150MS to process one fetch, because of which the report is real slow.

4) The report is using SETCURRENTKEY on fields upon which SETFILTER\SETRANGE are applied, so its making it a mistry to solve this issue.

5) Am providing you with a SQL quiery snipit retrieved via Client monitor,This quiery is executed when report executes the CALCFIELDS on the table 25 for fileds "Invoice Amts" & "Cr. Memo Amounts": SELECT SUM("SUM$Amount") FROM dbo."CompanyName$Detailed Vendor Ledg_ Entry$VSIFT$7" WITH (READUNCOMMITTED, NOEXPAND ) WHERE (("Initial Document Type"=3)) AND (("Vendor No_"='X28894066')) AND (("Posting Date">={ts '2005-01-01 00:00:00.000'} AND .....

So here i am, stuck and helpless as i have no idea about this VSIFT tuning or Index tunings. So can you atleast provide me a small clue, as to were am supposed to head from here to solve this major issue.Is there any thing else that am supposed to do or is there any thing which i have already done wrong ??? #-o

The part which worries me is, the same report worked as dream in 3.7 version but after upgrade its changed completely.

Thank you for reading the whole thing.

Warm regards.....

davmac1 · 2010-03-11

You don't give any details.
5.1 is using managed views.
Have you run the same query in SQL to check performance?
Have you checked the state of your system? Are there any bottlenecks?
One pssible bottleneck is insufficient RAM on the SQL Server. Did you install the new SQL Server on the same computer? What else is running on the SQL Server computer?

vgvozdev · 2010-03-12

Hi,

Try to change key... Try to create key with 3 fields: "Vendor No.","Initial Document Type","Posting Date" - and Anount as SIF.

stryk · 2010-03-12

Hi!
First of all you should search MIBUSO for "SQL Performance" and you'll get gazillions of useful advices :!:

SELECT SUM("SUM$Amount") FROM dbo."CompanyName$Detailed Vendor Ledg_ Entry$VSIFT$7" WITH (READUNCOMMITTED, NOEXPAND ) WHERE (("Initial Document Type"=3)) AND (("Vendor No_"='X28894066')) AND (("Posting Date">={ts '2005-01-01 00:00:00.000'} AND .....

Try this in SSMS:

USE [YourNavDatabase]
GO
CREATE INDEX ssi01 ON ."CompanyName$Detailed Vendor Ledg_ Entry$VSIFT$7"
("Vendor No_", "Initial Document Type", "Posting Date", ...) -- all fields from the WHERE clause
INCLUDE
("SUM$Amount")

Does this one help?

dilla · 2010-03-12

Hello Experts,

Thank you for all your responses.

I made the changes as suggested of creating "Ven NO.,Initial Doc type,Posting Date" with SIFT Key of "Amount". But that has NOT made any impact.

I have checked the DB for the bottlenecks, but till now there has been none, the DB cache is good, so i don't see it as an issue.

I would like to share with you the SQL query that is executed when i run this report on the 3.7 (SQL2000)
SQL Statement
"SELECT SUM("s7"),SUM("s8") FROM "COmpanyName$380$4" WITH (READUNCOMMITTED) WHERE (bucket=7 AND f35=2 AND f9='XXXX' AND ((f4>={ts '2010-01-01 00:00:00.000'} AND f4<={ts '2010-03-12 00:00:00.000'})) AND f3=1)"
SQL Index
Initial Document Type,Vendor No.,Posting Date,Currency Code,Entry Type,Entry No.

This report executes without any problems in the 3.7 Version. But when i run the same over the 5.1 it takes all the time in the world to throw the output.

So what more can be done to make this happen ?? I really appreciate your help till this point.

Cheers...

stryk · 2010-03-12

The general difference between SIFT and VSISFT is this:

SIFT aggregates the table data (using SQL triggers) on multiple levels aka "buckets". Most of the data is useless, so the number of SIFT buckets should be reduced to a necessary minimum (usually this is just one). WHen SIFT data is generated it is naturally that "zero-sums" are created. As "0" is pointless in a sum-statement all those records should be deleted. Once all this SIFT optimization was implemented, SIFT perform quite OK. Only writing the data is a little bit of a pain, due to the SQL trigger.

VSIFT are Views which run the summing directly on the source table. The sums are grouped according to the "KEy" definition, thus, the level of aggregation is pretty much the same as with optimized SIFT. VSIFT Views are indexed, thus the sum-data is sort of "materialized", to improve performance.
Writing to VSIFT is faster than with SIFT, as no trigger code is executed. But: the "in the fly" sum-calculation also creates "zero-sums". THese "records" are also useless, but cannot be deleted as previously with SIFT. Hence, VSIFT could "contain" many more records than optimized SIFT, and this could affect certain reading queries.

To optimize VSIFT you could do this:
- Disable all VSIFT which contain mostly "zero-sums"
- Create "Keys" for VSIFT purposes on the desired aggregation level
- Create optimized SQL site indexes (using INCLUDE, since SQL 2005)

Did you try my proposed index? Any result?

David_Singleton · 2010-03-12

Hi Joerg,

what is the difference between your suggestion and Val's. As I see it, creating the key in Navision (especially for someone that is an absolute beginner like the OP here) is much safer in Navision than SQL.

Will there be a significant performance difference doing it your way?

dilla · 2010-03-12

Hi Joerg,

I had the same question as David here, i did create the Key combination suggested with the "Amount" defined as a SIFT key through the NAV client, but did NOT inflict any difference on the performance.

So will this make any difference when i do it over SQL keeping INCLUDE also in Query ??

Kind Regards,
Dilla

DenSter · 2010-03-12

Here's a simple rule: if you don't understand what a query means, and you don't have a person on staff that does, or someone at your partner, then simply don't run it.

stryk · 2010-03-12

David Singleton wrote:

Hi Joerg,

what is the difference between your suggestion and Val's. As I see it, creating the key in Navision (especially for someone that is an absolute beginner like the OP here) is much safer in Navision than SQL.

Will there be a significant performance difference doing it your way?

Well, first of all I prefer to fix such thing directly within SQL Server, leaving NAV outside. Thus, I don't need to fiddle with tables and all the related impact, means I could implement such changes during business. Call it a matter of habbit ...

Technically there's not necessarily such a big difference to Val's prposal.
The difference could be this: if you create the new VSIFT View via NAV, the new object (e.g. the clustered index) could be somewhat larger than a non-clustered index. I just ran a brief example an my local Cronus for a demo:

Table "CRONUS 501$Calendar Entry"; contains 9378 records
Key (PK): "Capacity Type,No.,Date,Starting Time,Ending Time,Work Shift Code"
Corresponding VSIFT: CRONUS 501$Calendar Entry$VSIFT$0; "contains" 9378 records (!)
Size: 106 pages (= 848KB)

The replacement NCI:
CREATE INDEX [ssi_99000757_1] ON [dbo].[CRONUS 501$Calendar Entry]
([Capacity Type],[No_],[Date],[Starting Time],[Ending Time],[Work Shift Code])
INCLUDE ([Capacity (Total)],[Capacity (Effective)])
Size: 88 pages (= 704KB)

So having in mind this is CRONUS, but still, the relative difference is actually quite remarkable: the NCI is ca. 17% smaller than the VSIFT.

I have to admit, that this is not always the case, in some cases the NCI can even be larger than the VSIFT - this actually really depends on the effectivity af the VSIFT summing, hard to predict from a remote pespective ...

To make a long story short:
My primary reason for creating such SQL indexes is just that I don't want to fiddle with NAV; I'm faster in SSMS.
Depending on the "Key", NCI could be more efficient than new VSIFT (normally I use such NCI to replace VSIFT with poor effectivity - means: few records, or mostly containing zero-sums)

Cheers,
Jörg

stryk · 2010-03-12

dilla wrote:

Hi Joerg,

I had the same question as David here, i did create the Key combination suggested with the "Amount" defined as a SIFT key through the NAV client, but did NOT inflict any difference on the performance.

So will this make any difference when i do it over SQL keeping INCLUDE also in Query ??

Kind Regards,
Dilla

Please also see my previous comment. Regarding the INCLUDEs:
If you create the index (non-clustered) without the "Amount" field, SQL Server has to perform a "Key Lookup" operation, means looking up the "Amount" from the Clustered Index, which simply takes more time.
If you place the "Amount" into the index, this "Key Lookup" will not be performed, as all the required data is directly available from the index. But: it is not wise to put decimal fields in the B-Tree of an index, as this could remarkably increase the size of such an index. Placing "to-be-summed" fields like "Amount" as INCLUDEed column will only attach the values to the "Leaf Nodes" of the index. Hence, the non-clustered index is created properly an basis of the WHERE clause filter fields, plus it includes the required sum-data from the select. Thus, this NCI is fully covering the query, performing as best as possible, while keeping its size reasonable.

DenSter · 2010-03-12

First of all, the first thing I would do is remove MaintainSIFTIndex from the primary key. There is absolutely no need for VSIFT on the primary key, because in that case you would have a 1-to-1 relationship with the PK and the indexed view, and it would faster to read from the table itself, without the extra overhead of VSIFT. You can't make that change in SQL Server, you have to go into the NAV table design.

Second, you don't need to make the PK the clustered index, so I don't think you are making a fair comparison. The change that you make there can be done on NAV table object directly, and you will not need to maintain objects in multiple places, for multiple databases (think about all the customers that have a development database, and a QA database, and a test database and a training database). Doing these types of changes on SQL Server adds a level of complexity to object maintenance that not many customers are capable of handling themselves.

Third, with the latest exe's, you MUST have the key as well as the index match in C/AL code. Unfortunately, many times indexes that are created on SQL Server and not in the NAV table object are simply never used.

Anecdote: at a recent customer, I found many indexes that were created on SQL Server, without making any changes in NAV. These indexes had prefixes that identified where they came from (which is not important here), and I can only assume that these were generated by running some sort of script. Looking at actual index usage statistics on SQL Server, we found that NONE of these indexes had any usage at all. Just removing those indexes gave us a more peppy system.

Now I'm not saying there's no use for making changes on SQL Server directly, things like covering indexes can't be done in NAV for instance. BUT, my main objection to this type of advice in the forum is that you have to know what you are talking about, and be able to actively monitor the system for the effectiveness of the changes, and manage these mocifications. In a post like this, where the OP clearly has no clue, in my opinion telling them to make changes to SQL Server directly, is REALLY bad advice.

stryk · 2010-03-12

DenSter wrote:

First of all, the first thing I would do is remove MaintainSIFTIndex from the primary key. There is absolutely no need for VSIFT on the primary key, because in that case you would have a 1-to-1 relationship with the PK and the indexed view, and it would faster to read from the table itself, without the extra overhead of VSIFT. You can't make that change in SQL Server, you have to go into the NAV table design.

Second, you don't need to make the PK the clustered index, so I don't think you are making a fair comparison. The change that you make there can be done on NAV table object directly, and you will not need to maintain objects in multiple places, for multiple databases (think about all the customers that have a development database, and a QA database, and a test database and a training database). Doing these types of changes on SQL Server adds a level of complexity to object maintenance that not many customers are capable of handling themselves.

Third, with the latest exe's, you MUST have the key as well as the index match in C/AL code. Unfortunately, many times indexes that are created on SQL Server and not in the NAV table object are simply never used.

Uhm, I'm afraid you're getting a little bit off the track here ... I'm not sure how your reply is related to this topic ... Sure, you're stating the obvious, but please just have in mind: this "Calendar Entry" was just a random example I picked, actually because it has the largest index on my CRONUS db, so it's the only one to at least get some result. I never intended to claim this as a general solution and THE way to do this ...
Basically I was just trying to answer David's question ... sorry if there's something misunderstood ...

I absolutely agree that VSIFT tuning is a more complex matter where you need to know what you're doing.

DenSter wrote:

Anecdote: at a recent customer, I found many indexes that were created on SQL Server, without making any changes in NAV. These indexes had prefixes that identified where they came from (which is not important here), and I can only assume that these were generated by running some sort of script. Looking at actual index usage statistics on SQL Server, we found that NONE of these indexes had any usage at all. Just removing those indexes gave us a more peppy system.

That's what I always say: "It's not the tool which fixes a problem, it's the person using it." You give someone a hammer and tell him how to use it, but still someone will hit his thumb ...

DenSter wrote:

Now I'm not saying there's no use for making changes on SQL Server directly, things like covering indexes can't be done in NAV for instance. BUT, my main objection to this type of advice in the forum is that you have to know what you are talking about, and be able to actively monitor the system for the effectiveness of the changes, and manage these mocifications. In a post like this, where the OP clearly has no clue, in my opinion telling them to make changes to SQL Server directly, is REALLY bad advice.

Yes and No. I agree that SQL site tuning requires some advanced knowlede, you really have to know what you're doing here. But I am convinced, that if you want to optimize a system properly, sooner or later you HAVE to do it directly in SQL. Further I'd say, SQL tuning is no rocket-science (even if many others try to {t|s}ell this), and every NAV/SQL admin and developer should IMHO dig into that stuff.
I don't want to judge the skill-level of any community member, so I'll always try to answer as best as I can. How should others be able to learn if you're keeping things secret just because you THINK they won't understand? I have a more optimistic approach ...
And: If I'm wrong or if others - like you - have other opinions and experiences, they should be encouraged to state it. Isn't that the general idea of this web platform? But I guess I'm also drifting off into a topic which has nothing to do with the original question ...

Let's get back to business. So IF you try this SQL Index and IF it does not help, then delete it using

DROP INDEX ssi01 ON "CompanyName$Detailed Vendor Ledg_ Entry$VSIFT$7"

And IF this index could indeed help you, then consider it as an example how efficient SQL tuning could be.
IF you're not sure about all this, if you don't understand this or have any doubts then simply leave it (hope this enough of a disclaimer).

DenSter · 2010-03-12

stryk wrote:

Uhm, I'm afraid you're getting a little bit off the track here ... I'm not sure how your reply is related to this topic ...

I was replying to your elaborate post, so if you think that is off track, it's becuase of the example that you used yourself.

stryk wrote:

please just have in mind: this "Calendar Entry" was just a random example I picked, actually because it has the largest index on my CRONUS db, so it's the only one to at least get some result.

Alright let's just agree that it was a bad example then, because I think there was an unfair comparison (those percentages in themselves don't mean squat for performance if you don't look at it in the right context), as well as the wrong "fix".

stryk wrote:

That's what I always say: "It's not the tool which fixes a problem, it's the person using it." You give someone a hammer and tell him how to use it, but still someone will hit his thumb ...

I agree 100% with that.

stryk wrote:

I am convinced, that if you want to optimize a system properly, sooner or later you HAVE to do it directly in SQL

I do not, and will never, agree with the approach that the first thing you do is to just jump into making index changes on SQL Server directly, while there are many things you can do in NAV that can make huge performance improvements. I will ALWAYS prefer to make changes in NAV first, and ONLY if that doesn't make enough of a difference, only then do you go into SQL Server.

stryk wrote:

Further I'd say, SQL tuning is no rocket-science (even if many others try to {t|s}ell this), and every NAV/SQL admin and developer should IMHO dig into that stuff.
I don't want to judge the skill-level of any community member, so I'll always try to answer as best as I can. How should others be able to learn if you're keeping things secret just because you THINK they won't understand? I have a more optimistic approach ...

Again I agree 100% with the "not rocket science" part, but I don't think it's a good advice to give someone who clearly does not understand this a script and say "run this it will make your problem go away" without addressing the underlying fundamentals. I also don't want to just guess at someone's skill level, but it's not always 'rocket science' to figure out skill level, just read this quote from the initial post:

dilla wrote:

So here i am, stuck and helpless as i have no idea about this VSIFT tuning or Index tunings

stryk wrote:

If I'm wrong or if others - like you - have other opinions and experiences, they should be encouraged to state it. Isn't that the general idea of this web platform?

Exactly, I was stating my opinion. Are YOU are allowed to write YOUR opinion, but I should keep mine to myself?

Let's not make this a spitting contest, because I really value your opinion. I don't think there's a "right" and a "wrong", just different approaches. I am just saying that, in my opinion, doing what you can do in NAV first is a better approach. It's easiest to manage, and it's all proprietary, so as long as you synchronize the NAV objects, each copy of the database is tuned in one effort.

stryk · 2010-03-12

DenSter wrote:

Exactly, I was stating my opinion. Are YOU are allowed to write YOUR opinion, but I should keep mine to myself?

That was not my point.

DenSter wrote:

Let's not make this a spitting contest, because I really value your opinion

I'm afraid that whatever I try to state you misunderstand (or maybe I am misunderstanding?). Honestly, I don't know what all this fuss is about. So I retreat from this discussion as IMHO all this is way off topic.

DenSter · 2010-03-12

stryk wrote:

IMHO all this is way off topic.

If you don't want to be off topic, then don't take it off topic.

Performance degrade of a report after upgarde frm 3.7 to 5.1

Comments

Categories