public PackageDependents GetPackageDependents(string id)
{
if (string.IsNullOrWhiteSpace(id))
{
throw new ArgumentNullException(nameof(id));
}
PackageDependents result = new PackageDependents();
result.TopPackages = GetListOfDependents(id);
result.TotalPackageCount = GetDependentCount(id);
return(result);
}
public PackageDependents GetPackageDependents(string id)
{
if (string.IsNullOrWhiteSpace(id))
{
throw new ArgumentNullException(nameof(id));
}
PackageDependents result = new PackageDependents();
using (_entitiesContext.WithQueryHint("RECOMPILE"))
{
result.TopPackages = GetListOfDependents(id);
result.TotalPackageCount = GetDependentCount(id);
}
return(result);
}
public PackageDependents GetPackageDependents(string id)
{
if (string.IsNullOrWhiteSpace(id))
{
throw new ArgumentNullException(nameof(id));
}
PackageDependents result = new PackageDependents();
// We use OPTIMIZE FOR UNKNOWN by default here because there are distinct 2-3 query plans that may be
// selected via SQL Server parameter sniffing. Because SQL Server caches query plans, this means that the
// first parameter plus query combination that SQL sees defines which query plan is selected and cached for
// all subsequent parameter values of the same query. This could result in a non-optimal query plan getting
// cached depending on what package ID is viewed first. Using OPTIMIZE FOR UNKNOWN causes a predictable
// query plan to be cached.
//
// For example, the query plan for Newtonsoft.Json is very good for that specific parameter value since
// there are so many package dependents but the same query plan takes a very long time for packages with few
// or no dependents. The query plan for "UNKNOWN" (that is a package ID with unknown SQL Server statistic)
// behaves somewhat poorly for Newtonsoft.Json (2-5 seconds) but very well for the vast majority of
// packages. Because we have in-memory caching above this layer, OPTIMIZE FOR UNKNOWN is acceptable other
// unconfigured cases similar to Newtonsoft.Json because the extra cost of the non-optimal query plan is
// amortized over many, many page views. For the long tail packages, in-memory caching is less effective
// (low page views) so an optimal query should be selected for this category.
//
// For the cases where RECOMPILE is known to perform the best, the package ID can be added to the query hint
// configuration JSON file from the content object service. This should only be done when the following
// things are true:
//
// 1. The overhead of SQL Server recompile is worth it. We have seen the overhead to be 5-50ms.
// 2. SQL Server has up to date statistics which will lead to the proper query plan being selected.
// 3. SQL Server actually picks the proper query plan. We have observed cases where this does not happen
// even with up-to-date statistics.
//
var useRecompile = _contentObjectService.QueryHintConfiguration.ShouldUseRecompileForPackageDependents(id);
using (_entitiesContext.WithQueryHint(useRecompile ? "RECOMPILE" : "OPTIMIZE FOR UNKNOWN"))
{
result.TopPackages = GetListOfDependents(id);
result.TotalPackageCount = GetDependentCount(id);
}
return(result);
}
