Нужно количество строк после инструкции SELECT: какой оптимальный подход SQL?

Ответ 1

Есть только два способа быть на 100% уверенными, что COUNT(*) и фактический запрос будут давать согласованные результаты:

• Объедините COUNT(*) с запросом, как в вашем подходе 2. Я рекомендую форму, которую вы показываете в своем примере, а не коррелированную форму подзапроса, указанную в комментарии от kogus.
• Используйте два запроса, как в вашем подходе 1, после запуска транзакции на уровне изоляции SNAPSHOT или SERIALIZABLE.

Использование одного из этих уровней изоляции важно, потому что любой другой уровень изоляции позволяет новым строкам, созданным другими клиентами, стать видимыми в текущей транзакции. Подробнее см. Документацию MSDN на SET TRANSACTION ISOLATION.

Ответ 2

Если вы используете SQL Server, после вашего запроса вы можете выбрать функцию @@RowCount (или если в вашем результирующем наборе может быть более 2 миллиардов строк, используйте функцию BIGROW_COUNT()). Это вернет количество строк, выбранных предыдущим оператором, или количество строк, на которые влияет инструкция insert/update/delete.

SELECT my_table.my_col
  FROM my_table
 WHERE my_table.foo = 'bar'

SELECT @@Rowcount

Или, если вы хотите подсчитать счет, включенный в результат, отправленный аналогично подходу №2, вы можете использовать предложение OVER (см. http://msdn.microsoft.com/en-us/library/ms189461.aspx 1).

SELECT my_table.my_col,
    count(*) OVER(PARTITION BY my_table.foo) AS 'Count'
  FROM my_table
 WHERE my_table.foo = 'bar'

Использование предложения OVER будет иметь гораздо лучшую производительность, чем использование подзапроса, чтобы получить количество строк. Использование @@RowCount будет иметь лучшую производительность, потому что не будет никакой стоимости запроса для выражения select @@RowCount

Обновление в ответ на комментарий: в примере, который я дал, будет указано количество строк в разделе, определяемое в этом случае "PARTITION BY my_table.foo". Значение столбца в каждой строке - это число строк с таким же значением my_table.foo. Поскольку ваш примерный запрос имел предложение WHERE my_table.foo = 'bar' ", все строки в наборе результатов будут иметь то же значение my_table.foo, и поэтому значение в столбце будет одинаковым для всех строк и равно (в этот случай) это # ​​строк в запросе.

Вот лучший/более простой пример того, как включать столбец в каждую строку, которая является полным количеством строк в наборе результатов. Просто удалите необязательное предложение Partition By.

SELECT my_table.my_col, count(*) OVER() AS 'Count'
  FROM my_table
 WHERE my_table.foo = 'bar'

Ответ 3

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

Я предлагаю вам связать суб-запрос с внешним запросом, чтобы гарантировать, что условие в вашем счете соответствует условию в наборе данных.

SELECT 
  mt.my_row,
 (SELECT COUNT(mt2.my_row) FROM my_table mt2 WHERE mt2.foo = mt.foo) as cnt
FROM my_table mt
WHERE mt.foo = 'bar';

Ответ 4

Если вы обеспокоены тем, что число строк, удовлетворяющих условию, может измениться за несколько миллисекунд с момента выполнения запроса и поиска результатов, вы могли/должны выполнить запросы внутри транзакции:

BEGIN TRAN bogus

SELECT COUNT( my_table.my_col ) AS row_count
FROM my_table
WHERE my_table.foo = 'bar'

SELECT my_table.my_col
FROM my_table
WHERE my_table.foo = 'bar'
ROLLBACK TRAN bogus

Это всегда вернет правильные значения.

Кроме того, если вы используете SQL Server, вы можете использовать @@ROWCOUNT, чтобы получить количество строк, затронутых последним оператором, и перенаправить вывод реального запроса в таблицу temp или табличную переменную, чтобы вы могли вернуть все в целом, и нет необходимости в транзакции:

DECLARE @dummy INT

SELECT my_table.my_col
INTO #temp_table
FROM my_table
WHERE my_table.foo = 'bar'

SET @[email protected]@ROWCOUNT
SELECT @dummy, * FROM #temp_table

Ответ 5

Вот несколько идей:

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

Ответ 6

Если вы действительно обеспокоены тем, что количество строк будет меняться между подсчетом выбора и оператором select, почему бы не сначала выбрать ваши строки в таблице temp? Таким образом, вы знаете, что будете синхронизированы.

Ответ 7

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

Ответ 8

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

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

Количество строк не изменится - google для ACID и SQL.

Ответ 9

IF (@@ROWCOUNT > 0)
BEGIN
SELECT my_table.my_col
  FROM my_table
 WHERE my_table.foo = 'bar'
END

Ответ 10

Просто добавьте это, потому что это лучший результат в google для этого вопроса. В sqlite я использовал это, чтобы получить строку.

WITH temptable AS
  (SELECT one,two
   FROM
     (SELECT one, two
      FROM table3
      WHERE dimension=0
      UNION ALL SELECT one, two
      FROM table2
      WHERE dimension=0
      UNION ALL SELECT one, two
      FROM table1
      WHERE dimension=0)
   ORDER BY date DESC)
SELECT *
FROM temptable
LEFT JOIN
  (SELECT count(*)/7 AS cnt,
                        0 AS bonus
   FROM temptable) counter
WHERE 0 = counter.bonus