optimize series display

1 files changed, 119 insertions, 62 deletions

diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs
index f017a21dc..67aa6cc3b 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<string>();
+            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<Guid>();
+            var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0;
+
+            var statementTexts = new List<string>();
+            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<Tuple<BaseItem, ItemCounts>>();
             var count = 0;
 
+            var statementTexts = new List<string>();
+            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))