Return to Answer

It looks like you're kind of using ApplicationUser for this purpose, but I think your question alone shows one of the downsides to that.

Select UserId = U.UserId, ContractId = C.ContractId, CanRead = Max(Case When ContractOwner.UserId Is Not Null Then 1 When <<down the list...>> Else 0 End), CanWrite = Max(Case When ContractOwner.UserId Is Not Null Then 1 When <<down the list...>> Else 0 End) From dbo.User As U Cross Join dbo.Contract As C Left Outer Join dbo.ApplicationUserRole As ContractOwner On ContractOwner.UserId = U.UserId And ContractOwner.BUid = C.SignatoryBUId And ((C.IsPrivate Is Null) Or (C.IsPrivate = Cast(0 As Bit))) -- NO ISNULL!! And ContractOwner.RoleId In (16, 19, 20) Left Outer Join (<<down the list...>>) Group By U.UserId, C.ContractId 

Select UserId = U.UserId, ContractId = C.ContractId, CanRead = Max(Case When ContractOwner.UserId Is Not Null Then 1 When <<down the list...>> Else 0 End), CanWrite = Max(Case When ContractOwner.UserId Is Not Null Then 1 When <<down the list...>> Else 0 End) From dbo.User As U Cross Join dbo.Contract As C Left Outer Join dbo.LotSupplier As LotSupplier On LotSupplier.ContractId = C.Id Left Outer Join dbo.ContractBuyer As ContractBuyer On ContractBuyer.ContractId = C.Id Left Outer Join dbo.Lot As LinkedLot On LotSupplier.LotId = LinkedLot.Id Left Outer Join dbo.ApplicationUserRole As ContractOwner On ContractOwner.UserId = U.UserId And ContractOwner.BUid = C.SignatoryBUId And ((C.IsPrivate Is Null) Or (C.IsPrivate = Cast(0 As Bit))) -- NO ISNULL!! And ContractOwner.RoleId In (16, 19, 20) Left Outer Join dbo.ContractBuyer As ContractBuyer   On ContractBuyer.ContractId = C.Id Left Outer Join dbo.ApplicationUserRole As Buyer On ((C.IsPrivate = Cast(1 As Bit) Or (LinkedLot.IsPrivate = Cast(1 As Bit)) And ContractBuyer.BuyerId = Buyer.UserId And ...on and on... Left Outer Join (<<down the list...>>) Group By U.UserId, C.ContractId 

Since you're also obviously working in what I call a many-hats model (a person can be a user, buyer, owner, et cetera, I highly recommend you consider normalizing out a Person (or Party, or whatever tickles your fancy) table.

• Information would have to be updated in one place only
• You would not have to do that awful join on e-mail
• That join would not fail on updating info in only one place :-)
• You would be much better set up for sub-contracting, multi-owner contracting, contract transfers and all that fun

• Fix your WHERE clauses. Your current query forces table scans just about everywhere because you use functions.

• Change UNION to UNION ALL. UNION does an implicit DISTINCT, but you're doing one in the main query already. Depending on your data, this may help a lot or hurt a lot.

• Make sure your temporary tables have indexes/primary keys.

• If the query actually takes a while, load it into a copy of the table, then MERGE it into the live copy afterwards.

Look through your query plan (or rather, save it and open it up in SQL Server Management Studio), and hover over some of those thick arrows. You're exploding Contract times ApplicationUserRole five times, Contract times ApplicationUser twice, then slapping all those together (on several levels) to get distinct values.

But that's not what you want. You want users versus documents, and look up their rights.

• Fix your WHERE clauses. Your current query forces table scans just about everywhere because you use functions. IsNull(COALESCE(linkedlot.IsPrivate, c.IsPrivate), 0) = 0 should be ((ll.IsPrivate = 0) Or (ll.IsPrivate Is Null And c.IsPrivate = 0) Or (ll.IsPrivate Is Null And c.IsPrivate Is Null)). Side note: are these fields actually nullable? Why? These queries would be MUCH simpler if they were not.

• Change UNION to UNION ALL. UNION does an implicit DISTINCT, but you're doing one in the main query already. Depending on your data, this may help a lot or hurt a lot.

• Make sure your temporary tables have indexes/primary keys.

• If the query actually takes a while, load it into a copy of the table, then MERGE it into the live copy afterwards.

Look through your query plan (or rather, save it and open it up in SQL Server Management Studio), and hover over some of those thick arrows. You're exploding Contract times ApplicationUserRole five times (8 million rows every time), Contract times ApplicationUser twice (6 million rows every time), then slapping all those together (on several levels) to get distinct values (sorting 8-16 million rows every time).

But "explode all possible combinations for every possible way rights can be affected, whether they exist or not, then combine, then sort, then sort again" is not what you want. You want users versus documents, and look up their rights.

Unless you're running this on a brutally underspecced server, the entire query can be made to run in a few seconds, trust me.