Вопрос по sql-server, sql, sql-server-2005 – Эффективный способ получения @@ rowcount из запроса с использованием row_number

30

У меня дорогой запрос с использованием функции row_number over () в SQL Server 2005. Я возвращаю только подсписок этих записей, поскольку запрос разбит на страницы. Однако я хотел бы также вернуть общее количество записей, а не только разбитое на страницы подмножество. Эффективно выполнить запрос дважды, чтобы получить счет, не может быть и речи.

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

Что мне действительно нравится, так это @@ ROW_NUMBERROWCOUNT :-)

Ваш Ответ

4   ответа
36

например так:

    SELECT
     ROW_NUMBER() OVER(ORDER BY object_id, column_id) as RowNum
    , COUNT(*) OVER(PARTITION BY 1) as TotalRows
    , * 
    FROM master.sys.columns

Это ИМХО лучший способ сделать это без необходимости делать два запроса.

Спасибо, именно то, что я искал !!! Phil Bennett
Действительно, очень хорошее решение !, хотя вы добавляете в запрос столбец, который нужен только для первой строки. Но я думаю, что это быстрее и использует меньше ресурсов, чем два отдельных запроса ...
0

помещая весь набор результатов с row_number во временную таблицу, затем использую @@ rowcount из этого и использую запрос для этого, чтобы вернуть страницу данных, которые мне нужны.

4

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

По моему опыту, нет ничего лучше, чем выполнять два отдельных запроса: один для получения страницы данных и один для получения общего количества. Использование временной таблицы для подсчета общего количества - стратегия проигрышная, так как количество строк увеличивается. Например, стоимость вставки 10 000 000 миллионов строк во временную таблицу просто для их подсчета, очевидно, будет чрезмерной.

Хотя я полностью с вами согласен, этот запрос сложен, и в этом случае мне нужно пройти путь оптимизации. Phil Bennett
36

За эти годы куча пота разработчиков превратилась в эффективные пейджинговые наборы результатов. Тем не менее, нет единого ответа - это зависит от вашего варианта использования. Часть варианта использования - эффективная загрузка вашей страницы, часть - выяснение количества строк в полном наборе результатов. Извините, если я немного забочусь на пейджинг, но оба довольно тесно связаны между собой.

Существует множество стратегий, большинство из которых являются плохими, если у вас есть какой-либо объем данных & amp; не подходит к случаю использования. Хотя это и не полный список, ниже приведены некоторые варианты .....

Run Separate Count(*)

  • run a separate query that does a simple "select count(*) from MyTable"
  • simple and easy for a small table
  • good on an unfiltered large table that is either narrow or has a compact non-clustered index you can use
  • breaks down when you have a complicated WHERE/JOIN criteria because running the WHERE/JOIN twice is expensive.
  • breaks down on a wide index because the number of reads goes up.

Combine ROW_Number() OVER() and COUNT(1) OVER(PARTITION By 1)

  • This was suggested by @RBarryYoung. It has the benefit of being simple to implement and very flexible.
  • The down side is that there are a lot of reasons this can become extremely expensive quickly.
  • For example, in a DB i'm currently working there is a Media table with about 6000 rows. It's not particularly wide, has a integer clustered PK and, as well as a compact unique index. Yet, a simple COUNT(*) OVER(PARTITION BY 1) as TotalRows results in ~12,000 reads. Compare that to a simple SELECT COUNT(*) FROM Media -- 12 reads. Wowzers.

UPDATE -- the reads issue I mentioned is a bit of red-herring. It turns out, that with windowed functions the unit used to measure reads is kind of mixed. The net result is what appears to be massive numbers of reads. You can see more on the issue here : Why are logical reads for windowed aggregate functions so high?

Temp Tables / Table Variables

  • There are lots of strategies that take a result set and insert relevant keys or segments of results into temp tables / table variables.
  • For small/medium sized result sets this can provide great results.
  • This type of strategy works across almost any platform/version of SQL.
  • Operating on a result set multiple times (quite often a requirement) is also easy.
  • The down side is when working with large results sets ... inserting a few million rows into a temp table has a cost.
  • Compounding the issue, in a high volume system pressure on TempDB can be quite a factor, and temp tables are effectively working in TempDB.

Gaussian Sum / Double Row Number

  • This idea relies on subset of something the mathematician Gauss figured out (how to sum a series of numbers). The subset is how to get row count from any point in the table.
  • From a series of numbers (Row_Number()) the row count for 1 to N is (N + 1) - 1. More explanation in the links.
  • The formula seems like it would net out to just N, but the if you stick with the formula an interesting things happens, you can figure out row count from a page in the middle of the table.
  • The net result is you do ROW_Number() OVER(Order by ID) and ROW_Number() OVER(Order by ID DESC) then sum the two numbers and subtract 1.
  • Using my Media table as an example my reads dropped from 12,000 to about 75.
  • In a larger page you've ended up repeating data many many times, but the offset in reads may be worth it.
  • I haven't tested this on too many scenarios, so it may fall apart in other scenarios.

Top (@n) / SET ROWCOUNT

  • These aren't specific strategies per-se, but are optimizations based on what we know about the query optimizer.
  • Creatively using Top(@n) [top can be a variable in SQL 2008] or SET ROWCOUNT can reduce your working set ...even if you're pulling a middle page of a result set you can still narrow the result
  • These ideas work because of query optimizer behavior ...a service pack/hotfix can change the behavior (although probably not).
  • In certian instances SET ROWCOUNT can be a bit in accurate
  • This strategy doesn't account for getting the full row count, just makes paging more efficient

So what's a developer to do?

Читайте мой хороший человек, читайте. Вот несколько статей, на которые я опирался ...

Надеюсь, это поможет.

@Martin - только что видел ответ. Спасибо, что дали мне знать.
@Martin - Я тоже не разобрался со всем этим, но заметил несколько вещей. Во-первых, я подозреваю, что вы видите в чтениях памяти (т.е. ваша таблица кэшируется). При большем наборе данных производительность взорвется. Во-вторых, кажется, что ветвь план выполнения для каждой оконной функции (как если бы вы сделали соединение). В-третьих, вы часто сталкиваетесь с поиском ключевых слов - посмотрите на «Начальное решение». и «альтернативные методы»; в & quot; Оптимизации подкачки на стороне сервера - часть I & quot; статья.
Я заметил, что логические чтения для оконных агрегатных функций также могут показаться очень высокими. Они не кажутся вредными для производительности, ноI would still like to know exactly why! чтобы оценить это немного больше.
Да, они определенно в памяти читает. Он заполняет катушку и повторно использует ту же катушку с перемотками и повторными привязками в других областях плана (общая катушка подвыражения). Я просто не могу учесть такое большое количество операций чтения, учитывая план и данные.
Кстати.I just got an answer to my original question и оказывается, что логические чтения для катушек учитываются иначе, чем для обычных таблиц. Таким образом, цифры не сопоставимы напрямую.

Похожие вопросы