Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Updates with Unique Indexes
 

Pages: 1
LLBLGen Pro Runtime Framework
Updates with Unique Indexes
Page:1/1 

  Print all messages in this thread  
Poster Message
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# 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?
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37476 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# 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.


  Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# 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:

Code:
        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!)

Code:
        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.


  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14484 posts
# 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).
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.