Maintaining SQL Index for secondary keys

mihai.valcea

Hello,

In technical documentation is telling that is not necessary to allow Navision maintain SQL Index for secondary keys because MSSQL will do that.
I do not understand how different C/AL code will be afected.

Thanks

DenSter

Please don't interpret that as 'I can turn off MaintainSQLIndex for all my secondary keys'. Turning off the SQL index might cause performance issues just as much as it might solve others.

You need to understand that in Navision you have KEYS, and in SQL Server you have INDEXES. These are two different things. When you create a new KEY in Navision, the executable will create an INDEX on SQL Server with the same fields, unless you uncheck the MaintainSQLIndex property of the Navision key.

As long as you keep the Navision key active, you can turn the SQL index off and you will not see a difference in C/AL code. You only need the Navision key to use in C/AL code. SQL Server decides on its own which index to use when it receives queries, so technically you don't need the index for the application to work correctly.

The thing is though that you might WANT to keep the index on SQL Server, because that might make data retrieval quicker. You should only turn off the SQL indexes that are not selective enough for SQL Server to ever use them.

DenSter

Very well put Black, I have nothing to add well a least not right now

bbrown

SQL uses both indexes and keys. Indexes are used to retrieve data (as noted above). Keys are used to enforce data constraints. Unique indexes may also be used to enforce data constraints.

Sorting of data retrieved by SQL comes at a cost. It requires system resources, which can be signifcant on large datasets. Getting SQL to retrieve data in the same order that it is needed can improve performance (not always easy).

kriki

Search the forum for SQL. You will find a lot of info on this.

nsrao2k

Hello,

Am trying to import CRM database to SQL Server that is integrated with custom defined solution for Navision v4. I could able to get all the customer data from MySQL server to SQL Server 2005.

MySQL as similar to any RDBMS creates performance and search indices overtime of its usage, notably known as statistics. These are mostly RDBMS dependent but throws major light on the usage of the RDBMS and its bottlenecks while at runtime.

Here, my question is

Is there any scope or possibility for either Navision or SQL Server to manually modify these search indices.

Else are there any ways to fine tune a Navision and in what respects.

Just to let you know, am an entry level knowledge on Navision.

Thanks in advance for your suggestions.

Warm Rgds,
Srinivas

kriki

SQL Statistics:you can do this by creating a DB maintenance plan with the Enterprise Manager.

Maintaining indexes and SIFT-indexes, you can do that in Navision. Open the table, show the keys. You probably need to make some fields visible.
There are some properties to not maintain an index or SIFTindex on SQL.
Another field is SIFTLevels. With this you can decide NOT to maintain some levels of the SIFT-field.
With "SQLIndex", you can also change the Navision-key to another index on SQL.
I am not going into detail, because there is some documentation on how to proceed. But I advice to have the work done by a (very) experienced Navision and SQL specialist. The person must know Navision well (functional and technical) and have a good knowledge on how SQL works with indexes.

kriki

BlackTiger wrote:

Be VERY carefull with SIFTLevels!

Because Navision(SQL) will still calculate this fields without any error messages or warnings. Just through "SUM(Field)". It can reduce performace dramatically without any errors.

But also increase performance dramatically....

bbrown

The performance improvement you get from disabling SIFT is on writes/deletes not reads. Hot tables that have a lot of SIFTindexs can generate a lot of additional db calls during a write/delete operation. Disabling SIFTindexes that are used infrequently can improve performance.

bbrown

Disabling the right SIFTindexes can have a major performance improvement. The trick is picking the right ones. Pick the wrong one and you can kill performance. If there is not a SIFTindex (depending on filters used) SQL may do a table scan This can have a sizable performance impact on a large table.

Real World Example:

A client I worked with at my old NSC was importing Sales Orders through Biztalk/Commerce Gateway. The typical order was 200+ lines. Initially these order took several minutes to import. Since the order were processed as a single transaction this caused major locking issues.

Disabling SIFT on the Sales Line table reduced order processing time to a few seconds.

kriki

bbrown wrote:

The performance improvement you get from disabling SIFT is on writes/deletes not reads. Hot tables that have a lot of SIFTindexs can generate a lot of additional db calls during a write/delete operation. Disabling SIFTindexes that are used infrequently can improve performance.

And also disabling double SIFT-levels.
E.g.:
Index "G/L Account","Posting Date" : SIFTFIELDS:Amount
Index "G/L Account","global dim code 1",... : SIFTFIELDS:Amount
=> no need to keep the siftlevel on "G/L Account" of the second index because the level is also kept on the first index.

DenSter

BlackTiger wrote:

Sometimes in will work, sometimes - not. Depends on server's hardware.

That is completely false. It has nothing to do with the hardware. By turning off certain SIFT levels (after careful analysis if which ones you need) you can accomplish dramatic increases in performance, regardless of the hardware.

DenSter

Alright here you have it:

BlackTiger wrote:

Depends on server's hardware.

That is completely false. It has nothing to do with the hardware. By turning off certain SIFT levels (after careful analysis if which ones you need) you can accomplish dramatic increases in performance, regardless of the hardware.

