MODIFYALL & SQL Server

Christian_Feitl

Hi all,

I have to reset ten fields for a large number of entries in a table.
Does anyone know if it is better to use ten times MODIFYALL or to loop through the entries and modify one by one?

Especially on SQL Server thinking of locking and performance in general.

:-k

Chris

ara3n

I would write a report that does it both ways and run and see which one runs faster. Do it on a large table and get the time before and after the report runs. Post the results here and let us know.

CtrlShiftF11

You should use a standard looping construct with the MODIFY command as MODIFYALL is meant for updating one column...

IF RecordVariable.FIND('-') THEN
  REPEAT
    RecordVariable.Col1 = ValueA;
    RecordVariable.Col2 = ValueB;
    RecordVariable.Col3 = ValueC; 
    RecordVariable.MODIFY;
  UNTIL RecordVariable.NEXT = 0;

kriki

10 time a MODIFYALL goes through the whole table (or selected records) 10 times. If you do it with a REPEAT-UNTIL, you go through the whole table (or selected records) only once.

If you have to do this only once (meaning not on a regular basis), best thing you do is disable all the secondary keys (exept the one you need if you have some filters), then run your procedure, then recreate the secondary keys. This way it is a lot faster, because otherwise the system has to update every index for each record changed. Like this, you just do every index once.

IMPORTANT: best wat to do modifies on records to avoid that you skip records or do records more than once.

recMyTable.RESET;
recMyTable.SETCURRENTKEY(.....);
recMyTable.SETRANGE(.....);
IF recMyTable.FIND('-') THEN
  REPEAT
    recMyTable2 := recMyTable;
    recMyTable2."My Field 1" := "Some Value 1";
    recMyTable2."My Field 2" := "Some Value 2";
    ...
    recMyTable2.MODIFY(FALSE);
  UNTIL recMyTable.NEXT = 0;

Marije_Brummel

If you think SQL can perform this faster, why don't you make some code in SQL directly and leave Navision out? 8)

Problem with the 10 fields is that if you filter on them and do a repeat until, the values thange. If one of these fields in the key, you can get into some nasty never ending loops. :?

Modifyall, does not have that problem I think. :?:

However I would always perform such a task during a weekend, then it can take hours and no one will notice. :-$

