Poor performance when renaming record on sql database

macl
macl Member Posts: 9
edited 2012-10-24 in SQL Performance
Hi,

I have done an SQL migration for a customer from the native database NAV 3.70 to NAV 2009 R2 SQL. After the migration we have a sorting problem in the cost center table (table 11). Some cost centers have 5 digits while the rest has 7 digits. So according to our customer I could solve this problem by just adding "00" as the second and third digit. Then all cost centers are 7 digits long and the sorting would be OK.

So I wrote a simple report that would do the rename of the 5 digits cost centers. By the way there are several companies in the database. In three companies the rename worked fine since there were only about 36 records out of 300 to be renamed. However when I came to the main company (the largest in the database) where there are 2280 cost centers/records in table 11 and 398 have to be renamed the report keeps running and running. For the other three companies I realized that when I started the report it would always take a while before the renaming would actually start - being indicated by the popup-windows.

So for the largest company I then decided not to use my report but just to rename one record from the form. I also started the SQL profiler to see what's happening. While I am typing this support request SQL profiler has been running for one hour - just for one record to be renamed - and is still not finished. What I can see from the trace is that a lot of "exec sp_cursorfetch" statements are recorded and in each Reads-column the value is always around 507/508.

Can anyone help me with this problem? What is the cause that renaming takes so long and is there a way to speed it up?

Thanks for your suggestions.

Best regards,
MACL

Answers

  • stryk
    stryk Member Posts: 645
    Well, when RENAMEing a record actually plenty (= one for each related table) of UPDATE statements are executed, all looking similar to this:
    UPDATE MyTable SET "Cost Center" = 'NewValue' WHERE "Cost Center" = 'OldValue'

    On large tables this is causing huge ( :shock: !!!) workload, putting high pressure on the Transaction Log, CPU, Memory and Disks ...

    All related tables will be updated in one single "monster"-transaction ...

    So you really need to have a sufficient platform ... and to be patient ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • pdj
    pdj Member Posts: 643
    If you only need the sorting to be fixed in the Cost Center table, you could experiment with the property "SQL Data Type". Just have this little note from the on-line help in mind:

    "You cannot change the SQL Data Type of a code field from Variant to Varchar or Integer, unless there are no rows in the table or unless the field contains only an empty string in every row."
    Regards
    Peter
  • macl
    macl Member Posts: 9
    Hi Jörg and Peter

    Thank you both for your suggestions. The solution was really to be patient. The whole job took about 6 hours to complete. But now all the records are renamed.

    @Peter: I know the property "SQL Data Type". That would have solved the sorting problem. But since I am dealing with a vertical solution developed by another NAV partner I didn't want to change the data type to Integer.

    Thanks again and best regards,
    Mario

Categories