Rename Bill-to Customer No

matias_jaure

Hello,
We have a problem when we try to rename a Customer, when we change the No., Navision begins changing all the relations, in staging environment it took like 40 minutes. But when we try it on production, we have to stop it 8 hours later, because users have to use the system, and still not finished. Any idea what could happen?

Thanks for ur time,
Matias

Joeri

Hi Matias,

I've had the same problem once for a customer where i had to rename the Job."No.".
Because of all the tablerelations it took a couple of minutes per Job to rename, and it would cost several days or even weeks to run this.

What we did to solve it.

We identified all the tablerelations to this table, then create a codeunit that runs through all these tables and sets the "Job No." to a new number (using a temporary table with old / new job no.). After that was completed, we exported all the objects with the tablerelation to a .txt file and .fob file and removed all the table relations from the .txt file, and imported it back into NAV. Compiled the objects, and then ran the rename on the Job table.
After this was completed we imported the original Fob to restore all table relations.

This reduced the running times to about 1 day, and could be run in a weekend.

matias_jaure

Joeri wrote:

Hi Matias,

I've had the same problem once for a customer where i had to rename the Job."No.".
Because of all the tablerelations it took a couple of minutes per Job to rename, and it would cost several days or even weeks to run this.

What we did to solve it.

We identified all the tablerelations to this table, then create a codeunit that runs through all these tables and sets the "Job No." to a new number (using a temporary table with old / new job no.). After that was completed, we exported all the objects with the tablerelation to a .txt file and .fob file and removed all the table relations from the .txt file, and imported it back into NAV. Compiled the objects, and then ran the rename on the Job table.
After this was completed we imported the original Fob to restore all table relations.

This reduced the running times to about 1 day, and could be run in a weekend.

Wow, that's a lot of effort. The wired thing here is that in our staging environment took 40 minutes, how could you explain that?

Joeri

And your staging area is a complete copy of your live system? Same database (sql/cside), same hardware, and most importantly, same data in the database?

matias_jaure

Joeri wrote:

And your staging area is a complete copy of your live system? Same database (sql/cside), same hardware, and most importantly, same data in the database?

all the same, just a couple of weeks difference

David_Singleton

If this is a regular requirement, then you might take a look at how long it takes in SQL. Its generally many order of magnitudes faster than Native. We had a client on Native, that took normally about 8-10 hours to renumber an item, now on SQL it takes 2-5 minutes.

hawwa

It shouldn't take so long to rename a customer. Do you have performance problem in you production database ?

David_Singleton

hawwa wrote:

It shouldn't take so long to rename a customer. Do you have performance problem in you production database ?

Definitely it can take this long to rename a customer. Maybe you are only testing in a small database.

matias_jaure

David Singleton wrote:

If this is a regular requirement, then you might take a look at how long it takes in SQL. Its generally many order of magnitudes faster than Native. We had a client on Native, that took normally about 8-10 hours to renumber an item, now on SQL it takes 2-5 minutes.

This is not a regular requirement, but we have to rename 8 customers. How do you did it in SQL? Just looking at the relationships ?

David_Singleton

matias_jaure wrote:

David Singleton wrote:

If this is a regular requirement, then you might take a look at how long it takes in SQL. Its generally many order of magnitudes faster than Native. We had a client on Native, that took normally about 8-10 hours to renumber an item, now on SQL it takes 2-5 minutes.

This is not a regular requirement, but we have to rename 8 customers. How do you did it in SQL? Just looking at the relationships ?

Are you on SQL or Native? SQL rename is a lot faster. But if you are already on SQL, then its probably a network issue. In which case if its only 8 items, then run it on the server.

matias_jaure

David Singleton wrote:

matias_jaure wrote:

David Singleton wrote:

If this is a regular requirement, then you might take a look at how long it takes in SQL. Its generally many order of magnitudes faster than Native. We had a client on Native, that took normally about 8-10 hours to renumber an item, now on SQL it takes 2-5 minutes.

This is not a regular requirement, but we have to rename 8 customers. How do you did it in SQL? Just looking at the relationships ?

Are you on SQL or Native? SQL rename is a lot faster. But if you are already on SQL, then its probably a network issue. In which case if its only 8 items, then run it on the server.

I am in SQL, but probably you are right and it's a network issue, I will try in the production server. DBA will complain, haha

David_Singleton

matias_jaure wrote:

...

I am in SQL, but probably you are right and it's a network issue, I will try in the production server. DBA will complain, haha

You are not by any chance using WiFi are you?

DenSter

I would bet that it is because there are people still using the database, or there's a backup going on, or some other process. I've had a table reindexing run for a whole hour, and this table only had one record. The cause was a NAS instance running a timer object to check the table every 100 milliseconds, and for some reason it was blocking the reindexing. Turned off the NAS and it went through without even showing up in the SQL Profiler (which I had set to catch everything that takes more than 10 miliseconds).

I would schedule this for the weekend, make sure that no users are in the database, disable all NAS instances, take the database to single user, create the backup, make sure that no other process is running on the database, and THEN rename your customers.