Updates with Unique Indexes

Posts   
 
    
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 31-Aug-2017 11:10:43   

LLBLGen 4.2/SQL Server 12

I have a database table with ~80,000 rows. It has 7 Unique Indexes (not Unique Constraints - nulls must be ignored) created like this:-


-- Special index to ensure that ISINs are unique but can also be null

CREATE UNIQUE NONCLUSTERED INDEX [UC_ISIN] ON [dbo].[Fund] ( [ISIN] ) WHERE [ISIN] IS NOT NULL

I am making many updates in one transaction saving an EntityCollection<Fund> but the problem is that the ordering of the saves cannot be predicted. e.g. an ISIN (above) may be moved from one entity to another with the source entity having its ISIN set to null but if the target entity happens to get saved first then the Index will reject the change. (Having many of these Unique Indexes means I can't even save them by ISIN=null first)

The only thing I can think of is to disable the indexes within the transaction somehow and rebuild them afterwards.

Is there anything else I could consider?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 31-Aug-2017 11:39:26   

If ISIN I is moved from Fund F1 to F2, then F1 always ends up with no ISIN ? (so it's null) ?

Would a dummy ISIN help? You assign that to the one which has no isin, but I'm not sure I entirely understand the specifics of what goes wrong

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 01-Sep-2017 09:08:25   

database: F1: ISIN = null, MEX = ABC F2: ISIN = XYZ, MEX = null

In memory (swapped): F1: ISIN = XYZ, MEX = null F2: ISIN = null, MEX = ABC

If F1 is saved first then crash because database F2.ISIN is already XYZ If F2 is saved first then crash because database F1.MEX is already ABC

In the simpler case with no MEX changes, F2 (ie with a null value) must be saved first to prevent crash but currently the save order is effective random.

Even if saved in two passes with all entities where ISIN=null first and everything else second, that would only work if no other swaps in the other 7 fields where a UniqueIndex is used occur which is unlikely.

simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 01-Sep-2017 09:48:26   

It turns out that Disabling/Rebuilding the Indexes does work and works within a transaction too, so I added these methods to DataAccessAdapter:

        public void DisableIndex(string indexName, string tableName)
        {
            var commandString = CreateIndexCommandString(indexName, tableName, "DISABLE");

            ExecuteSqlScript(commandString);
        }

        public void RebuildIndex(string indexName, string tableName)
        {
            var commandString = CreateIndexCommandString(indexName, tableName, "REBUILD");

            ExecuteSqlScript(commandString);
        }

        string CreateIndexCommandString(string indexName, string tableName, string action)
        {
            return $"USE [{(CatalogNameToUse.Length == 0 ? "TADS" : CatalogNameToUse)}]\r\n" +
                   $"ALTER INDEX [{indexName}] ON [{tableName}] {action};\r\n";
        }

and do my save like this.. (you have to specify individual Index names rather than use ALL otherwise the clustered index gets disabled and nothing works!)

        static readonly string[] UniqueIndexNames = { "UC_CitiCode", "UC_FLIBMatch", "UC_FLMatch", "UC_ISIN", "UC_MEX", "UC_SEDOL", "UC_TNMatch" };

            using (var adapter = new DataAccessAdapter())
            {
                adapter.StartTransaction(IsolationLevel.Serializable, "X");
                
                UniqueIndexNames.Visit(uin => adapter.DisableIndex(uin, "Fund"));

                updateCount += adapter.SaveEntityCollection(allFundsAndShares, false, false);
                updateCount += adapter.SaveEntityCollection(Providers, false, true);

                UniqueIndexNames.Visit(uin => adapter.RebuildIndex(uin, "Fund"));

                Debugger.Break();
                adapter.Commit();
            }

Not something for every use I guess but this works for me because I'm the only user of this particular database and doing bulk updates periodically.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 01-Sep-2017 10:29:49   

I think you have a good resolution, so I'll mark this thread as resolved.

But just to share my 2 cents.

I don't think you are swapping the values of all the fields of 2 entities, I'm assuming there are some of fields that don't get swapped, otherwise it doesn't make sense to me.

In this case, I think the swappable fields might better fit in another table (extension table), and so each entity in the main table only has a FK pointing to one row in the extension table, or the other way around and the FK can be made unique, so it turns out to 1:1 relation.

Then it will come down to just swapping one field (the FK in this case).