Deleting Keys

pvar · 2009-11-05

Does any one know how I can delete keys from tables other than designing the table in object designer and deleting it from there?

I know there is a Key virtual table where you can see all the keys of all the tables. But I can't delete keys from there, I am getting permission errors. At the same time I can run Field virtual table and delete fields using the same license so I don't think it is a license issue. If it is not possible to delete keys by running key table is there any other easy way to delete multiple keys from multiple tables?

Thanks in advance for any help

DenSter · 2009-11-06

I don't know of any other way than to use the table designer. What's wrong with doing it from there? Trying to develop a tool or something?

ara3n · 2009-11-06

If the keys belong to a keygroup, you can disable the keygroups.
You can disable them from sql, but suggest against it.

stryk · 2009-11-06

Hi!

The question is: Why do you want to delete "Keys"? This could cause severe trouble within NAV's business logic, as SETCURRENTKEY etc. might raise errors then.
It is reasonable to deaktivate "SQL Indexes" which are not used by the SQL Server. This requires a thorough analysis about "Index Usage", then you could just remove the SQL site indexes by setting the "Key" property "MaintainSQLIndex" to FALSE.
This could be done by C/AL Code directly in table 2000000063 "Key", for example:

IF Keys.GET(TableID, KeyID) THEN BEGIN
  Keys.VALIDATE(MaintainSQLIndex, FALSE);
  Keys.MODIFY;
END;

Regards,
Jörg
P.S.: all this could be easily accomplished with this "NAV/SQL Perfromance Toolbox": http://www.stryk.info/english/toolbox.html

pvar · 2009-11-06

Thanks everyone for your responses. Don't get alarmed, I am not planning to delete keys from the production database and nothing wrong with using object designer to do that either.

I am just thinking about a distant possibility of deleting hundreds of keys from hundreds of tables. It's all part of a two step navision upgrade we are working on and trying to save some time on the first import of the objects where non-primary keys may not be required.

All I wanted to confirm is through the virtual table 'Key' you cannot delete keys and there is no other way to delete keys in bulk in Navision. My best bet would be to assign all those keys to a key group and then disable the key group so that it won't build those keys during the import.

kapamarou · 2009-11-06

You can use the virtual table to Disable / Enable a a whole key. Keep in mind that this will modify the tables properties (Modified,Date).

pvar · 2009-11-06

Thanks kapamarou, I think that is a better option =D> . That way you are not loosing the keys and if you want it later you can enable them back. But somehow the Replace on the Edit menu is grayed out for the Enabled field so I can't do a mass replace, you have to manually uncheck the field for each key.

kapamarou · 2009-11-06

You can create a codeunit / report to do this automatically.

But I suggest to use a new table to store the status of the key before change and also the values of the object table so that you can restore them later.

stryk · 2009-11-08

Once I created a little CU for a similar task:

OBJECT Codeunit 50005 Disable Keys & SIFT
{
  OBJECT-PROPERTIES
  {
    Date=12.11.08;
    Time=19:11:18;
    Modified=Yes;
    Version List=SSI/TEMP;
  }
  PROPERTIES
  {
    OnRun=BEGIN
            IF NOT CONFIRM('CAUTION: This will disable ALL Indexes and SIFTs!\\' +
                           'Backup the database and export all Tables into a FOB before running this!\\' +
                           'Continue?', FALSE)
            THEN
              EXIT;

            IF NOT CONFIRM('Really?', FALSE) THEN
              EXIT;

            Key.SETRANGE(TableNo, 1, 1999999999);
            Key.SETRANGE("No.", 1, 255);
            Key.SETRANGE(Enabled, TRUE);
            Key.SETRANGE(Clustered, FALSE);
            IF Key.FINDSET THEN BEGIN
              c := Key.COUNT;
              Window.OPEN('Table #1######## #2##################\' +
                          'Key   #3########\' +
                          '@4@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@');
              REPEAT
                i := i + 1;
                Window.UPDATE(1, Key.TableNo);
                Window.UPDATE(2, Key.TableName);
                Window.UPDATE(3, Key."No.");
                Window.UPDATE(4, ROUND(10000 / c * i, 1));

                IF Key.MaintainSQLIndex AND (NOT IsClustered(Key.TableNo, Key."No.")) THEN
                  Key.VALIDATE(MaintainSQLIndex, FALSE);
                IF Key.MaintainSIFTIndex THEN
                  Key.VALIDATE(MaintainSIFTIndex, FALSE);
                Key.MODIFY;

                IF (i MOD 100) = 0 THEN
                  COMMIT;

              UNTIL Key.NEXT = 0;
              Window.CLOSE;
            END;
          END;

  }
  CODE
  {
    VAR
      Key@1000000000 : Record 2000000063;
      c@1000000003 : Integer;
      i@1000000001 : Integer;
      Window@1000000002 : Dialog;

    PROCEDURE IsClustered@1000000006(TableNo@1000000000 : Integer;KeyNo@1000000001 : Integer) : Boolean;
    VAR
      Key2@1000000002 : Record 2000000063;
    BEGIN
      Key2.SETRANGE(TableNo, TableNo);
      Key2.SETRANGE(Clustered, TRUE);
      IF Key2.FINDFIRST THEN
        EXIT(KeyNo = Key2."No.")
      ELSE
        EXIT(KeyNo = 1);
    END;

    BEGIN
    END.
  }
}

