0

We've hit a performance nightmare with one of our data pulls in our application. Situation being is for a region based query where we have a massive list of customers and they should only see customers in their region when they view them. Current Tables (cut-down for ease of explanation):

Customer (Table):
CustomerID
FirstName
LastName
....

RegionCustomer (Table):
RegionCustomerID
RegionID
CustomerID

This is than retrieved through the .net entity framework using the following statement (edmx sets up the one to many relationship where one customer can have many RegionCustomer):

EntitiesManager.Customers.Where(function(x) x.RegionCustomers.Any(function(y) y.RegionID = CurrentUserRegionID)).ToList()

This runs ok when you are dealing with < 1000 records in either table but after that it is a performance nightmare the sql server spins up the CPU something shocking and takes to long. The SQL that the above statement creates is pretty nasty for what it needs to do (tracked through SQL Profiler).

Just wondering if anyone has any ideas on how this could be sped up?

Thanks in advance

Matt
  • 3,305
  • 11
  • 54
  • 98

1 Answers1

0

It should help to start with RegionCustomers:

EntitiesManager.RegionCustomers.Where(function(regionCustomer) regionCustomer.RegionID = CurrentUserRegionID).SelectMany(function(regionCustomer) regionCustomer.Customers).ToList()

I am not used to the VisualBasic syntax, but if that is incorrect, in C# it would be:

EntitiesManager.RegionCustomers.Where(regionCustomer => regionCustomer.RegionID == CurrentUserRegionID).SelectMany(regionCustomer => regionCustomer.Customers).ToList();

I think that the SQL will be something along the lines of:

SELECT * FROM Customer WHERE CustomerID IN (SELECT CustomerID FROM RegionCustomer WHERE RegionID = {0})

You may also want to consider paging with skip().take() and optimizing the RegionCustomer table with a Foreign Key relationship with Customer on CustomerId if it isn't there. That should be a pretty fast query.

Also be sure to check the Execution Plan to see if there are any suggestions that SQL Server has.

Good luck!

Community
  • 1
  • 1
Dan VanWinkle
  • 991
  • 11
  • 20
  • Hi Dan, Thanks for your response. The SelectMany would not work for me in this relationship. It would only allow me to do a straight select which returned the correct data (EntitiesManager.RegionCustomers.Where(function(regionCustomer) regionCustomer.RegionID = CurrentUserRegionID).Select(function(regionCustomer) regionCustomer.Customers).ToList()) But this was actually slower than doing it the original way? – Matt Jun 04 '13 at 07:25
  • Interesting about SelectMany not working. Makes me wonder if that is a VB thing or what... – Dan VanWinkle Jun 05 '13 at 21:49
  • I will update my answer if this is your issue, but is there a reason why you aren't just mapping this as a many-to-many relationship like it is? It seems as if what you really want to do is something along the lines of EntitiesManager.Customers.Where(function(customer) customer.Regions.Any(function(region) region.RegionID = CurrentUserRegionID).ToList() – Dan VanWinkle Jun 05 '13 at 21:55
  • @Raven: If the above is what you want, check out this: http://msdn.microsoft.com/en-us/library/dd742359.aspx You will want to delete RegionCustomerID. This is assuming that you are using the designer and not using RegionCustomerID anywhere else. If you are doing code first, you can use configurations to do this without deleting RegionCustomerID. If that is what you want, check out this answer: http://stackoverflow.com/a/11382958/412339 – Dan VanWinkle Jun 05 '13 at 22:00
  • @Raven: I am sure your DB design is correct for your situation, but I am curious; Is Customer -> Region really a many-to-many? In most scenarios (again, I don't know yours), Customer to Region would be a One-to-Many, and RegionID can just be brought to the Customer Table. With that, I do know some scenarios where a customer could be in multiple regions. – Dan VanWinkle Jun 05 '13 at 22:04
  • @Raven What ended up fixing it? – Dan VanWinkle Jun 09 '13 at 04:11