optimize series display

3 files changed, 190 insertions, 123 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))
diff --git a/Emby.Server.Implementations/Security/AuthenticationRepository.cs b/Emby.Server.Implementations/Security/AuthenticationRepository.cs
index dad05718c..2632b9666 100644
--- a/Emby.Server.Implementations/Security/AuthenticationRepository.cs
+++ b/Emby.Server.Implementations/Security/AuthenticationRepository.cs
@@ -70,9 +70,9 @@ namespace Emby.Server.Implementations.Security
 
             cancellationToken.ThrowIfCancellationRequested();
 
-            using (var connection = CreateConnection())
+            using (WriteLock.Write())
             {
-                using (WriteLock.Write())
+                using (var connection = CreateConnection())
                 {
                     connection.RunInTransaction(db =>
                     {
@@ -137,78 +137,78 @@ namespace Emby.Server.Implementations.Security
                 throw new ArgumentNullException("query");
             }
 
-            using (var connection = CreateConnection(true))
+            using (WriteLock.Read())
             {
-                var commandText = BaseSelectText;
-
-                var whereClauses = new List<string>();
-
-                var startIndex = query.StartIndex ?? 0;
-
-                if (!string.IsNullOrWhiteSpace(query.AccessToken))
+                using (var connection = CreateConnection(true))
                 {
-                    whereClauses.Add("AccessToken=@AccessToken");
-                }
+                    var commandText = BaseSelectText;
 
-                if (!string.IsNullOrWhiteSpace(query.UserId))
-                {
-                    whereClauses.Add("UserId=@UserId");
-                }
+                    var whereClauses = new List<string>();
 
-                if (!string.IsNullOrWhiteSpace(query.DeviceId))
-                {
-                    whereClauses.Add("DeviceId=@DeviceId");
-                }
+                    var startIndex = query.StartIndex ?? 0;
 
-                if (query.IsActive.HasValue)
-                {
-                    whereClauses.Add("IsActive=@IsActive");
-                }
+                    if (!string.IsNullOrWhiteSpace(query.AccessToken))
+                    {
+                        whereClauses.Add("AccessToken=@AccessToken");
+                    }
 
-                if (query.HasUser.HasValue)
-                {
-                    if (query.HasUser.Value)
+                    if (!string.IsNullOrWhiteSpace(query.UserId))
                     {
-                        whereClauses.Add("UserId not null");
+                        whereClauses.Add("UserId=@UserId");
                     }
-                    else
+
+                    if (!string.IsNullOrWhiteSpace(query.DeviceId))
                     {
-                        whereClauses.Add("UserId is null");
+                        whereClauses.Add("DeviceId=@DeviceId");
                     }
-                }
 
-                var whereTextWithoutPaging = whereClauses.Count == 0 ?
-                    string.Empty :
-                    " where " + string.Join(" AND ", whereClauses.ToArray());
+                    if (query.IsActive.HasValue)
+                    {
+                        whereClauses.Add("IsActive=@IsActive");
+                    }
 
-                if (startIndex > 0)
-                {
-                    var pagingWhereText = whereClauses.Count == 0 ?
+                    if (query.HasUser.HasValue)
+                    {
+                        if (query.HasUser.Value)
+                        {
+                            whereClauses.Add("UserId not null");
+                        }
+                        else
+                        {
+                            whereClauses.Add("UserId is null");
+                        }
+                    }
+
+                    var whereTextWithoutPaging = whereClauses.Count == 0 ?
                         string.Empty :
                         " where " + string.Join(" AND ", whereClauses.ToArray());
 
-                    whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM AccessTokens {0} ORDER BY DateCreated LIMIT {1})",
-                        pagingWhereText,
-                        startIndex.ToString(_usCulture)));
-                }
+                    if (startIndex > 0)
+                    {
+                        var pagingWhereText = whereClauses.Count == 0 ?
+                            string.Empty :
+                            " where " + string.Join(" AND ", whereClauses.ToArray());
 
-                var whereText = whereClauses.Count == 0 ?
-                    string.Empty :
-                    " where " + string.Join(" AND ", whereClauses.ToArray());
+                        whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM AccessTokens {0} ORDER BY DateCreated LIMIT {1})",
+                            pagingWhereText,
+                            startIndex.ToString(_usCulture)));
+                    }
 
-                commandText += whereText;
+                    var whereText = whereClauses.Count == 0 ?
+                        string.Empty :
+                        " where " + string.Join(" AND ", whereClauses.ToArray());
 
-                commandText += " ORDER BY DateCreated";
+                    commandText += whereText;
 
-                if (query.Limit.HasValue)
-                {
-                    commandText += " LIMIT " + query.Limit.Value.ToString(_usCulture);
-                }
+                    commandText += " ORDER BY DateCreated";
 
-                var list = new List<AuthenticationInfo>();
+                    if (query.Limit.HasValue)
+                    {
+                        commandText += " LIMIT " + query.Limit.Value.ToString(_usCulture);
+                    }
+
+                    var list = new List<AuthenticationInfo>();
 
-                using (WriteLock.Read())
-                {
                     using (var statement = connection.PrepareStatement(commandText))
                     {
                         BindAuthenticationQueryParams(query, statement);
@@ -244,9 +244,9 @@ namespace Emby.Server.Implementations.Security
                 throw new ArgumentNullException("id");
             }
 
-            using (var connection = CreateConnection(true))
+            using (WriteLock.Read())
             {
-                using (WriteLock.Read())
+                using (var connection = CreateConnection(true))
                 {
                     var commandText = BaseSelectText + " where Id=@Id";
 
diff --git a/Emby.Server.Implementations/Sync/SyncRepository.cs b/Emby.Server.Implementations/Sync/SyncRepository.cs
index 308c3bd00..d8bec1ce3 100644
--- a/Emby.Server.Implementations/Sync/SyncRepository.cs
+++ b/Emby.Server.Implementations/Sync/SyncRepository.cs
@@ -516,9 +516,23 @@ namespace Emby.Server.Implementations.Sync
                     commandText += " where " + string.Join(" AND ", whereClauses.ToArray());
                 }
 
+                var statementTexts = new List<string>
+                {
+                    commandText
+                };
+
+                commandText = commandText
+                    .Replace("select ItemId,Status,Progress from SyncJobItems", "select ItemIds,Status,Progress from SyncJobs")
+                    .Replace("'Synced'", "'Completed','CompletedWithError'");
+
+                statementTexts.Add(commandText);
+
                 using (WriteLock.Read())
                 {
-                    using (var statement = connection.PrepareStatement(commandText))
+                    var statements = connection.PrepareAll(string.Join(";", statementTexts.ToArray()))
+                        .ToList();
+
+                    using (var statement = statements[0])
                     {
                         if (!string.IsNullOrWhiteSpace(query.TargetId))
                         {
@@ -532,13 +546,9 @@ namespace Emby.Server.Implementations.Sync
                         LogQueryTime("GetSyncedItemProgresses", commandText, now);
                     }
 
-                    commandText = commandText
-                        .Replace("select ItemId,Status,Progress from SyncJobItems", "select ItemIds,Status,Progress from SyncJobs")
-                        .Replace("'Synced'", "'Completed','CompletedWithError'");
-
                     now = DateTime.UtcNow;
 
-                    using (var statement = connection.PrepareStatement(commandText))
+                    using (var statement = statements[1])
                     {
                         if (!string.IsNullOrWhiteSpace(query.TargetId))
                         {