Also beware of dialog windows in Navision, they can take a lot of your performance away if out update them often. 8-[

I think I would try to do the update directly in SQL, if it does not bother Navision, and it is a one time update.

DenSter

I really would not recommend modifying data directly on SQL server, that is just all out a bad idea if you don't exactly know what you're doing, and I don't think we should be recommending this solution in a Navision user group.

Like Kriki said, it's faster to loop through once, setting all 10 fields and doing one MODIFY per record. Mark has a valid point that you should be careful with modifying fields that are part of your filter criteria. It's not so much a problem if they are part of the key you are using, but if you are setting a filter on any of the fields that you are modifying, you could run into trouble.

Marije_Brummel

DenSter wrote:

I really would not recommend modifying data directly on SQL server, that is just all out a bad idea if you don't exactly know what you're doing, and I don't think we should be recommending this solution in a Navision user group.

No, I am not a SQL specialist. I would only recomend this to people who know exactly what they are doing. I know it has been done before.

Christian_Feitl

Hi all,

thanks for your replies. I think more infos are required:
- the code hast to work for both c/side and sql so a proprietary sql solution is not possible.
- this has to be done not only once. it is a regular task.
- I have to reset numeric fields, so MODIFYALL is possible .
e.g. MODIFYALL(Field1,0);
- I do not modify filtered or indexed fields.

I asked myself what may be the better way because:
If I use MODIFYALL Navision can translate it into one UPDATE statement with the apropriate filters. And if there is more than one MODIFYALL in sequence perhaps Navision performs not one UPDATE for each MODIFYALL but combines them?
Even if not: If I do a MODIFYALL SQL-Server has to do 10 UPDATE statements, and they are executed on Server without network traffic.
If I loop through the records and do seperate MODIFYs, SQL has to transfer each complete record to the client and then the client sends the information back. Another thing I don't know: Are only the changed fields updated (sent back) or all??

The problem with trying it on my personal sql server is: there is no heavy network load and there is only one user performing this task. I am especially interested if anybody has experience with the two possibilities in real world.

Thanks,

Chris

guidorobben

Have test this in real life. I had to update 4 fields in a table that has 37 miljon records.

4 modifyall took 4 * 6 hours

a repeat/until took also 6 hours.

So when you have to update more than one field a modifyall is not the way to go.

Guido

DenSter

So you did:

MODIFYALL(Field1,Value1);
MODIFYALL(Field2,Value2);
MODIFYALL(Field3,Value3);
MODIFYALL(Field4,Value4);

and that took 24 hours in total?

And then you did:

REPEAT
  field1 := value1;
  field2 := value2;
  field3 := value3;
  field4 := value4;
  MODIFY;
UNTIL Next = 0;

and that took 6 hours?

I find this very hard to believe, unless you are on older versions.

What version did you test this on? Were you on SQL Server? Which version of SQL Server were you on?

guidorobben

DenSter wrote:

So you did:

MODIFYALL(Field1,Value1);
MODIFYALL(Field2,Value2);
MODIFYALL(Field3,Value3);
MODIFYALL(Field4,Value4);

and that took 24 hours in total?

And then you did:

REPEAT
  field1 := value1;
  field2 := value2;
  field3 := value3;
  field4 := value4;
  MODIFY;
UNTIL Next = 0;

and that took 6 hours?

I find this very hard to believe, unless you are on older versions.

What version did you test this on? Were you on SQL Server? Which version of SQL Server were you on?

It was on Navision 3.70 C/Side. I did the test multiple times. And they all gave the same result. I never did a time test on SQL, so I cannot comment on this.

Another problem of the modifyall is that you cannot commit after a amount of records. And there is no way to give a status progress.

DenSter

Thanks, you just earned me a beer . I said you'd be on 3.60, but definately on C/SIDE. There is a big performance difference on SQL Server, especially on the latest 4.0 service pack. MS has put a lot of attention into making Navision perform better on SQL Server.

I'd like to see the same test on 4.0 SP2 and SQL Server 2005.

guidorobben

In the tests we have done, the SQL server is always slower than C/Side. I must add to that, we did not tweak SQL. Just out of the box.

None of our customers are on SQL 2005. So it's nice it is fast, but it doens't help our current customers.

I also know the findfirst and findnext functions are new in 4.0. We don't use them so we can maintain one version of the code.

To make a long story short. Only bad things have happen since SQL is supported.

bbrown

I have been doing a little bit of performance testing lately with version 4.x on SQL. I have been using the SQL Performance Toolkit (available here) to test how restructuring code impacted the SQL commands generated.

I find MODIFYALL to be faster than a loop with either FIND('-') or FINDSET. With MODIFYALL SQL is able to issue a single update command. With a loop, SQL must issue an update for each record.

I have worked with SQL since the early days (remember OS/2) and still find I learn something new with almost every project. I have worked with a number of mid-tier accounting systems along with other products and custom systems on SQL. I have been working with Navision since 2.50

I found that Navision's initial SQL release was much like other products I have worked with. Someone decides that they need to have a SQL version, and SQL is just another database. So they move the code "as is" and ship the product. To say the initial releases of these products were horrible is probably being nice.

Over the releases that followed I found that Navision has improved their SQL offering. I find version 4 to be a sizable step forward. I see this as an indication that Navision has gained a better understanding of SQL over these few years, and is beginning to apply it to the product.

Navision SQL will not be the right fit for all customers, but it is a good resource to have in you arsenal.

guidorobben

Yould someone give me some time examples of how long the modifyall is processing 4 fields on SQL 4.02/SQL2000/2005??

Code:
MODIFYALL(Field1,Value1);
MODIFYALL(Field2,Value2);
MODIFYALL(Field3,Value3);
MODIFYALL(Field4,Value4);


And

Code:
REPEAT
field1 := value1;
field2 := value2;
field3 := value3;
field4 := value4;
MODIFY;
UNTIL Next = 0;


If someone has a few live situations...

Because I think that 4 times a modifyall will be slower than a repeat /until.


Guido

DenSter

Not to mention that MS is developing Navision on SQL Server now, so they feel its limitations themselves.

I would not say 'only bad things happen with SQL Server', there are some real advantages with it, and there are many things you can do to speed it up. There are some new features in the new releases, but you can do a lot of it on older versions as well (one of the more important ones is SIFTLevelsToMaintain and MaintainIndex). If you have Navision add-ons especialy it is good to pay some attention to SQL Server performance.

You should at least read the material in the SQL Server performance tools, you will find a lot of useful information.

PConijn

NAV2009SP1 on a SQL2005 database

Changed four fields in the item table, containing 208592 records.

[*] ModifyAll: 22.797 seconds

[*] Repeat - Until: 2 minutes and 34.196 seconds

David_Singleton

<edit> urrggh someone bumping a nine year old thread.