linq.Any() check on large database

Question

Is there a way to speed up the Linq.Any check or perform the check in another way to enhance performance.

I have a mastercodes table that records unique codes for a campaign. The code below will create new codes for a campaign but checks the mastercode table to make sure that it doesn;t already exist. Once the codes have been created and added to the mastercodes table it creates an excel file for download.

The main issue is that a campaign can have 400k codes and we have several campaigns running at once. So the mastercode table can get into the millions of records. When I request 300 new codes and run the below process without the .Any() check it takes 1-2 seconds in production. However when i add the check the process times out with the "no response to user" timeout error.

I'm using the Z.EntityFrameworrk.Extensions and the post to the table happens pretty much instantly. But is there a way to code the perform the mastercode check that doesn't have a huge performance issue.

The Model

public class MasterCode
{
 public int MasterCodeId { get; set; }
 public int CampaignId { get; set; }
 public string CampaignCode { get; set; }
 public string Code { get; set; }
 public bool Used { get; set; }
 public DateTime SubmittedOn { get; set; }
}

The code

public static void GenerateMasterCodes(GenerateMasterCode model)
{
 using (ApplicationDbContext _context = new ApplicationDbContext())
 {
 Campaign campaign = _context.Campaigns.Find(model.CampaignId);
 List<MasterCode> masterCodes = new List<MasterCode>();
 for (var i = 1; i <= model.Count; i++)
 {
 var masterCode = new MasterCode
 {
 CampaignId = campaign.CampaignId,
 CampaignCode = campaign.CampaignCode,
 Used = false,
 SubmittedOn = DateTime.Now
 };
 // Ensure that no code already exists for that campaign.
 var code = GenerateMasterCode(model.StartString, model.CodeLength);
 while (_context.MasterCodes.Any(m => m.Code == code && m.CampaignCode == campaign.CampaignCode))
 {
 code = GenerateMasterCode(model.StartString, model.CodeLength);
 }
 masterCode.Code = code;
 masterCodes.Add(masterCode);
 }
 try
 {
 _context.MasterCodes.AddRange(masterCodes);
 _context.BulkSaveChanges();
 }
 catch (Exception ex)
 {
 ...
 }
 }
}

The Code generator

public static string GenerateMasterCode(string startString, int mastercodeLength)
{
 string numericChars = "X123456789";
 char[] chars = new char[mastercodeLength];
 Random rd = new Random();
 for (int i = 0; i < mastercodeLength; i++)
 {
 if (!string.IsNullOrEmpty(startString) && i < startString.Length)
 {
 chars[i] = Char.Parse(startString.Substring(i, 1));
 }
 else
 {
 chars[i] = numericChars[rd.Next(0, numericChars.Length)];
 }
 }
 return new string(chars);
}

• Andrew: THe 'model.StartString' and 'model.CodeLength' are just inputs from the user to generate the random code with a certain length and starting string. So the output code will look something like M12X5432. Then if the code exists on the mastercode table, the while loop will call the code generation code again to create a new random code. Then the while loop will check the table again to make sure that the new code doesn't exist.
• Heslacher: It seems I misread your code.
• Andrew: All good. Thanks for asking the question. Made me go back and check the code. Always a good thing :)
• Heslacher: Can the user input a model.StartString which Length equals to model.CodeLength ?
• t3chb0t: It would help if you could add the sql generated by entity framework. You can also paste it into the sql management studio and check the execution plan or even post its screenshot.

Answer 1

GenerateMasterCode

I would break the GenerateMasterCode() method into two methods and would declare the Random as a static field tof the class.

From the Random documentation:

The default seed value is derived from the system clock and has finite resolution. As a result, different Random objects that are created in close succession by a call to the default constructor will have identical default seed values and, therefore, will produce identical sets of random numbers. This problem can be avoided by using a single Random object to generate all random numbers.

Instead of using a char[] to generate the random characters I would use a StringBuilder.

public static string GenerateMasterCode(string startString, int mastercodeLength)
{
 if (startString.Length == masterCodeLength) { return startString; }
 if (startString.Length > masterCodeLength) { return startString.Substring(0, masterCodeLength); }
 return startString + GenerateRandomCharacters(masterCodeLength - startString.Length);
}
private static Random rd = new Random();
private static string GenerateRandomCharacters(int length)
{
 string numericChars = "X123456789";
 int length = numericChars.Length;
 StringBuilder builder = new StringBuilder(length);
 for (int i=0; i < length; i++)
 {
 builder.Append(numericChars[rd.Next(length)]);
 }
 return builder.ToString();
}

---

GenerateMasterCodes

Right now you query in the while loop all of the MasterCodes although you are only interested in the Campain related MasterCodes. So you should query the MasterCodes which belongs to the Campain before you start with the for loop.

Assigning false to Used can be omitted, because that is the default value of bool.

You can use the var type for the List<MasterCode> as well.

If you don't use that method outside of the class you should consider to make it private.

public static void GenerateMasterCodes(GenerateMasterCode model)
{
 using (ApplicationDbContext _context = new ApplicationDbContext())
 {
 Campaign campaign = _context.Campaigns.Find(model.CampaignId);
 var campainRelatedMasterCodes = _context.MasterCodes.Where(m.CampaignCode == campaign.CampaignCode);
 var masterCodes = new List<MasterCode>();
 for (var i = 1; i <= model.Count; i++)
 {
 var masterCode = new MasterCode
 {
 CampaignId = campaign.CampaignId,
 CampaignCode = campaign.CampaignCode,
 SubmittedOn = DateTime.Now
 };
 // Ensure that no code already exists for that campaign.
 var code = GenerateMasterCode(model.StartString, model.CodeLength);
 while (campainRelatedMasterCodes.Any(m => m.Code == code))
 {
 code = GenerateMasterCode(model.StartString, model.CodeLength);
 }
 masterCode.Code = code;
 masterCodes.Add(masterCode);
 }
 try
 {
 _context.MasterCodes.AddRange(masterCodes);
 _context.BulkSaveChanges();
 }
 catch (Exception ex)
 {
 ...
 }
 }
}

• Andrew: Nice....thanks for the reply. This is very helpful. I will implement and test and get back to you. thanks
• Andrew: This has significantly speed up the process. Thanks heaps.

Answer 2

public static string GenerateMasterCode(string startString, int mastercodeLength)
{
 string numericChars = "X123456789";
 char[] chars = new char[mastercodeLength];
 Random rd = new Random();
 for (int i = 0; i < mastercodeLength; i++)
 {
 if (!string.IsNullOrEmpty(startString) && i < startString.Length)
 {
 chars[i] = Char.Parse(startString.Substring(i, 1));
 }
 else
 {
 chars[i] = numericChars[rd.Next(0, numericChars.Length)];
 }
 }
 return new string(chars);
}

This is one of the methods that slow you down. Do not create the Random each time. Create it once and reuse it or otherwise you may get non-random numbers.

rd.Next(0, numericChars.Length)

Another thing is this line. Next by default uses the min = 0 and the overload with only the max value is much faster then with the min and max. If you use 0 anyway then you might as well use the Next(max) instead.

---

As far as the Any is concerned I think a join might actually do better then two queries (Find + Any). I suggest trying something like this:

var codeExists =
 (from mc in _context.MaterCodes.AsNoTracking()
 join cc in _context.Campaigns.AsNoTracking() on mc.CampaignCode equals cc.CampaignCode
 where cc.Id == model.CampaignId && mc.Code == code).Any();

Adding .AsNoTracking() might additionaly speed up the query.