I have a large Product table:
ProductID | ProductName |
---|---|
1 | Product 1 |
2 | Product 2 |
... | ... |
n | Product n |
whereas n is around 2,000,000.
To limit product availability to a certain country, I have a second ProductCountryRestriction table:
ProductID | CountryCode | IncludeExclude |
---|---|---|
1 | USA | 0 |
1 | UK | 0 |
2 | AUS | 1 |
2 | NZ | 1 |
If a product is restricted to one or more countries, I will then add product- included countries to this table with IncludeExclude = 0. For example Product 1 is available for USA and UK only.
If a product is valid for all countries except some countries, I will then add product - excluded countries to this table with IncludeExclude = 1. For example Product 2 is available for all countries except AUS and NZ.
All country codes corresponding to a product must either be set as IncludeExclude = 0 or as IncludeExclude = 1. The mixing of 0 and 1 is not allowed. For example, there are records with IncludeExclude = 0 for Product 1, I can't add a record with ProductID = 1 and IncludeExclude = 1 to this table.
An user of the system can select several countries to work with. The user preference is stored in UserCountry table:
UserID | CountryCode |
---|---|
1 | USA |
1 | NZ |
So the User 1 can see Product 1 because it is available for USA. He can also see Product 2 because the Product 2 is also available for USA (although it is excluded from NZ). Here is my query to get all available products for an user:
DECLARE @UserID int = 1;
SELECT P.*
FROM Product P
WHERE
EXISTS
(
SELECT * FROM ProductCountryRestriction PCR
WHERE
PCR.ProductId = P.ProductId
AND PCR.IncludeExclude = 0
AND PCR.CountryCode IN (SELECT CountryCode FROM UserCountry WHERE UserID = @UserID)
)
OR EXISTS
(
SELECT * FROM UserCountry UC
WHERE UserID = @UserID
AND UC.CountryCode NOT IN (SELECT CountryCode FROM ProductCountryRestriction PCR WHERE PCR.ProductId = P.ProductId AND PCR.IncludeExclude = 1)
)
This query works as expected but it introduces poor performance. What can I do to improve it? I don't mind to change the DB design. Thank you for reading my question! I will be grateful for any help you can provide.
Edited 12/31/2020 - Added execution plan as suggested by @J.D. Please check this link for the plan: https://www.brentozar.com/pastetheplan/?id=BywbFaqaw