From f9702672f42e30e65670c71ed3258eb3c46443ee Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Fri, 25 Nov 2016 12:36:00 -0500 Subject: optimize series display --- .../Data/SqliteItemRepository.cs | 181 ++++++++++++++------- 1 file changed, 119 insertions(+), 62 deletions(-) (limited to 'Emby.Server.Implementations/Data/SqliteItemRepository.cs') diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index f017a21dcd..67aa6cc3b2 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -664,11 +664,18 @@ namespace Emby.Server.Implementations.Data { var requiresReset = false; - using (var saveItemStatement = db.PrepareStatement(GetSaveItemCommandText())) + var statements = db.PrepareAll(string.Join(";", new string[] { - using (var deleteAncestorsStatement = db.PrepareStatement("delete from AncestorIds where ItemId=@ItemId")) + GetSaveItemCommandText(), + "delete from AncestorIds where ItemId=@ItemId", + "insert into AncestorIds (ItemId, AncestorId, AncestorIdText) values (@ItemId, @AncestorId, @AncestorIdText)" + })).ToList(); + + using (var saveItemStatement = statements[0]) + { + using (var deleteAncestorsStatement = statements[1]) { - using (var updateAncestorsStatement = db.PrepareStatement("insert into AncestorIds (ItemId, AncestorId, AncestorIdText) values (@ItemId, @AncestorId, @AncestorIdText)")) + using (var updateAncestorsStatement = statements[2]) { foreach (var tuple in tuples) { @@ -2576,16 +2583,42 @@ namespace Emby.Server.Implementations.Data } } + var totalRecordCount = 0; + var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0; + + var statementTexts = new List(); + if (!isReturningZeroItems) + { + statementTexts.Add(commandText); + } + if (query.EnableTotalRecordCount) + { + commandText = string.Empty; + + if (EnableGroupByPresentationUniqueKey(query)) + { + commandText += " select count (distinct PresentationUniqueKey)" + GetFromText(); + } + else + { + commandText += " select count (guid)" + GetFromText(); + } + + commandText += GetJoinUserDataText(query); + commandText += whereTextWithoutPaging; + statementTexts.Add(commandText); + } + using (var connection = CreateConnection(true)) { using (WriteLock.Read()) { - var totalRecordCount = 0; - var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0; + var statements = connection.PrepareAll(string.Join(";", statementTexts.ToArray())) + .ToList(); if (!isReturningZeroItems) { - using (var statement = connection.PrepareStatement(commandText)) + using (var statement = statements[0]) { if (EnableJoinUserData(query)) { @@ -2608,33 +2641,22 @@ namespace Emby.Server.Implementations.Data } } - commandText = string.Empty; - - if (EnableGroupByPresentationUniqueKey(query)) - { - commandText += " select count (distinct PresentationUniqueKey)" + GetFromText(); - } - else - { - commandText += " select count (guid)" + GetFromText(); - } - - commandText += GetJoinUserDataText(query); - commandText += whereTextWithoutPaging; - - using (var statement = connection.PrepareStatement(commandText)) + if (query.EnableTotalRecordCount) { - if (EnableJoinUserData(query)) + using (var statement = statements[statements.Count - 1]) { - statement.TryBind("@UserId", query.User.Id); - } + if (EnableJoinUserData(query)) + { + statement.TryBind("@UserId", query.User.Id); + } - BindSimilarParams(query, statement); + BindSimilarParams(query, statement); - // Running this again will bind the params - GetWhereClauses(query, statement); + // Running this again will bind the params + GetWhereClauses(query, statement); - totalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); + totalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); + } } LogQueryTime("GetItems", commandText, now); @@ -2966,58 +2988,77 @@ namespace Emby.Server.Implementations.Data } var list = new List(); + var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0; + + var statementTexts = new List(); + if (!isReturningZeroItems) + { + statementTexts.Add(commandText); + } + if (query.EnableTotalRecordCount) + { + commandText = string.Empty; + + if (EnableGroupByPresentationUniqueKey(query)) + { + commandText += " select count (distinct PresentationUniqueKey)" + GetFromText(); + } + else + { + commandText += " select count (guid)" + GetFromText(); + } + + commandText += GetJoinUserDataText(query); + commandText += whereTextWithoutPaging; + statementTexts.Add(commandText); + } using (var connection = CreateConnection(true)) { + var statements = connection.PrepareAll(string.Join(";", statementTexts.ToArray())) + .ToList(); + using (WriteLock.Read()) { var totalRecordCount = 0; - using (var statement = connection.PrepareStatement(commandText)) + if (!isReturningZeroItems) { - if (EnableJoinUserData(query)) + using (var statement = statements[0]) { - statement.TryBind("@UserId", query.User.Id); - } + if (EnableJoinUserData(query)) + { + statement.TryBind("@UserId", query.User.Id); + } - BindSimilarParams(query, statement); + BindSimilarParams(query, statement); - // Running this again will bind the params - GetWhereClauses(query, statement); + // Running this again will bind the params + GetWhereClauses(query, statement); - foreach (var row in statement.ExecuteQuery()) - { - list.Add(row[0].ReadGuid()); + foreach (var row in statement.ExecuteQuery()) + { + list.Add(row[0].ReadGuid()); + } } } - commandText = string.Empty; - - if (EnableGroupByPresentationUniqueKey(query)) - { - commandText += " select count (distinct PresentationUniqueKey)" + GetFromText(); - } - else - { - commandText += " select count (guid)" + GetFromText(); - } - - commandText += GetJoinUserDataText(query); - commandText += whereTextWithoutPaging; - - using (var statement = connection.PrepareStatement(commandText)) + if (query.EnableTotalRecordCount) { - if (EnableJoinUserData(query)) + using (var statement = statements[statements.Count - 1]) { - statement.TryBind("@UserId", query.User.Id); - } + if (EnableJoinUserData(query)) + { + statement.TryBind("@UserId", query.User.Id); + } - BindSimilarParams(query, statement); + BindSimilarParams(query, statement); - // Running this again will bind the params - GetWhereClauses(query, statement); + // Running this again will bind the params + GetWhereClauses(query, statement); - totalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); + totalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); + } } LogQueryTime("GetItemIds", commandText, now); @@ -4875,13 +4916,29 @@ namespace Emby.Server.Implementations.Data var list = new List>(); var count = 0; + var statementTexts = new List(); + if (!isReturningZeroItems) + { + statementTexts.Add(commandText); + } + if (query.EnableTotalRecordCount) + { + var countText = "select count (distinct PresentationUniqueKey)" + GetFromText(); + + countText += GetJoinUserDataText(query); + countText += whereText; + statementTexts.Add(countText); + } + using (var connection = CreateConnection(true)) { using (WriteLock.Read()) { + var statements = connection.PrepareAll(string.Join(";", statementTexts.ToArray())).ToList(); + if (!isReturningZeroItems) { - using (var statement = connection.PrepareStatement(commandText)) + using (var statement = statements[0]) { statement.TryBind("@SelectType", returnType); if (EnableJoinUserData(query)) @@ -4919,7 +4976,7 @@ namespace Emby.Server.Implementations.Data commandText += GetJoinUserDataText(query); commandText += whereText; - using (var statement = connection.PrepareStatement(commandText)) + using (var statement = statements[statements.Count - 1]) { statement.TryBind("@SelectType", returnType); if (EnableJoinUserData(query)) -- cgit v1.2.3 From d54c11fb747e3cf0369ddade42c93d7e795f5a4f Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Fri, 25 Nov 2016 13:17:26 -0500 Subject: add image safeguards --- .../Data/SqliteItemRepository.cs | 25 +++++++++++++++++++--- 1 file changed, 22 insertions(+), 3 deletions(-) (limited to 'Emby.Server.Implementations/Data/SqliteItemRepository.cs') diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 67aa6cc3b2..727a9c4bb2 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -1122,7 +1122,9 @@ namespace Emby.Server.Implementations.Data return null; } - return string.Join("|", images.Select(ToValueString).ToArray()); + var imageStrings = images.Where(i => !string.IsNullOrWhiteSpace(i.Path)).Select(ToValueString).ToArray(); + + return string.Join("|", imageStrings); } private void DeserializeImages(string value, BaseItem item) @@ -1141,7 +1143,12 @@ namespace Emby.Server.Implementations.Data foreach (var part in parts) { - item.ImageInfos.Add(ItemImageInfoFromValueString(part)); + var image = ItemImageInfoFromValueString(part); + + if (image != null) + { + item.ImageInfos.Add(image); + } } } @@ -1149,7 +1156,14 @@ namespace Emby.Server.Implementations.Data { var delimeter = "*"; - return (image.Path ?? string.Empty) + + var path = image.Path; + + if (path == null) + { + path = string.Empty; + } + + return path + delimeter + image.DateModified.Ticks.ToString(CultureInfo.InvariantCulture) + delimeter + @@ -1162,6 +1176,11 @@ namespace Emby.Server.Implementations.Data { var parts = value.Split(new[] { '*' }, StringSplitOptions.None); + if (parts.Length != 4) + { + return null; + } + var image = new ItemImageInfo(); image.Path = parts[0]; -- cgit v1.2.3