public IActionResult GetStoredProcedureStatistics(GetStoredProcedureStatisticsRequest request)
{
GetStoredProcedureStatisticsResponse result = new GetStoredProcedureStatisticsResponse();
if (request != null && request.Filter != null)
{
HandleException(() =>
{
var data = new StoredProcedureStatisticsData();
var totalStoredProcStatsRepository = DALRepositories.GetTotalStoredProcedureStatisticsRepository();
data.TotalStoredProcedureStatistics = totalStoredProcStatsRepository.GetAllForProcedure(request.StoredProcedureID, request.Filter.DateFrom, request.Filter.DateTo).ToList();
result.Data = data;
result.IsSuccess = result.Data != null;
}, ex => result.ErrorMessage = ex.Message);
}
return(Json(result));
}
public IActionResult GetUnusedObjects(GetUnusedObjectsRequest request)
{
GetUnusedObjectsResponse result = new GetUnusedObjectsResponse();
if (request != null)
{
HandleException(() =>
{
result.Data = new UnusedObjectsData();
result.Data.Objects = new List <UnusedObjectData>();
var now = DateTime.Now;
DateTime thresholdDate = now.AddDays(-30);
var totalIndexStatsRepo = DALRepositories.GetTotalIndexStatisticsRepository();
var totalRelationStatsRepo = DALRepositories.GetTotalRelationStatisticsRepository();
var totalProceduresStatsRepo = DALRepositories.GetTotalStoredProcedureStatisticsRepository();
var totalViewsStatsRepo = DALRepositories.GetTotalViewStatisticsRepository();
bool areDataAvailableForWholePeriod = totalIndexStatsRepo.AreDataAvailableForWholePeriod(thresholdDate, now.Date) ||
totalRelationStatsRepo.AreDataAvailableForWholePeriod(thresholdDate, now.Date) ||
totalProceduresStatsRepo.AreDataAvailableForWholePeriod(thresholdDate, now.Date) ||
totalViewsStatsRepo.AreDataAvailableForWholePeriod(thresholdDate, now.Date);
var indicesLastKnownCollectionDate = totalIndexStatsRepo.GetForAllLastKnownCollectionDate(request.DatabaseID);
var relationsLastKnownCollectionDate = totalRelationStatsRepo.GetForAllLastKnownCollectionDate(request.DatabaseID);
var proceduresLastKnownCollectionDate = totalProceduresStatsRepo.GetForAllLastKnownCollectionDate(request.DatabaseID);
var viewsLastKnownCollectionDate = totalViewsStatsRepo.GetForAllLastKnownCollectionDate(request.DatabaseID);
using (var scope = Converter.CreateDatabaseScope(request.DatabaseID))
{
foreach (var i in DBMSRepositories.GetIndicesRepository().GetAll())
{
bool?isUnused = null;
DateTime?lastKnownUsageDate = null;
if (indicesLastKnownCollectionDate.TryGetValue(i.ID, out var lastKnownCollectionDate))
{
lastKnownUsageDate = lastKnownCollectionDate;
if (areDataAvailableForWholePeriod)
{
isUnused = lastKnownCollectionDate < thresholdDate;
}
}
result.Data.Objects.Add(new UnusedObjectData()
{
IsUnused = isUnused, LastKnownUsageDate = lastKnownUsageDate,
Name = i.Name + $" ON {i.SchemaName}.{i.RelationName}", ObjectType = "Index", SchemaName = i.SchemaName
});
}
foreach (var r in DBMSRepositories.GetRelationsRepository().GetAll())
{
bool?isUnused = null;
DateTime?lastKnownUsageDate = null;
if (relationsLastKnownCollectionDate.TryGetValue(r.ID, out var lastKnownCollectionDate))
{
lastKnownUsageDate = lastKnownCollectionDate;
if (areDataAvailableForWholePeriod)
{
isUnused = lastKnownCollectionDate < thresholdDate;
}
}
result.Data.Objects.Add(new UnusedObjectData()
{
IsUnused = isUnused,
LastKnownUsageDate = lastKnownUsageDate,
Name = r.Name,
ObjectType = "Relation",
SchemaName = r.SchemaName
});
}
foreach (var p in DBMSRepositories.GetStoredProceduresRepository().GetAll())
{
bool?isUnused = null;
DateTime?lastKnownUsageDate = null;
if (proceduresLastKnownCollectionDate.TryGetValue(p.ID, out var lastKnownCollectionDate))
{
lastKnownUsageDate = lastKnownCollectionDate;
if (areDataAvailableForWholePeriod)
{
isUnused = lastKnownCollectionDate < thresholdDate;
}
}
result.Data.Objects.Add(new UnusedObjectData()
{
IsUnused = isUnused,
LastKnownUsageDate = lastKnownUsageDate,
Name = p.Name,
ObjectType = "Stored procedure",
SchemaName = p.SchemaName
});
}
foreach (var v in DBMSRepositories.GetViewsRepository().GetAll())
{
bool?isUnused = null;
DateTime?lastKnownUsageDate = null;
if (viewsLastKnownCollectionDate.TryGetValue(v.ID, out var lastKnownCollectionDate))
{
lastKnownUsageDate = lastKnownCollectionDate;
if (areDataAvailableForWholePeriod)
{
isUnused = lastKnownCollectionDate < thresholdDate;
}
}
result.Data.Objects.Add(new UnusedObjectData()
{
IsUnused = isUnused,
LastKnownUsageDate = lastKnownUsageDate,
Name = v.Name,
ObjectType = "View",
SchemaName = v.SchemaName
});
}
}
result.Data.Objects.Sort((x, y) =>
{
var cmp = (x.LastKnownUsageDate?.Ticks ?? double.MaxValue).CompareTo(y.LastKnownUsageDate?.Ticks ?? double.MaxValue);
if (cmp == 0)
{
cmp = x.SchemaName.CompareTo(y.SchemaName);
if (cmp == 0)
{
cmp = x.Name.CompareTo(y.Name);
}
}
return(cmp);
});
result.IsSuccess = result.Data != null;
}, ex => result.ErrorMessage = ex.Message);
}
return(Json(result));
}