Here the idea was to reduce the workload when converting/migrating a database:
1. Export all Tables to FOB
2. Run CU/disable all Indexes and SIFT/VSIFT
3. Convert/Migrate Database
4. Restore FOB (large tables separately, importing the FOB in several batches)

Belias · 2009-11-09

stryk wrote:

Once I created a little CU for a similar task:

OBJECT Codeunit 50005 Disable Keys & SIFT
{
  OBJECT-PROPERTIES
  {
    Date=12.11.08;
    Time=19:11:18;
    Modified=Yes;
    Version List=SSI/TEMP;
  }
  PROPERTIES
  {
    OnRun=BEGIN
            IF NOT CONFIRM('CAUTION: This will disable ALL Indexes and SIFTs!\\' +
                           'Backup the database and export all Tables into a FOB before running this!\\' +
                           'Continue?', FALSE)
            THEN
              EXIT;

            IF NOT CONFIRM('Really?', FALSE) THEN
              EXIT;

            Key.SETRANGE(TableNo, 1, 1999999999);
            Key.SETRANGE("No.", 1, 255);
            Key.SETRANGE(Enabled, TRUE);
            Key.SETRANGE(Clustered, FALSE);
            IF Key.FINDSET THEN BEGIN
              c := Key.COUNT;
              Window.OPEN('Table #1######## #2##################\' +
                          'Key   #3########\' +
                          '@4@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@');
              REPEAT
                i := i + 1;
                Window.UPDATE(1, Key.TableNo);
                Window.UPDATE(2, Key.TableName);
                Window.UPDATE(3, Key."No.");
                Window.UPDATE(4, ROUND(10000 / c * i, 1));

                IF Key.MaintainSQLIndex AND (NOT IsClustered(Key.TableNo, Key."No.")) THEN
                  Key.VALIDATE(MaintainSQLIndex, FALSE);
                IF Key.MaintainSIFTIndex THEN
                  Key.VALIDATE(MaintainSIFTIndex, FALSE);
                Key.MODIFY;

                IF (i MOD 100) = 0 THEN
                  COMMIT;

              UNTIL Key.NEXT = 0;
              Window.CLOSE;
            END;
          END;

  }
  CODE
  {
    VAR
      Key@1000000000 : Record 2000000063;
      c@1000000003 : Integer;
      i@1000000001 : Integer;
      Window@1000000002 : Dialog;

    PROCEDURE IsClustered@1000000006(TableNo@1000000000 : Integer;KeyNo@1000000001 : Integer) : Boolean;
    VAR
      Key2@1000000002 : Record 2000000063;
    BEGIN
      Key2.SETRANGE(TableNo, TableNo);
      Key2.SETRANGE(Clustered, TRUE);
      IF Key2.FINDFIRST THEN
        EXIT(KeyNo = Key2."No.")
      ELSE
        EXIT(KeyNo = 1);
    END;

    BEGIN
    END.
  }
}

Here the idea was to reduce the workload when converting/migrating a database:
1. Export all Tables to FOB
2. Run CU/disable all Indexes and SIFT/VSIFT
3. Convert/Migrate Database
4. Restore FOB (large tables separately, importing the FOB in several batches)

I did something similar to avoid a bug about delzeroSIFTs (4SP1 version)...

