'Optimize loading and searching from database
I have code that searching for high, medium and low matches by name, postcode and city. It's working well but slow on big amount of comparing data.
Here is code of it
public abstract class GenericScoreSupplierComparisonService<TEntity>
where TEntity : class, ISupplierComparisonWithScore
{
private readonly ILogger<GenericScoreSupplierComparisonService<TEntity>> _logger;
private readonly IServiceProvider _services;
private readonly DataPipelineDbContext _dataPipelineDbContext;
public GenericScoreSupplierComparisonService(ILogger<GenericScoreSupplierComparisonService<TEntity>> logger,
IServiceProvider services, DataPipelineDbContext dataPipelineDbContext)
{
_logger = logger;
_services = services;
_dataPipelineDbContext = dataPipelineDbContext;
}
protected MatchingScoreOutput FindMatch(string name, string postalCode, string city)
{
try
{
string comparisonName = ComparablePropsHelper.GetComparableCompanyName(name);
string comparisonPostalCode = ComparablePropsHelper.GetComparablePostalCode(postalCode);
string comparisonCity = ComparablePropsHelper.GetComparableCompanyName(city);
var highestMatched = _dataPipelineDbContext.JobSuppliers.FirstOrDefault(x =>
x.SupplierNameNormalized == comparisonName && x.City == comparisonCity &&
x.PostalCodeClean == comparisonPostalCode);
var hightMatched = _dataPipelineDbContext.JobSuppliers.FirstOrDefault(x =>
x.SupplierNameNormalized == comparisonName && x.City == comparisonCity);
var mediumMatched = _dataPipelineDbContext.JobSuppliers.FirstOrDefault(x =>
x.SupplierNameNormalized.Contains(comparisonName) && x.City == comparisonCity);
var lowMatсhed = _dataPipelineDbContext.JobSuppliers.FirstOrDefault(x =>
x.SupplierNameNormalized.Contains(comparisonName));
if (highestMatched != null)
{
return new MatchingScoreOutput
{
MatchFound = true,
Confidence = MatchConfidence.HIGH,
MatchedEntity = highestMatched,
Score = 90
};
}
if (hightMatched != null)
{
return new MatchingScoreOutput
{
MatchFound = true,
Confidence = MatchConfidence.HIGH,
MatchedEntity = hightMatched,
Score = 80
};
}
if (mediumMatched != null)
{
return new MatchingScoreOutput
{
MatchFound = true,
Confidence = MatchConfidence.MEDIUM,
MatchedEntity = mediumMatched,
Score = 60
};
}
if (lowMatсhed != null)
{
return new MatchingScoreOutput
{
MatchFound = true,
Confidence = MatchConfidence.LOW,
MatchedEntity = mediumMatched,
Score = 40
};
}
return new MatchingScoreOutput {MatchFound = false, Confidence = MatchConfidence.UNMATCHED,};
}
catch (Exception ex)
{
_logger.LogError(ex,
$"{nameof(GenericScoreSupplierComparisonService<TEntity>)}: Exception occurred. Message: {ex.Message}");
throw;
}
}
}
Problem that I can call this method for list of 200-300 items and JobSuppliers table has 30k items and this whole process is taking much time. Any advice how I can speed up this FindMatch method.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