bbrown

Fast hardware will tend to hide poor or sloppy database design and maintence. That is not to say you don't also need appropriate hardware, but there is a balance.

kriki

BlackTiger wrote:

E.g.:
Index "G/L Account","Posting Date" : SIFTFIELDS:Amount
Index "G/L Account","global dim code 1",... : SIFTFIELDS:Amount
=> no need to keep the siftlevel on "G/L Account" of the second index because the level is also kept on the first index.

Not true! You MUST keep this level. Why?

Don't mix SQLIndex and SIFTLevel together!

You got me worrying, so I did a test:
I created a new table with some fields in it. and 2 keys with SIFT:
-"G/L Account","Posting Date" : SIFTFIELDS:Amount
-"G/L Account","global dim code 1",... : SIFTFIELDS:Amount

I created 2 pieces of code:
FIRST PIECE:

SETCURRENTKEY("G/L Account","Posting Date");
SETRANGE("G/L Account",'100000');
CALCSUMS(amount);

=> SQL Profiler gives this Select statement:

SELECT SUM("s10") FROM "THEDB"."dbo"."THEDB$50000$0" WITH (READUNCOMMITTED)  WHERE (bucket=@P1 AND f2=@P2)

SECOND PIECE:

SETCURRENTKEY("G/L Account","global dim code 1");
SETRANGE("G/L Account",'100000');
CALCSUMS(amount);

=> The client monitor gives this Select statement:

SELECT SUM("s10") FROM "THEDB"."dbo"."THEDB$50000$0" WITH (READUNCOMMITTED)  WHERE (bucket=@P1 AND f2=@P2)

Now I did NOT maintain SIFTlevel "G/L Account" in index "G/L Account","global dim code 1" anymore and launched my code and this is what the client monitor gives:

SELECT SUM("s10") FROM "THEDB"."dbo"."THEDB$50000$0" WITH (READUNCOMMITTED)  WHERE (bucket=@P1 AND f2=@P2)

Meaning I was right.
But with a surprise: If Navision notices a lower level bucket (in this case with "The Date", it will do a sum of these instead of a sum on the real table!

DenSter

BlackTiger wrote:

Beleive me... If you have CPUx8 (Itanium or Opteron), 64GB RAM, 15000rpm SCSI drives... And 2GB database...

So.. It depends on hardware! :whistle: \:D/

Of course bigger faster hardware will go faster, but it is not the main reason for faster performance. You are basically saying that performance problems are solved mainly by better hardware and that my friend is not a true statement. In fact about 80% of performance gain is by tweaking keys, SIFT levels, code improvements, and things like that.

I did a Navision restore on a 64 CPU, 255 GB RAM state of the art machine in a Unisys hardware lab and that did not go any faster than my laptop because of the way that Navision communicates with SQL server. You can have an ordinary server machine, optimize SIFT levels and get enormous performance increases.

kriki

BlackTiger wrote:

That's why you have to analyse your code and key FlowField usage before SIFTLevel adustments. Bacause following also will work without any errors:

//Table1:
//Key: Field1, Field2, Field3, Field4, Field5; Sum: Amount

Table1.SETRANGE(Field1,'1');
Table1.SETRANGE(Field2,'2');
Table1.SETRANGE(Field3,'3');
Table1.SETRANGE(Field4,'4');
Table1.CALCSUMS(Amount);

But Navision will generate statement "SELECT SUM(Amount) FROM Table1 WHERE ..." instead of using SIFT-tables.

True.
But if you maintain ONLY the SIFT-level on Field5, Navision wil generate a statement SELECT SUM(Field5) FROM Table1.0 WHERE Bucket = .....
Meaning it does a sum on the SIFT-table and not on the real table. And this is generally faster!

kriki

BlackTiger wrote:

Meaning it does a sum on the SIFT-table and not on the real table. And this is generally faster!

Quite controversial point. Depends too much on number of records in SIFT-table. Ofcos it will be faster anyway. But sometimes it will be "horribly slow" vs "just slow".

This is the reason it is important to optimize the tables regularly.

Marije_Brummel

There was a VERY interestiong session about SQL tuning at TechEd. There are loads of tools available in the SQL toolkit. There is also a new Index optimizing toolkit that will be released shortly.

TechEd sessions will be available for download in 2 or 3 weeks, I'll upload them as soon as they become available.

kriki

BlackTiger wrote:

PS: I remember one situation... Once I've seen funny "key" which had "DateTime" field. In result, SIFT-table had MUCH MORE records than original table!

That's because this type of field (together with date-fields) generate standard 3 records per field in the SIFT-table! That is the reason I think it is only necessary to keep the day-level-record and not the year- and month-level. The times Navision can use those to read are (at least most of the time) limited, so why maintain them.

nsrao2k

Nice interesting things were highlighted by all you guys. It was nice reading all these articles and messages to tune the system.

Tuning is more important to databased on the long run, and yes its independent of any hardware.

Its as a person can ride on a bike at 60mh or in small air plane at 300mph - its just hardware. Tuning helps the person to keep fit and going!! places

Warm Rgds,
Srinivas