the real thing i want to add is: don't disable the key, disable SQLIndex maintenance instead (as stryk suggested)...you know, setcurrentkeys problems

DenSter · 2009-11-09

It's nice if you need to migrate, or mass updates, when they are going to be set back (either by saving their original state or by replacing them back with a fob file), or to fix a bug or something but setting key properties with the Key virtual table makes object management more difficult. It's not a good substitute for a developer going in and documenting their change. I would not use this for development purposes.

The way that I used it was with the clustered index bug (I think that was in 4.0 SP1), I wrote a processing only report that set the first key of every table to clustered.

pvar · 2009-11-11

If I understand correctly, setting MaintainSQLIndex=False would be a better approach than disabling the key. One way or other we could save considerable amount of time during the upgrade process if we don't have all the keys in the tables as we have 30 companies to upgrade and our database size is close to 300 GB. But at the same time we need a few keys for the upgrade process to work because the upgrdade codeunit has SETCURRENTKEY in the code that will require keys on certain tables.

We are doing a 2 step upgrade from 3.6 to 4.0 and then from 4.0 to 2009 classic. For the first step we are importing only the tables and we will import the complete objects only during the second step. The question is at what point I need to disable the keys so that I will save the most time in the upgrade? Unfortunately there is no Key table in 3.6 so I can't do a mass update. Is there a way to disable keys in 3.6 in bulk before I start the upgrade then I could save some time in the restore also?

DenSter · 2009-11-11

The important part is understanding the difference between 'keys' in the NAV table object, and 'indexes' on SQL Server. They are two different things. The 'key' is necessary for sorting purposes in NAV objects. Without the key, a whole bunch of things don't work properly. For instance if you disable a key that is used in a SETCURRENTKEY command, you get a runtime error. Unchecking the 'MaintainSQLIndex' property of the key means that the 'index' on SQL Server will be dropped.

As long as the 'key' is enabled, regardless of whether the 'index' on SQL Server is maintained, all NAV functionality will work. There will not be any runtime errors, but query performance can be affected. SQL Server doesn't need an 'index' for sorting (most of the time), but without 'indexes', data retrieval might become really slow when queries based on non-existent indexes are executed. So, when you disable a SQL 'index' for a NAV 'key', that could mean that queries that use that particular sort order might run more slowly. The 'art' of index tuning is finding the right balance between what index to keep and what index not to keep.

For you the trick becomes to somehow list the 'indexes' that you want to maintain for the upgrade routines only, and create a routine that disables/enables them at various stages of the upgrade process. As for the key virtual table not being available in 3.7, I think you could do a technical upgrade to even 2009 and run the upgrade routines in 2009 exe's, even though the object and data is in 4.0 state.

pvar · 2009-11-11

Without the key, a whole bunch of things don't work properly. For instance if you disable a key that is used in a SETCURRENTKEY command, you get a runtime error.

I know I will get runtime errors if a certain key is not there but used in SETCURRENTKEY in the code and that is why I said I need to enable some keys for the upgrade routines to work

As for the key virtual table not being available in 3.7, I think you could do a technical upgrade to even 2009 and run the upgrade routines in 2009 exe's, even though the object and data is in 4.0 state.

We are doing the full upgrade in 2009 executable but the restore of a 300 gb sql 2000 backup in sql 2008 took almost 24 hrs in our initial testing and then another 6 hrs to do the technical upgrade to NAV 2009 and I am trying to cut short that time if I could eliminate the unnecessary keys even before the technical upgrade.

DenSter · 2009-11-11

pvar wrote:

that is why I said I need to enable some keys for the upgrade routines to work

