SQL 2005 оптимальная подкачка

При создании «сетки» записей с настраиваемой разбивкой по страницам, какой лучший/оптимальный способ запросить общее количество записей, а также начало и конец записей с помощью С#?

SQL для возврата набора выгружаемых записей:

SELECT Some, Columns, Here FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY Column ASC) AS RowId, *
    FROM
        Records
    WHERE
        (...)
) AS tbl
WHERE ((RowId > @Offset) AND (RowId <= (@Offset + @PageSize)) )

SQL для подсчета общего количества записей:

SELECT COUNT(*) FROM Records WHERE (...)

Прямо сейчас я делаю два похода на сервер: один для получения записей, а другой для подсчета общего количества записей.

Каковы наилучшие способы объединения этих запросов, чтобы избежать многократных обращений к БД?


.net sql c# sql-server-2005
person David Murdoch    schedule 18.05.2010    source источник

Ответы (3)


arrow_upward
2
arrow_downward

Вы можете использовать хранимую процедуру с выходным параметром (или возвращаемым значением) для передачи общего количества строк.

create procedure dbo.Stuff_GetAll (
    @StartRowIndex int, -- zero based
    @MaximumRows int
)
as
begin
    declare @TotalRows int

    select @TotalRows = count(*) 
    ...

    if (@TotalRows > 0 and @MaximumRows > 0)
    begin
        ;with T as (
            select *, row_number() over ()
            ...
        )
        select T.* from T
        where Row between @StartRowIndex + 1 and (@StartRowIndex + @MaximumRows)
    end

    return @TotalRows
end
GO

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

person UserControl    schedule 18.05.2010
comment
Лично я ненавижу использовать SP. Они всегда были кошмаром для меня, чтобы поддерживать. Можно ли как-то это сделать без создания SP? - person David Murdoch; 18.05.2010
comment
Да, вы можете использовать union для объединения обоих запросов в один, но для этого потребуется динамическое построение запроса (выглядит слишком сложным для меня, даже если я ненавижу SP :). - person UserControl; 18.05.2010
comment
Я думал об использовании NextResult после выбора двух таблиц. как здесь. Но я не знаю, как это реализовать на С#. - person David Murdoch; 18.05.2010
comment
Интересно, никогда не слышал о возможности разделения нескольких SELECT с помощью ; в одном запросе. Вот псевдокод (в данный момент не могу проверить, извините): string query = select pageddata ... ; select count(*) ... var command = new SqlCommand(query, conn); Читатель IDataReader = command.ExecuteReader(); while (reader.Read()) // обработка строки reader.NextResult(); // перейти к результату count() if (reader.Read()) long totalRows = (long)reader[0]; - person UserControl; 18.05.2010
comment
По моему опыту, это решение может работать плохо, так как вам придется выполнять один и тот же запрос дважды. То есть, чтобы заполнить @TotalRows, вы должны выполнить необработанный запрос (например, предложение From, Where, причина и т. д.) дважды (один раз для Count(*) и один раз в CTE, где вы получаете выгруженные результаты). Если запрос очень сложный и/или у вас много данных, это не сработает. - person Thomas; 02.06.2010

arrow_upward
2
arrow_downward

Самый быстрый способ, который я нашел, - вернуть количество строк в наборе результатов:

With PagedItems As
    (
    Select ...
        , ROW_NUMBER() OVER ( ORDER BY Column ASC ) As Seq
        , ROW_NUMBER() OVER ( ORDER BY Column DESC ) As ReverseSeq
    From Table
    Where ....
    )
Select ..., ( ReverseSeq + Seq - 1) As TotalRows
From PagedItems
Where RowId > @Offeset
    ANd RowId <= ( @Offset + @PageSize )
person Thomas    schedule 18.05.2010
comment
Это не. Выглядит очень элегантно, даже если у вас есть индекс, который используется для упорядочивания ASC, на практике случай DESC намного медленнее. Более того, результирующий набор должен содержать одну и ту же информацию в каждой возвращаемой строке. - person UserControl; 01.06.2010
comment
@UserControl - не думайте. Система сделает один проход по рядам. В моих тестах это было заметно быстрее, чем временная таблица или подзапрос с count(*). - person Thomas; 02.06.2010
comment
@UserControl - Кроме того, возврат счетчика в виде столбца для каждой строки не имеет значения. Помните, что мы возвращаем не более @PageSize в строках, поэтому дополнительные 4 байта для каждой строки не повлияют на производительность. - person Thomas; 02.06.2010
comment
готов поспорить, что SQL Server 2005 может гарантировать одинаковую скорость для запросов на упорядочение как ASC, так и DESC (особенно при наличии одного индекса в столбце). На самом деле у вас часто даже нет индекса для столбцов, которые вы хотите упорядочить (например, свободный выбор пользователем порядка столбцов в сетке). На самом деле порядок не имеет ничего общего с темой, за исключением того, что функция ранжирования требует этого (но не одновременно и ASC, и DESC!), чтобы убедиться, что у нас есть согласованные наборы результатов. - person UserControl; 02.06.2010

arrow_upward
0
arrow_downward

Одним из способов было бы превратить ваш запрос в хранимую процедуру, а затем иметь выходной параметр, который позволяет вам подсчитывать общее количество записей. Вы должны заполнить выходной параметр внутри процедуры, а затем вернуть свой набор записей так же, как сейчас.

person jaltiere    schedule 18.05.2010