Optimize single large table

downtap

Thanks in advance for any and all help!

Have a quick question about optimization.

We have a table in Nav (v4sp2) that is quite large: 25,000,000 records, 11GB. There are only 20 fields, all pretty standard with no flowfield or flowfilters. Currently there are only 5 keys on the table. 1 key is for two fields, the other 4 are for four individual fields.

A lot of reports, and functions are based on this table, and due to its record size (I'm assuming), these processes run agonizingly slow. We also are constantly adding data to this table, so the table will only grow in size, and the processes have continually gotten longer since our install roughly 3 years ago.

My question is, what's the best way to approach optimization? I can't delete any records in this table and archive them unfortunately, but I do have the option of archiving and then deleting data from a large section of certain fields but not deleting the entire record. Will this cut down on its size at all? Should I have more keys, and will the standard optimize table feature in Navision really help with this table? Currently it says 64% optimized.

I'm assuming that its just due to the large number of records that things are going so slow, not so much the size. A couple processing only reports we use, have to go through the entire table pretty much. One report takes upwards of 30 minutes to run.

Thanks again!

kapamarou

How many Sumindexes have you defined?

Have you used client monitor in order to see what exactly slows you down?

downtap

Thanks for the help.

I have one sumindex field defined on two of the keys.

I've never run Client Monitor before, and am actually unsure if it's included in our license. The option under Tools is completely greyed out.

kapamarou

downtap wrote:

but I do have the option of archiving and then deleting data from a large section of certain fields but not deleting the entire record. Will this cut down on its size at all? Should I have more keys, and will the standard optimize table feature in Navision really help with this table? Currently it says 64% optimized.

downtap wrote:

I've never run Client Monitor before, and am actually unsure if it's included in our license. The option under Tools is completely greyed out.

I assumed you have a developer's license.

Are you sure that it's not blocked by roles?

This tool will show you the query executed and the time it took to execute along with the actual indexes used.

On a large dataset maybe you are filtering with non'key values so this could be an issue. You need to find a way to run client monitor and run the report in order to spot the issue. Once you have identified it you can choose hoe to solve it. Maybe adding a key would help - but which fields are required? :-k

downtap

Unfortunately we're running on a standard license, not developer's. I am a super user, so I'm assuming client monitor's only included in the development license.

I see what you mean about having the right key. There are some processes that run off that table that run very fast, due to how they're filtered when run. Trouble is, I can't figure out an easier way to filter a couple of the processes so that they don't take as long. I warned our partner of this potential problem on implementation, but they didn't seem too worried that it would get bad down the road as this table grew in number of records.

So, I'm assuming running the optimize table, or simply deleting data from sets of records isn't going to help the problem if it still has to go through 25,000,000 records every time. Are you saying I should maybe try and analyze how each process is run, and try and identify a way for it to filter down the number of records?

Savatage

First of all, what I would do is at the end of the day optimize the table and see if that fixes your problem.

If not then check other alternatives.
But that's the easiest thing to try File->Database->Information->Tables->"Find Your Table"->Click Optimize.

Therefore at the top of my list, way before deleting anything.

Belias

my personal opinion is that kapamarou got the point:
you said you have 5 idx,
2 fields
1 field
1 field
1 field
1 field

you also said that there are jobs run on this table, and that some runs fast: my idea is that there are insufficient indexes for your queries. maybe some procedures sometimes filters on 4-5 values and, depending on the selectivity of the keys, this can slow down a lot the process (as the max no. of filtered fields which matches the key can only be 2).
can you also post the datatype and name of the fields of the keys please? (boolean, integer, code...).
P.S.: Client monitor could really help in these cases...and also some piece of code of the procedures, more precisely the "SETRANGE/SETFILTER" on this table.

downtap

Ok, here's the fields on that table:

Field No. / Data Type / Length
10 Code 20
15 Code 20
20 Text 30
25 Text 30
30 Text 30
35 Text 30
40 Text 30
45 Code 20
50 Text 30
55 Boolean
60 Date
65 Text 150
66 Text 30
67 Text 30
68 Text 30
69 Decimal
70 Text 30
75 Text 100
76 Text 100
80 Code 20

Keys:

Field No. 10 (Code), Field No. 15 (Code) SumIndexField: Field No. 69 (Decimal)
Field No. 55 (Boolean)
Field No. 60 (Date)
Field No. 69 (Decimal)
Field No. 80 (Code)

In particular, we have one major processing only report that we'd like to speed up. Here's how it processes through that table:
Within its DataItemTableView on the properties on the report (tried to simplify the code, hopefully it's easy to follow:

SORTING(Field No. 55) WHERE(Field No. 55=FILTER(Yes))

Also, on the OnPreDataItem, these filters are applied:

SETRANGE("Field No. 10",AnotherCustomTable"Related Field");
SETFILTER("Field No. 60",DateVariable);

I think that covers how that table is filtered and used in that particular report.

Thanks again for the advice!

klavin

downtap wrote:

Ok, here's the fields on that table:

Field No. / Data Type / Length
10 Code 20
15 Code 20
20 Text 30
25 Text 30
30 Text 30
35 Text 30
40 Text 30
45 Code 20
50 Text 30
55 Boolean
60 Date
65 Text 150
66 Text 30
67 Text 30
68 Text 30
69 Decimal
70 Text 30
75 Text 100
76 Text 100
80 Code 20

Keys:

Field No. 10 (Code), Field No. 15 (Code) SumIndexField: Field No. 69 (Decimal)
Field No. 55 (Boolean)
Field No. 60 (Date)
Field No. 69 (Decimal)
Field No. 80 (Code)

In particular, we have one major processing only report that we'd like to speed up. Here's how it processes through that table:
Within its DataItemTableView on the properties on the report (tried to simplify the code, hopefully it's easy to follow:

SORTING(Field No. 55) WHERE(Field No. 55=FILTER(Yes))

Also, on the OnPreDataItem, these filters are applied:

SETRANGE("Field No. 10",AnotherCustomTable"Related Field");
SETFILTER("Field No. 60",DateVariable);

I think that covers how that table is filtered and used in that particular report.

Thanks again for the advice!

You are filtering on Fields 55, 60, and 10.

10 is a part of the Primary key and will be appended to any additional key, but you should have the other two fields as a part of the SAME key.
For this report, I would use a Key as: Field 55, Field 60 I believe.

Kevin

downtap

I'll give that a try at the end of the day, as that will take a long time to process. I wonder if a restructuring of how it is filtered would be the long-term solution.

klavin

Without knowing what you were trying to figure out from this table or report it is hard for me to say. Regardless the fields you are filtering should be a part of that key if there are that many records just so the index is built for it.

You have a couple indexes that may not be needed; depending on if you are using them somewhere else. If you are just using them pretty much for sorting purposes turning on MaintainSQLIndex maybe should be set to No...
Each one of these Keys are holding duplicate indexes of the entire massive table.

Lavin

downtap

Here are the other properties with those keys:

Key / SumIndexFields / MaintainSQLIndex / MaintainSIFTIndex / SIFTLevels
Field No. 10, Field No. 15 Field No. 69 Yes Yes {Field No. 10}
Field No. 15 Field No. 69 Yes Yes {Field No. 15, Field No. 10}
Field No. 60 Yes Yes
Field No. 69 Yes Yes
Field No. 80 Yes Yes

So I should look at how all those keys are used? Mostly it is just sorting I believe, with the exception of the SumIndex field.

I guess what I'm going to try and set the keys to is:
Field No.10, Field No. 15
Field No. 60, Field No. 55
Field No. 69
Field No. 80

Is that correct? Which ones should I try turning off MaintainSQLIndex for?

downtap

I should also mention, that I've considered making the primary key one field as opposed to two.
Basically, those two fields can be combined into one unique field.
Would this speed up the queries with one field as the primary key as opposed to two?

klavin

downtap wrote:

Here are the other properties with those keys:

Key / SumIndexFields / MaintainSQLIndex / MaintainSIFTIndex / SIFTLevels
Field No. 10, Field No. 15 Field No. 69 Yes Yes {Field No. 10}
Field No. 15 Field No. 69 Yes Yes {Field No. 15, Field No. 10}
Field No. 60 Yes Yes
Field No. 69 Yes Yes
Field No. 80 Yes Yes

So I should look at how all those keys are used? Mostly it is just sorting I believe, with the exception of the SumIndex field.

I guess what I'm going to try and set the keys to is:
Field No.10, Field No. 15
Field No. 60, Field No. 55
Field No. 69
Field No. 80

Is that correct? Which ones should I try turning off MaintainSQLIndex for?

There's several components to this:
1. The table is very large
2. The report is running slow
3. Optimizing the table

1. The table's size is due to the number of records and the number of indexes. Shutting off MaintainSQLIndex for Keys "Field No. 69" and "Field No. 80" will significantly cut down the size of the table (by dropping those indexes). Also, during posting SQL will not have to update these indexes as well, so speed can go up there.
The downfall is if other functions / processes are heavily using these. If Field No. 69 and Field No. 80 are important to other processes/functions and it needs it sorted to do it's work then those processes would be slowed down (because SQL has to re-sort it because it doesn't have an index readily available).

2. The report is running slow possibly for many reasons, but it can be helped dramatically by using the correct key as we mentioned above. You would have to tell me if it needs a sumindex as well (if this report in question is using it). But since you are running the Report with an immediate filter on Field No. 55, I would put that first in the key. (may not matter - maybe someone else can help there). Field No. 55, Field No. 60

3. Optimizing the table. Running Optimize on your table could significantly lower the size of the table. It rebuilds all the said indexes and rebuilds the SIFT (removing 0 sums etc). This should be done last if you feel like redesigning the key structure to what we mentioned above. If you do all of these things for tonight, you should see an improvement tomorrow.

As for the Primary Key having 1 field instead of 2, I do not know the answer...

Kevin

downtap

Awesome Klavin, thank you so much for the detailed response. That makes complete sense with rebuilding of the indexes. I think I might tonight back up that table, run it in a test environment and see what kind of results I get.

Belias

The worst choice you can do is to put boolean, option,date and decimal fields as the first field in a key.

the best index for your report (based on the info you gave), is

field10,field55,field 60, maintainsqlindex yes

then create a "sorting only" key, based on the sorting order you really want for this report:
a "sorting only" key is a key with maintainsqlindex = FALSE

NOTE: those posts are only suggestions but you should really contact an expert who could put his hands on your table and on your processes, and see the whole picture

klavin

Belias,

The Primary key he has defined is Field10, Field15 - you can not have Field10 in the secondary key.

NAV Normally throws an error to the effect of:

The key already exists.
The fields from the primary key are automatically appended to all secondary keys.
Table: BlahBlah
Key fields: Field10,Field15

Also, I am just curious on this one. Boolean is stored as tinyint in sql. With it being only 0 and 1, in a secondary key why is that bad? I don't understand why it would matter... Can you help me understand that?

Belias

#-o that error...you're right :oops: ...

boolean fields are bad in the beginning of the key because a boolean field is not selective...
let's say that your table has 25000000 records...let's say that a half of these records have the boolean field true, and the second half has the boolean field false.
SQL prefers high selectivity keys as the first field in a key (let's say a "document no." for example) because it can narrows the resultset on the first shot on the index (let's say that there's an average of 100 records for each "document no.")
working on 100 records on the first shot is far better than on 12500000, isn't it?

that said...if only do a SETRANGE on the boolean field and nothing else...there's nothing you can do...

downtap

I'm definitely going to test using that boolean field first in the keys in my development database to see what kind of results I get.
About 2% of the records will have a value of 'Yes' In that field, which is the first filter applied. So, if I'm understanding this right, after that it would be going through about 500,000 records. I think all I can do is test some different keys and see what kind of results I get. If none of the changes really helps, it may take some reworking of the logic used in the reports I'm assuming.

klavin

Gotcha, makes sense.

Yeah downtap, give it a shot. Regardless having a key with the fields you are using should help with speed, and reducing some of those other keys for Sorting only should definitely help with some size/posting. Farther tweaking might be required. Let us know the testing result.