public IActionResult GetIndexStatistics(GetIndexStatisticsRequest request) { GetIndexStatisticsResponse result = new GetIndexStatisticsResponse(); if (request != null && request.Filter != null) { HandleException(() => { var data = new IndexStatisticsData(); var totalIndexStatsRepository = DALRepositories.GetTotalIndexStatisticsRepository(); var statementIndexStatisticsRepository = DALRepositories.GetNormalizedStatementIndexStatisticsRepository(); data.TotalIndexStatistics = totalIndexStatsRepository.GetAllForIndex(request.IndexID, request.Filter.DateFrom, request.Filter.DateTo).ToList(); data.IndexSummaryStatementStatistics = statementIndexStatisticsRepository.GetIndexSummaryStatementStatistics(request.IndexID, 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)); }