Вывод результатов поиска и проблемы с производительностью

[gleb_l]

Вот я писал 7 лет назад на хабре, как можно модифицировать логику поиска с постраничной выдачей, чтобы она стала лечге: https://habr.com/ru/post/189946/

Вкратце:
1. вместо count получаем по условию поиска набор ключей записей, в порядке, определяемом заданной сортировкой (это важно!);
2. засовываем ключи в массив в отданном базой порядке, сохраняем его на клиенте (или на миддл-таере в сессии)
3. пагинацию рассчитываем и делаем по офсетам в нашем массиве, отдавая набор ключей в нужном порядке на сервер для получения нужной порции записей
4. в БД должна быть возможность выдать нужные записи по ключам в том порядке, в котором ключи переданы в процедуру выдачи (для этого есть средства, см. в статье)

В этом случае
а) не нужно использования сложного и тяжелого фильтра каждый раз, когда запрашивается следующая порция;
б) инверсия сортировки при сохранении фильтра (частая операция для юзера — например «сначала дешевые» <--> «сначала дорогие») делается чисто на клиенте — просто заполнением ключей с другого конца массива
в) подзапросы по категориям/свойствам по уже фильтрованным данным можно делать тоже, принимая на вход массив ID, и оперируя только с этим подмножеством
г) получая после первого запроса набор ключей, мы фактически получаем снепшот нужного нам подмножества — и это гарантирует, что пагинация не «съедет» из-за добавления и удаления записей, удовлетворяющих нашему фильтру — добавленные просто не будут видны, а для удаленных можно показывать дисклеймер, что данная запись больше не активна (для этого с таблицей ключей нужно сделать LEFT JOIN)
д) навигация пагинации, построенная на клиенте на основе массива ключей, тоже всегда останется корректной (см. предыдущий пункт)

И наконец — используйте в индексах included-колонки для часто используемых свойств в критериях поиска. Это проще и быстрее, чем создавать комбинации полей в множестве разных составных индексов

И НИКОГДА не делайте COUNT без NOLOCK в случае, если результат вашего запроса нужен для показа юзеру. Вы залочите огромное количество записей в таблице только для того, чтобы получить число, которое уже устареет к моменту, пока передается и рендерится HTML-страница и юзер осознает это число. К тому же, после первой же пагинации все может съехать, так как никто не гарантирует появление записей, удовлетворяющих условию фильтра в любой произвольный момент между запросом COUNT и получением им следующей страницы

[Dansoid]

Если все-же, количество записей важно, не забываем об оконных функциях.

SELECT COUNT(*) OVER () as TotalCount, * 
FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

Теперь в каждой записи будет зашито реальное количество записей. Достаточно прочитать из первой записи это значение, а дальше игнорировать. На вскидку, получается в полтора раза быстрее и один запрос к базе.

Естественно это будет работать только если вы не применяли другие техники выбора страницы.

[gleb_l]

Это хороший способ для некритичных применений (когда всего записей немного, меняются они нечасто, и предикат выборки ложится на индекс) — в противном случае, несмотря на то, что сервер отдаст N записей, начиная с M-ной, оконная функция все равно просканирует все записи, попадающие под предикат — а это локи, и еще раз локи. Причем, вы не можете сказать оконной функции отдельно WITH (NOLOCK), чтобы count вернулся сырым, а сами данные — нет.

Я не знаю, очевидно для вас это или нет — но дедушку лока реально получить при простом сканировании таблицы по чтению, если другие треды осуществляют ее модификацию. Причем, всегда жертвой будет выбран процесс, имеющий минимальное количество модифицированных данных, то есть ваш. Поэтому траверс-операций под локами нужно избегать везде, где без этого можно обойтись.

[YuriyIvon]

Не может этот запрос быть быстрее обычного count без оконной функции, можете посмотреть планы и статистику выполнения. Единственный плюс — объединение двух запросов в один, но схожего эффекта можно достичь посылкой двух раздельных запросов в один раундтрип к серверу БД (драйверы того же MS SQL это позволяет).

[Dansoid]

> Не может этот запрос быть быстрее обычного count без оконной функции

Как бы очевидно.
Но план запроса строится один раз в отличии от двух запросов в одном раундтрипе. И мало какой ADO.NET провайдер такое поддерживает. Сказал же в полтора раза быстрее.

[YuriyIvon]

Возможно я где-то ошибаюсь, но вот на этом примере кода два запроса в один раундтрип по скорости обгоняют комбинированный вариант (сам алгоритм подсчета результатов бенчмарка конечно можно улучшить, но в данном случае это не критично для результата). На большем объеме данных разница по идее будет более заметна.

Заголовок спойлера

class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Benchmarking a single query");
            BenchmarkOneQuery();

            Console.WriteLine("Benchmarking two queries");
            BenchmarkTwoQueries();
        }

        static void BenchmarkOneQuery()
        {
            Benchmark(@"SELECT COUNT(*) OVER () as TotalCount, * 
FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY", reader =>
            {
                while (reader.Read())
                {
                    //Console.WriteLine($"{reader.GetInt32(0)}, {reader.GetString(8)}");
                }
            });
        }

        static void BenchmarkTwoQueries()
        {
            Benchmark(@"SELECT * 
FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY;

SELECT COUNT(*) FROM Sales.SalesOrderHeader WHERE SubTotal > 100"
            , reader =>
            {
                while (reader.Read())
                {
                    //Console.WriteLine($"{reader.GetString(7)}");
                }

                reader.NextResult();

                while (reader.Read())
                {
                    //Console.WriteLine($"{reader.GetInt32(0)}");
                }
            });
        }

        static void Benchmark(string command, Action<SqlDataReader> innerAction)
        {
            using (SqlConnection dbConnection = new SqlConnection(@"Data Source=.;Database=AdventureWorks2016_EXT;Integrated Security=true;"))
            {
                dbConnection.Open();
                using (SqlCommand dbCommand = dbConnection.CreateCommand())
                {
                    dbCommand.CommandText = command;
                    long lastMilliseconds = 0;

                    //Run multiple times and take the last result to make sure warming up doesn't affect the timing
                    for (int i = 0; i < 10; i++)
                    {
                        var sw = Stopwatch.StartNew();
                        using (SqlDataReader reader = dbCommand.ExecuteReader())
                        {
                            innerAction(reader);
                            sw.Stop();

                            lastMilliseconds = sw.ElapsedMilliseconds;
                        }
                    }

                    Console.WriteLine(lastMilliseconds);
                }
            }
        }
    }