You were talking about that in the context of turning off MaintainSQLIndex though. It looked to me like you thought that turning off MaintainSQLIndex causes problems with SETCURRENTKEY (which it doesn't, to be clear). A lot of people don't understand the difference between keys and indexes, so that's why I explained that part.

The part about doing a tech upgrade, I meant to do a tech upgrade before running the data/object upgrade, so that you can use the Key virtual table to turn off MaintainSQLIndex.

pvar · 2009-11-11

The part about doing a tech upgrade, I meant to do a tech upgrade before running the data/object upgrade, so that you can use the Key virtual table to turn off MaintainSQLIndex.

I believe by technical upgrade, we both mean opening the 3.6 database in NAV 2009. But before that don't we have to convert sql 2000 database to sql 2008? The way we are planning to do the upgrade is get a sql 2000 backup from the current production env, restore that into sql 2008 in a different env, do the technical upgrade and start the upgrade process. Are you suggesting a different method?

DenSter · 2009-11-11

pvar wrote:

I believe by technical upgrade, we both mean opening the 3.6 database in NAV 2009. But before that don't we have to convert sql 2000 database to sql 2008? The way we are planning to do the upgrade is get a sql 2000 backup from the current production env, restore that into sql 2008 in a different env, do the technical upgrade and start the upgrade process. Are you suggesting a different method?

What I was thinking about was converting the 3.7 database to a version high enough to have the Key virtual table, that obviously needs to be compatible on SQL Server 2000, I think 4.0 SP3 will run on SQL 2000. That way, after unchecking MaintainSQLIndex in the Key virtual table (I would create a table to keep track of which ones were modified that way), still on SQL Server 2000, your SQL backup would be a lot less than 300GB, it would take a lot less time to create the SQL Server 2008 database, you could then convert the database to 2009 exe's, and choose to only turn MaintainSQLIndex on for the keys that are used for the upgrade process.

I'm just brainstorming here, this is not from experience, so I have no way of knowing how much time (if any) you would save this way. You're going to have to turn MaintainSQLIndex back on for all of the tables at some point though, so I don't know if you'll save a lot of overall time this way.

pvar · 2009-11-11

Got it. I will try that. Thanks.

David_Singleton · 2009-11-11

It wont take you more than an hour to create a 370 objects only database, in this deactivate all secondary keys. Export objects. Now you can use this object file to easily disable the keys when you need to. Seriously this is the fastest way.

I think this is simpler than a technical upgrade. And your database will probably drop to something like 130 gig making everything much faster.

pvar · 2009-11-11

It sounds faster to me also. Let me see if I understood the steps

1. Export tables from 3.6 database
2. Import that into a 4.0 objects only database
3. Disable the keys
4. Export the tables
5. Import that into the 3.6 database again
6. Take the sql backup

The whole objective is to save time on every step from the beginning. I guess it takes some time in step 5

David_Singleton · 2009-11-11

pvar wrote:

It sounds faster to me also. Let me see if I understood the steps

1. Export tables from 3.6 database
2. Import that into a 4.0 objects only database
3. Disable the keys
4. Export the tables
5. Import that into the 3.6 database again
6. Take the sql backup

The whole objective is to save time on every step from the beginning. I guess it takes some time in step 5

No,

just copy your 3.70 database and then manually go in and untick the boxes in Object designer. In reality how many tables do you need to do? Its probably only 20 or so. In my opinion you already spent more time discussing option than it would take just to do it, and you still have to write code the other way. In any case importing objects form 400 to 370 ay not work. Or it may give errors. So is not safe.

David_Singleton · 2009-11-11

pvar wrote:

...we have 30 companies to upgrade and our database size is close to 300 GB. ...

By the way, assuming the companies are similar in size, they would be 10 gig each, i.e. tiny databases. My experience is that deactivating keys is beneficial only when you have more than 1,000,000 records in a table. So this may not actually make the upgrade faster. But since the overall size of the database will be less than half, it will help to increase the overall speed of working.

If you had one table say GL Entry with 50,000,000 records and 10 keys and 5 with sift fields then it makes a huge advantage.

pvar · 2009-11-11

In reality how many tables do you need to do? Its probably only 20 or so

If it was that many I wouldn't have spent time discussing this. Ours is a heavily customized database with custom keys added to hundreds of tables so manual unchecking is not an (or will be the last) option to us. I do not have to develop anything to disable the keys programatically, I already have that, I just need a version which has key virtual table.

David_Singleton · 2009-11-11

pvar wrote:

In reality how many tables do you need to do? Its probably only 20 or so

If it was that many I wouldn't have spent time discussing this. Ours is a heavily customized database with custom keys added to hundreds of tables so manual unchecking is not an (or will be the last) option to us. I do not have to develop anything to disable the keys programatically, I already have that, I just need a version which has key virtual table.

A hundred tables with over 1,000,000 Records and a lot of keys? Are you sure?

I really think you are over complicating this, but probably its best to continue the way you feel comfortable with.

Really though how many tables have more than a million records?

PS go to File Database Tables and filter on No Of records >1,000,000 then check how many unique tables there are.

Deleting Keys

Comments

Categories