Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 74
Добрые люди, если минусуете, то просьба говорить за что… а то получается «анонимных клуб народных мстителей» :)
Готов предположить, что минусуют за мнение о том, что одно единственное преобразование int 64 в int 32 может хоть как-то существенно повлиять на вычисление количества элементов в таблице. На фоне сканирования индекса стоимость этой операции равна нулю (о чем говорят ваши же скриншоты). Так что разницы нет практически никакой.
Удивлен, что всеми везде используется COUNT(*), хотя COUNT(1) дешевле (а зачастую и корректнее).
Оптимизатор для этих запросов генерирует идентичный план выполнения:
Если сравнить планы, то разница только в * и 1:
SELECT COUNT_BIG(*) FROM t1
SELECT COUNT_BIG(1) FROM t1
Если сравнить планы, то разница только в * и 1:
Table 't1'. Scan count 6, logical reads 114911, physical reads 0, ....
SQL Server Execution Times:
CPU time = 2673 ms, elapsed time = 1787 ms.
Table 't1'. Scan count 6, logical reads 114911, physical reads 0, ....
SQL Server Execution Times:
CPU time = 2625 ms, elapsed time = 1878 ms.
Конкретно на этой таблице да. Но семантика этих запросов, согласно стандартам SQL — разная.
Приведите, пожалуйста, пример.
Разница между 1 и * была еще во времена SQL Server 2000. Точно могу сказать, что с 2005 планы генерируются одинаково. Если планы одинаковые, то и выполнение будет одинаковым (в рамках погрешности и при условии, что ресурсы сервера ничем другим в это время не нагружены). Единственное что может отличаться, время компиляции.
Разница между 1 и * была еще во времена SQL Server 2000. Точно могу сказать, что с 2005 планы генерируются одинаково. Если планы одинаковые, то и выполнение будет одинаковым (в рамках погрешности и при условии, что ресурсы сервера ничем другим в это время не нагружены). Единственное что может отличаться, время компиляции.
Люблю в конце подведение итогов в таблицу:
1) как правильно посчитать количество строк
2) как правильно проверить наличие данных
Тема интересная, но вывода нет, это плохо.
90% не нужны промежуточные результаты, им нужны best practices.
1) как правильно посчитать количество строк
2) как правильно проверить наличие данных
Тема интересная, но вывода нет, это плохо.
90% не нужны промежуточные результаты, им нужны best practices.
«Многие, конечно, могут сказать, что этот оператор ничего не стоит при выполнении, но нужно отметить простой факт – SQL Server очень часто недооценивает Compute Scalar операторы.»
Скажу, и скажу что весь пример высосан из пальца. Операция результирующего усечения Int64 до Int32 не просто дешевая, она почти бесплатная.
Скажу, и скажу что весь пример высосан из пальца. Операция результирующего усечения Int64 до Int32 не просто дешевая, она почти бесплатная.
Тут у меня возникает только один вопрос (Я системный администратор, не проектировщик/разработчик БД, но опыт разбора планов запросов имею, да и Microsoft SQL Server 2014 Query Tuning & Optimization читал и очень вдохновился, ну и скули разные видел :D).
Так вопрос вот в чём. Какого ответа вы ждёте на собеседовании, если это вопрос для него? Разработчик должен помнить план запроса на память? Или просто представлять, что существует несколько техник выполнения одной операции? (Практически всегда).
Ведь любой план запроса проектируется под конкретную БД, конкретной системой, в зависимости от кучи факторов: размеров таблиц, уровня параллелизма, информации в статистике и т.д. и т.п.
Так вопрос вот в чём. Какого ответа вы ждёте на собеседовании, если это вопрос для него? Разработчик должен помнить план запроса на память? Или просто представлять, что существует несколько техник выполнения одной операции? (Практически всегда).
Ведь любой план запроса проектируется под конкретную БД, конкретной системой, в зависимости от кучи факторов: размеров таблиц, уровня параллелизма, информации в статистике и т.д. и т.п.
Хороший вопрос. Если честно, то ответа я не жду. У меня свое видение процесса собеседования. Просто задвавать вопросы быстро наскучивает. Гораздо интереснее начать с простого и понятного вопроса и на основе него построить беседу. Многие из тех кого я побеседовал приходили проверить свой уровень, а не устраиваться на работу. Такие моменты быстро можно уловить и тогда остается просто поговорить о чем-то интересном. Опытом обменяться…
Между прочим и меня часто ловили на всякой ереси :) не всегда же можно все держать в голове на собеседовании. Люди же подсознательно всегда волнуются. А так начал с простого и диалог сам собой начинается.
Подобные вопросы на собеседованиях нужно задавать в двух случаях:
1. Если кандидату на вакансию после трудоустройства нужно будет каждый день отвечать на подобные вопросы (это скорее всего DBA, а не программист или админ)
2. Если вы хотите на собеседовании показать свое превосходство в знаниях перед кандидатом.
Если хотите завязать беседу этим вопросом, то дайте кандидату демонстрационный стенд и посмотрите, как он будет сам смотреть планы запросов и проводить тесты. Или заглянет в документацию. Вы не ждете, что во время штатного рабочего процесса, когда возникает подобный вопрос, то ваш сотрудник убирает руки от клавиатуры и как на собеседовании прямо из головы выдает версии и ответы?
1. Если кандидату на вакансию после трудоустройства нужно будет каждый день отвечать на подобные вопросы (это скорее всего DBA, а не программист или админ)
2. Если вы хотите на собеседовании показать свое превосходство в знаниях перед кандидатом.
Если хотите завязать беседу этим вопросом, то дайте кандидату демонстрационный стенд и посмотрите, как он будет сам смотреть планы запросов и проводить тесты. Или заглянет в документацию. Вы не ждете, что во время штатного рабочего процесса, когда возникает подобный вопрос, то ваш сотрудник убирает руки от клавиатуры и как на собеседовании прямо из головы выдает версии и ответы?
Надеюсь у Вас не сложилось мнение, что я люблю «доминировать» на собеседовании. :) Это далеко не так… Ничего сложного я никогда не спрашивал. Лишь то что нужно для работы. И такие вот моменты что были в этом посте… я их не спрашивал, а рассказывал… потому что с людьми было приятно поделиться опытом. Точно также они и мне рассказывали вещи, которые я не знал.
Теперь по поводу простых вопросов… Многие даже не могут на такой вопрос ответить:
Одного он вообще повергнул в шок… И мы вместе сидели и разбирались. Почему AVG вернет — 3, а не 1.5 и тд…
Теперь по поводу простых вопросов… Многие даже не могут на такой вопрос ответить:
DECLARE @t TABLE (a INT)
INSERT INTO @t (a) VALUES (1), (2), (3), (NULL)
SELECT AVG(a), COUNT(*), COUNT(a)
FROM @t
Одного он вообще повергнул в шок… И мы вместе сидели и разбирались. Почему AVG вернет — 3, а не 1.5 и тд…
Я в смятении… на Informix синтаксис несколько другой видимо…
Но результат выдало 2, 4, 3.
Почему среднее значение должно быть 3?
Но результат выдало 2, 4, 3.
Почему среднее значение должно быть 3?
Опечатался… Сорри :)
Оптимизатор раскладывает AVG(a) на операции SUM(a) / COUNT(a)
COUNT(a) подсчитывает значения, которые не NULL. Поэтому и получается результат, который привел BelAnt.
и тут еще был нюанс. Раз столбец INT, то и результат операции AVG будет целочисленным. Например, в такой ситуации среднее значение вернется не совсем такое как ожидается:
Т.е. мы ожидаем 1.5, а будет 1… :)
Оптимизатор раскладывает AVG(a) на операции SUM(a) / COUNT(a)
COUNT(a) подсчитывает значения, которые не NULL. Поэтому и получается результат, который привел BelAnt.
и тут еще был нюанс. Раз столбец INT, то и результат операции AVG будет целочисленным. Например, в такой ситуации среднее значение вернется не совсем такое как ожидается:
SELECT AVG(a)
FROM (VALUES(1), (2)) t(a)
Т.е. мы ожидаем 1.5, а будет 1… :)
AVG вернет 2
(1 + 2 + 3) / 3 = 2
COUNT(a) вернет 3
(1 + 2 + 3) / 3 = 2
COUNT(a) вернет 3
Интересно, а как понять простой вопрос или нет.
Я вот за много лет работы с sql ни разу не использовал функцию AVG — вот не было таких задач, где она нужна. Да и COUNT(a) я не использовал и постараюсь не использовать именно из-за его семантической неоднозначности — код должен не только выполняться правильно, но еще и легко читаться.
Поэтому ответить на ваш «простой вопрос» я могу только из-за того, что это — самый любимый вопрос на собеседованиях, а отнюдь не из-за опыта работы или хорошего знания. Соответственно и выявляет этот простой вопрос не тех, кто имеет работать, а тех, кто умеет готовиться к собеседованиям.
Я вот за много лет работы с sql ни разу не использовал функцию AVG — вот не было таких задач, где она нужна. Да и COUNT(a) я не использовал и постараюсь не использовать именно из-за его семантической неоднозначности — код должен не только выполняться правильно, но еще и легко читаться.
Поэтому ответить на ваш «простой вопрос» я могу только из-за того, что это — самый любимый вопрос на собеседованиях, а отнюдь не из-за опыта работы или хорошего знания. Соответственно и выявляет этот простой вопрос не тех, кто имеет работать, а тех, кто умеет готовиться к собеседованиям.
По поводу такого количества минусов, я немного удивлен. Неужели всем интересно приходить на собеседование и отвечать на вопросы с листочка? Это конвейер чистой воды… :)
просто придя на собеседование ожидаешь разговора по специальности. Какие задачи вы решали, как именно решали и тд. А если внезапно тебя просят ВСПОМНИТЬ какую то фигню, которая конечно где то преподовалась, но смысла держать ее в голове особо нету, но человек проводящий интервью выглядит мудаком, извините.
Особенно актуально это сейчас, когда за ответом на правильно поставленный вопрос даже не стоит лезть в msdn, значительно быстрее и проще он найдется в гугле. Проверять нужно умеет ли человек задавать правильные вопросы гуглу, а не может ли он вспомнить что то из основ.
Особенно актуально это сейчас, когда за ответом на правильно поставленный вопрос даже не стоит лезть в msdn, значительно быстрее и проще он найдется в гугле. Проверять нужно умеет ли человек задавать правильные вопросы гуглу, а не может ли он вспомнить что то из основ.
В случае если нужно проверить наличие записей в таблице, то использование метаданных как было показано выше не даст особых преимуществ…
А как насчет случая, когда в таблице было много данных, а потом их удалили оператором DELETE?
При выполнении разница будет только во времени компиляции запроса.
План будет одинаковый:
Estimated Number of Rows тоже будет идентичным в обоих случаях. А вот количество Actual Number of Rows будет различаться (когда данные есть в таблице и когда их нет, что вполне логично).
IF OBJECT_ID('dbo.test', 'U') IS NOT NULL
DROP TABLE dbo.test
GO
;WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
, E2(N) AS (SELECT 1 FROM E1 a, E1 b)
, E4(N) AS (SELECT 1 FROM E2 a, E2 b)
, E8(N) AS (SELECT 1 FROM E4 a, E4 b)
SELECT val = 1
INTO dbo.test
FROM E8
IF EXISTS(SELECT * FROM dbo.test)
PRINT 1
IF EXISTS(
SELECT *
FROM sys.dm_db_partition_stats
WHERE [object_id] = OBJECT_ID('dbo.test')
AND row_count > 0
AND index_id < 2
) PRINT 1
TRUNCATE TABLE dbo.test
--DELETE TOP(50) PERCENT FROM dbo.test
IF EXISTS(SELECT * FROM dbo.test)
PRINT 1
IF EXISTS(
SELECT *
FROM sys.dm_db_partition_stats
WHERE [object_id] = OBJECT_ID('dbo.test')
AND row_count > 0
AND index_id < 2
) PRINT 1
План будет одинаковый:
Estimated Number of Rows тоже будет идентичным в обоих случаях. А вот количество Actual Number of Rows будет различаться (когда данные есть в таблице и когда их нет, что вполне логично).
Исходный вопрос был не про планы, а про то, что «использование метаданных как было показано выше не даст особых преимуществ».
И ыы что, притворяетесь, что не видите разницу между TRUNCATE и DELETE?
И ыы что, притворяетесь, что не видите разницу между TRUNCATE и DELETE?
«использование метаданных как было показано выше не даст особых преимуществ» эта фраза была в контексте чего приведена?.. Когда нужно проверить есть ли записи в таблице — разницы нет, какой подход применять. Если надо узнать количество строк в таблице, то быстрее всего это можно получить из метаданных. Это данность бытия…
Разница между TRUNCATE и DELETE есть, но вопрос был не о них ранее. Или все же о них?
Разница между TRUNCATE и DELETE есть, но вопрос был не о них ранее. Или все же о них?
Когда нужно проверить есть ли записи в таблице — разницы нет, какой подход применять.
Я хочу уточнить: в случае «когда в таблице было много данных, а потом их удалили оператором DELETE» тоже нет разницы?
Так понятно?
У нас сейчас начинается холивар… Мне что рассказать как работают операции DELETE и TRUNCATE? Про минимальное протоколирование и т.д… Приведите, пожалуйста, пример и расскажите людям что Вас интересует. Я честно, не понимаю сути того, что Вы от меня хотите услышать.
Поверьте, у меня нет никакого намерения начинать холивар. Мы обсуждаем техническую статью, и я прошу уточнить интересующий меня момент. Вы можете ответить на мой вопрос?
У меня с SQL Server опыта меньше, чем с другими СУБД. И в других СУБД (некоторых) разница есть и существенная. Поэтому я и интересуюсь, как обстоит дело здесь.
У меня с SQL Server опыта меньше, чем с другими СУБД. И в других СУБД (некоторых) разница есть и существенная. Поэтому я и интересуюсь, как обстоит дело здесь.
Предположим, что в таблице миллион записей. Мы пытаемся их удалить. Значение количества строк в метаданных не будет изменяться, до тех пор пока в журнале не зафиксируются изменения командой DELETE. Если мы пытаемся очистить таблицу с помощью TRUNCATE, то страницы на которых хранятся таблицы будут помечены как свободные для записи, будет сброшен счетчик числа строк в метаданных…
Надеюсь я смог ответить на Ваш вопрос.
Надеюсь я смог ответить на Ваш вопрос.
Нет. Меня сейчас не интересует процесс удаления. Предположим, записи удалили вчера или вообще неделю назад. Я просто хочу проверить наличие данных в таблице. Есть лм разница между двумя вашими способами с точки зрения:
а) точности;
б) производительности.
Да, еще я часто встречал такой способ «SELECT TOP 1 1 FROM Table», как насчет него?
а) точности;
б) производительности.
Да, еще я часто встречал такой способ «SELECT TOP 1 1 FROM Table», как насчет него?
а) если метаданные для таблицы содержат
б) разницы, как уже говорил ранее — нет. погрешность лишь во времени компиляции каждого конкретного запроса
SQL Server считает что с TOP(1) будет немного быстрее:
Время выполнения в рамках погрешности:
План сравнивал не в SSMS, а в dbForge, чтобы Вы увидели разницу в оценке батчей.
б) разницы, как уже говорил ранее — нет. погрешность лишь во времени компиляции каждого конкретного запроса
Да, еще я часто встречал такой способ «SELECT TOP 1 1 FROM Table», как насчет него?
SELECT TOP 1 1
FROM Sales.SalesOrderDetail
SELECT 1
WHERE EXISTS(
SELECT *
FROM Sales.SalesOrderDetail
)
SQL Server считает что с TOP(1) будет немного быстрее:
Время выполнения в рамках погрешности:
Table 'SalesOrderDetail'. Scan count 1, logical reads 2, physical reads 0, ...
SQL Server Execution Times:
CPU time = 5 ms, elapsed time = 5 ms.
Table 'SalesOrderDetail'. Scan count 1, logical reads 2, physical reads 0, ...
SQL Server Execution Times:
CPU time = 4 ms, elapsed time = 6 ms.
План сравнивал не в SSMS, а в dbForge, чтобы Вы увидели разницу в оценке батчей.
а) если метаданные для таблицы содержатНе понял, что если?
Прощу прощение, видимо часть фразы затер случайно и не заметил.
с точки зрения точности, наиболее корректное число строк можно получить при Full Scan. Но в зависимости от уровня изоляции, хинтов и т.д. понятие «корректное» уйдет на второй план. Например, когда используется хинт NOLOCK — это приводит к грязные чтениям.
По этой причине, я всегда стараюсь смотреть в метаданных эту информацию.
Еще раз спасибо, что обратили внимание на ошибку в ответе.
с точки зрения точности, наиболее корректное число строк можно получить при Full Scan. Но в зависимости от уровня изоляции, хинтов и т.д. понятие «корректное» уйдет на второй план. Например, когда используется хинт NOLOCK — это приводит к грязные чтениям.
По этой причине, я всегда стараюсь смотреть в метаданных эту информацию.
Еще раз спасибо, что обратили внимание на ошибку в ответе.
И быстрее обращения к системным таблицам. Но для задачи «а есть ли там вообще данные» select count(*) будет самым тормознутым, если данные там вдруг оказались.
Мне стало интересно разобраться до конца. Оказалось, что разница в производительности все-таки есть. Причем она зависит от физической организации таблицы. Если таблица организована как куча, то когда мы удаляем строки оператором DELETE, в таблице остаются пустые страницы (в отличие от TRUNCATE). После этого при выполнении запроса, выбирающего хотя бы одну строку, придется просканировать все страницы, чтобы убедиться, что строк нет. В случае же с кластерным индексом пустые страницы, образующиеся при удалении строк, сразу освобождаются.
Эксперимент.
Создаем таблицу-кучу, без кластерного индекса.
Проверяем наличие строк разными способами, обращающимися к таблице. Проверку по представлению sys.dm_db_partition_stats я не включил, так как с ней все понятно, вы уже приводили ее результаты.
Посмотрим на статистику выполнения и заодно на количество страниц в таблице.
Как видно, все три способа требуют 5 логических чтений при заполненной таблице, которая занимает 514 страниц.
Удаляем строки:
Снова проверяем наличие строк.
Смотрим статистику.
Таблица теперь занимает 300 страниц, и для проверки наличия строк пришлось просканировать их все.
Кстати интересно, почему 300. Видимо сработала какая-то внутренняя оптимизация при выполнении DELETE, освободившая часть страниц. Я пробовал удалять в цикле по 10 строк и по одной. В этом случае количество занятых страниц не уменьшается.
Эксперимент с кластерным индексом я не привожу. (Проверка наличия строк делает 2 лог. чтения, а всего в пустой таблице 4 страницы.)
Итак, вывод. В данном частном случае, когда таблица организована как куча и в ней происходят частые вставки и удаления, проверка наличия данных по системным представлениям дает существенное преимущество в производительности.
P.S. Под рукой у меня оказался только SQL 2005, в старших версиях возможно будут отличия.
Эксперимент.
Создаем таблицу-кучу, без кластерного индекса.
use test_dev;
go
if object_id('T4', 'U') is not null
drop table T4;
create table T4 (
ID int
, Data varchar(500)
);
with V as (
select 1 as N
union all
select N+1 from V
where N < 10000
)
insert into T4 (Data)
select cast(replicate(ltrim(str(n)), 100) as varchar(500)) as data from V
option (maxrecursion 0)
;
(строк обработано: 10000)
Проверяем наличие строк разными способами, обращающимися к таблице. Проверку по представлению sys.dm_db_partition_stats я не включил, так как с ней все понятно, вы уже приводили ее результаты.
select 1 where exists (select * from T4);
select top 1 1 from T4;
if exists(select 1 from t4) print 1;
-----------
1
(строк обработано: 1)
-----------
1
(строк обработано: 1)
1
Посмотрим на статистику выполнения и заодно на количество страниц в таблице.
select
cast(object_name(object_id) as varchar(20)) as object_name
,object_id
,used_page_count
,row_count
from sys.dm_db_partition_stats t
where object_id = object_id('T4')
;
select
cast(replace(substring(s2.text, statement_start_offset / 2+1 ,
( (case when statement_end_offset = -1
then (len(convert(nvarchar(max),s2.text)) * 2)
else statement_end_offset end) - statement_start_offset) / 2+1), char(13)+char(10), ' ') as varchar(50)) as sql_statement
,execution_count
,last_physical_reads
,last_logical_reads
,last_logical_writes
,last_elapsed_time
from sys.dm_exec_query_stats as s1
cross apply sys.dm_exec_sql_text(sql_handle) as s2
where s2.objectid is null
order by s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset
;
object_name object_id used_page_count row_count
-------------------- ----------- -------------------- --------------------
T4 741577680 514 10000
(строк обработано: 1)
sql_statement execution_count last_physical_reads last_logical_reads last_logical_writes last_elapsed_time
-------------------------------------------------- -------------------- -------------------- -------------------- -------------------- --------------------
select cast(object_name(object_id) as varchar(20 1 0 2 0 0
with V as ( select 1 as N union all select N+1 1 0 106953 505 204012
select 1 where exists (select * from T4); 1 0 5 0 0
select top 1 1 from T4; 1 0 5 0 0
if exists(select 1 from t4) 1 0 5 0 0
(строк обработано: 5)
Как видно, все три способа требуют 5 логических чтений при заполненной таблице, которая занимает 514 страниц.
Удаляем строки:
delete from T4;
(строк обработано: 10000)
Снова проверяем наличие строк.
select 1 where exists (select * from T4);
select top 1 1 from T4;
if exists(select 1 from t4) print 1;
-----------
(строк обработано: 0)
-----------
(строк обработано: 0)
Смотрим статистику.
object_name object_id used_page_count row_count
-------------------- ----------- -------------------- --------------------
T4 773577794 300 0
(строк обработано: 1)
sql_statement execution_count last_physical_reads last_logical_reads last_logical_writes last_elapsed_time
-------------------------------------------------- -------------------- -------------------- -------------------- -------------------- --------------------
select 1 where exists (select * from T4); 1 0 303 0 0
select top 1 1 from T4; 2 0 303 0 1001
if exists(select 1 from t4) 1 0 303 0 3001
delete from T4; 1 0 3048 129 121007
Таблица теперь занимает 300 страниц, и для проверки наличия строк пришлось просканировать их все.
Кстати интересно, почему 300. Видимо сработала какая-то внутренняя оптимизация при выполнении DELETE, освободившая часть страниц. Я пробовал удалять в цикле по 10 строк и по одной. В этом случае количество занятых страниц не уменьшается.
Эксперимент с кластерным индексом я не привожу. (Проверка наличия строк делает 2 лог. чтения, а всего в пустой таблице 4 страницы.)
Итак, вывод. В данном частном случае, когда таблица организована как куча и в ней происходят частые вставки и удаления, проверка наличия данных по системным представлениям дает существенное преимущество в производительности.
P.S. Под рукой у меня оказался только SQL 2005, в старших версиях возможно будут отличия.
По сути, чтобы получить правильное значение количества строк в таблице, нужно выполнять запрос под уровнем изоляции SERIALIZABLE либо используя хинт TABLOCKXЭто смешно.
При интенсивных вставках-удалениях это самое «правильное значение» устареет очень быстро. Любой алгоритм, работающий с этим значением, должен вести себя так, будто его значение уже устарело — во избежание гонок. Но если оно уже устарело — зачем вообще бороться за его точность?
Никто не говорил, что нужно гоняться за точностью. Лично я всегда использую sys.partitions вместо COUNT(*) по таблице. Но знать о потенциальных проблемах с некорректными данными в системных представлениях нужно. Или хотя бы иметь представление куда копать в случае проблем подобного рода.
На мой взгляд, надо по возможности использовать count(*). А уж sys.partitions использовать только там, где это критично. Читабельность и сопровождаемость кода в большинстве случаев важнее, чем выигранные микросекунды. То есть использовать системные представления надо только для действительно больших таблиц в критичых местах.
Я правда плохо представляю случаи, когда бизнес-логика приложения реально зависит от числа строк в большой и нагруженной таблице. Обычно такие подсчеты нужны не для бизнес-логики, а для задач административного характера. Но тут опыт у всех разный.
Я правда плохо представляю случаи, когда бизнес-логика приложения реально зависит от числа строк в большой и нагруженной таблице. Обычно такие подсчеты нужны не для бизнес-логики, а для задач административного характера. Но тут опыт у всех разный.
А в чем практический смысл отслеживания количества записей по всем таблицам? По идее полезнее считать объем данных и темпы роста этого объема
В SSMS по правому клику на БД можно достаточно быстро сформировать отчет, который содержит как количество строк, так и объем данных по всем таблицам: Reports -> Standard Reports -> Disk Usage by Table
В SSMS по правому клику на БД можно достаточно быстро сформировать отчет, который содержит как количество строк, так и объем данных по всем таблицам: Reports -> Standard Reports -> Disk Usage by Table
Этот отчет не настолько информативный, чтобы мне было ним удобно пользоваться. Я вот таким запросом смотрю то что мне нужно:
SELECT
o.[object_id]
, s.name + '.' + o.name
, o.[type]
, i.total_rows
, total_space = CAST(i.total_pages * 8. / 1024 AS DECIMAL(18,2))
, used_space = CAST(i.used_pages * 8. / 1024 AS DECIMAL(18,2))
, unused_space = CAST((i.total_pages - i.used_pages) * 8. / 1024 AS DECIMAL(18,2))
, index_space = CAST(i.inx_pages * 8. / 1024 AS DECIMAL(18,2))
, data_space = CAST(data_pages * 8. / 1024 AS DECIMAL(18,2))
, is_heap
, i.[partitions]
, i.[indexes]
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
JOIN (
SELECT
i.[object_id]
, is_heap = MAX(CASE WHEN i.index_id = 0 THEN 1 ELSE 0 END)
, total_pages = SUM(a.total_pages)
, used_pages = SUM(a.used_pages)
, inx_pages = SUM(a.used_pages - CASE WHEN a.[type] !=1 THEN a.used_pages WHEN p.index_id IN(0,1) THEN a.data_pages ELSE 0 END)
, data_pages = SUM(CASE WHEN a.[type] != 1 THEN a.used_pages WHEN p.index_id IN (0,1) THEN a.data_pages END)
, total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1 THEN p.[rows] END)
, [partitions] = COUNT(DISTINCT p.partition_number)
, [indexes] = COUNT(DISTINCT p.index_id)
FROM sys.indexes i
JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
WHERE i.is_disabled = 0
AND i.is_hypothetical = 0
GROUP BY i.[object_id]
) i ON o.[object_id] = i.[object_id]
WHERE o.[type] IN ('V', 'U')
AND o.is_ms_shipped = 0
ORDER BY i.total_pages DESC
Простите, но всё(почти) вышесказанное относится исключительно к Microsoft SQL Server?!
Можно было упомянуть об этом в заголовке и/или начале статьи.
Можно было упомянуть об этом в заголовке и/или начале статьи.
Я не работал с большими объёмами данных именно на Sql.
Вы смотрите количество строк в таблице с помощью ANSI и не ANSI версии декларативного языка sql.
Люди на собеседовании вам так говорят, потому что они получили знание или напрямую или опосредованно через книги, которые используют первоисточник: https://msdn.microsoft.com/en-us/library/ms190317.aspx
Попробуйте написать туда, если вы уверены что нашли что-то новое.
Вы смотрите количество строк в таблице с помощью ANSI и не ANSI версии декларативного языка sql.
Люди на собеседовании вам так говорят, потому что они получили знание или напрямую или опосредованно через книги, которые используют первоисточник: https://msdn.microsoft.com/en-us/library/ms190317.aspx
Попробуйте написать туда, если вы уверены что нашли что-то новое.
1. С моей точки зрения такое спрашивать на собеседовании не надо: «Не сказал бы, что существенно, но преобразования типов увеличивает нагрузку на процессор» Лучше спросите какие представления целых чисел со знаком и без знака люди знают или как представлено «действительное» число в компьютере. (Если вы сами не сильно плаваете здесь)
2. Программисты проходят в том или ином виде курс структуры данных (кеширование, списки, хешы, деревья, графы).
Тема дискуссионная, но основной перф мне кажется
1. использоваие правильных структур данных (e.g. хеш таблицы)
2. не вычисление того, что уже вычислено (e.g. кеширование)
3. то, что программисты пишут могло эволюционировать дальше (e.g. развитие)
4. возможность наращивать мощь программы (субд) за счёт поддержки новой аппаратуры (e.g. новые хосты)
Sql не позволяет их сильно вам влиять на все эти аспекты. В этом его прелесть и беда. Если вы считаете кол-во строк во всех таблицах, и так вышло что их 10^6 штук — то посмотрите метаинфу в субд если она есть, или пройдитесь по всем с помощью языка который поддерживает СУБД. Мне кажется это неправильный запрос в рамках данной модели вообще.
2. Программисты проходят в том или ином виде курс структуры данных (кеширование, списки, хешы, деревья, графы).
Тема дискуссионная, но основной перф мне кажется
1. использоваие правильных структур данных (e.g. хеш таблицы)
2. не вычисление того, что уже вычислено (e.g. кеширование)
3. то, что программисты пишут могло эволюционировать дальше (e.g. развитие)
4. возможность наращивать мощь программы (субд) за счёт поддержки новой аппаратуры (e.g. новые хосты)
Sql не позволяет их сильно вам влиять на все эти аспекты. В этом его прелесть и беда. Если вы считаете кол-во строк во всех таблицах, и так вышло что их 10^6 штук — то посмотрите метаинфу в субд если она есть, или пройдитесь по всем с помощью языка который поддерживает СУБД. Мне кажется это неправильный запрос в рамках данной модели вообще.
У меня есть подборка простеньких вопросов, которые я люблю задавать при собеседовании. Например, как посчитать общее число записей к таблице? Вроде бы ничего сложного, но если копнуть глубже, то можно много интересных нюансов рассказать собеседнику.
Повторюсь… Я такие тонкости не спрашиваю. Ключевое слово рассказать… Многие на работу приходят не только за деньгами, а но из за новыми знаниями. Если показать человеку, чему его могут здесь научить, то больше вероятность его положительного ответа на предложение о работе.
Это не тонкости, а обычные фундаментальные знания, на которые ответит любой студент средних курсов. И спрашивать надо это. Я уверен, что в моём окружении программистов нет людей, которые считают что count(1), count(*) содержат хоть какое-либо «знание». Вам нравится спрашивать это — спрашивайте.
Зачем люди приходят — там много начиная от социального желания человека работать, так и аспектов как деньги и знания…
«Его могут здесь научить» взаимная учёба здесь и вас тоже научит человек этот новому, если у него есть страсть к области. Главное уметь учиться, а вот вопрос про count это не показатель обучаемости,..., но!!! — Это по крайней мере повод для начала разговора)
Зачем люди приходят — там много начиная от социального желания человека работать, так и аспектов как деньги и знания…
«Его могут здесь научить» взаимная учёба здесь и вас тоже научит человек этот новому, если у него есть страсть к области. Главное уметь учиться, а вот вопрос про count это не показатель обучаемости,..., но!!! — Это по крайней мере повод для начала разговора)
Предположим человек приходит на должность SQL Server Database Developer. В резюме 3 года опыта работы с БД под SQL Server. Если он не может ответить на такой вопрос… какие результаты вернет запрос… даже после получаса наводящих вопросов, то поневоле стоит задуматься…
А по поводу этого примера, так это конструкция языка, которой пользуются очень часто. Нужно понимать возможности того языка, который используешь.
SELECT COUNT(1), COUNT(*), COUNT(val), COUNT(DISTINCT val)
FROM (
VALUES (1), (1), (2), (NULL), (NULL)
) t (val)
А по поводу этого примера, так это конструкция языка, которой пользуются очень часто. Нужно понимать возможности того языка, который используешь.
Мне незнакомы конструкции когда в from стоит не имя таблицы, и не select-expression, но я и работать к вам идти не собираюсь.
Если вы девушку выбираете, посмотрите какие у ноги — красивые или нет. Я работ много поменял, и такие как вы мне встречались часто. Дело не в ногах, и не в count синтаксисе, а дело в страсти к области.
Вот как подходил к этой проблеме Леонардо Да Винчи (рассказывает Арнольд В.И.): https://www.youtube.com/watch?v=Fl33ryn9Xs8
Если вы девушку выбираете, посмотрите какие у ноги — красивые или нет. Я работ много поменял, и такие как вы мне встречались часто. Дело не в ногах, и не в count синтаксисе, а дело в страсти к области.
Вот как подходил к этой проблеме Леонардо Да Винчи (рассказывает Арнольд В.И.): https://www.youtube.com/watch?v=Fl33ryn9Xs8
К чему такая латентная агрессия...? Не пойму чем я Вас задел. «Такие как вы»… Это какие? :)
Теперь по существу. Ситуации номер раз. Приходит на работу джуниор, коим и я когда-то был. С него, что должен быть спрос много-чего знать? Нет. Только желание работать и развиваться.
Ситуация номер два. Приходит человек с «опытом» и просит соответствующий оклад. Мне про что с ним нужно говорить...? Про абстрактные основы институтской программы? Думаю, что нет. Нужно понять его уровень квалификации и готов ли он выполнять свою работу. А для этого нет необходимости пудрить серое вещество спрашивая про вещи, которые в повседневности не нужны. И тут мы возвращаемся к тому простому вопросу о котором было написано в самом начале.
Теперь по существу. Ситуации номер раз. Приходит на работу джуниор, коим и я когда-то был. С него, что должен быть спрос много-чего знать? Нет. Только желание работать и развиваться.
Ситуация номер два. Приходит человек с «опытом» и просит соответствующий оклад. Мне про что с ним нужно говорить...? Про абстрактные основы институтской программы? Думаю, что нет. Нужно понять его уровень квалификации и готов ли он выполнять свою работу. А для этого нет необходимости пудрить серое вещество спрашивая про вещи, которые в повседневности не нужны. И тут мы возвращаемся к тому простому вопросу о котором было написано в самом начале.
У меня никакой аггрессии, если что)
Это какие?
Такие кто задают вопросы ответ на который можно получить открыв стандарт или документацию по программе.
Не инженеры, а юристы.
Вам нужно понять подходит ли человек, а ему понять подходит ли контора по «засемплированному» (выборочным) впечатлениям.
Совсем не семплировать тоже не правильно, понимаю, что-то ведь спрашивать надо…
Мне про что с ним нужно говорить...? — про фундаментальные основы.
Про абстрактные основы институтской программы? — да в том числе. с упором на те которые применимы в вашей ситуации.
«Основы институтской программы» — да, они позволяют мыслить и заниматься инженерной деятельностью и развиваться.
Суть в том, чтобы спросить чему обучиться тяжело и долго…
Но если ваша компания не занимается инженерной деятельностью (ну не Яндекс вы, не Майкрософт, не Оракл) вы не занимаетесь инженерной деятельностью с большой буквы — то нет ничего страшного и паниковать не надо.
Такие люди всё равно смогут вас чему-то научить лично и вы будете более умный с течением времени, если будете с ними общаться. Вы сами будете рости. Вам самому польза будет и вы побольше о мире узнаете. Только не стесняйтесь спрашивать их. Когда я когда-то кого-то выбирал я ориентировался на своё впечатление и всё.
Это тема как говориться очень «холиварная».
Это какие?
Такие кто задают вопросы ответ на который можно получить открыв стандарт или документацию по программе.
Не инженеры, а юристы.
Вам нужно понять подходит ли человек, а ему понять подходит ли контора по «засемплированному» (выборочным) впечатлениям.
Совсем не семплировать тоже не правильно, понимаю, что-то ведь спрашивать надо…
Мне про что с ним нужно говорить...? — про фундаментальные основы.
Про абстрактные основы институтской программы? — да в том числе. с упором на те которые применимы в вашей ситуации.
«Основы институтской программы» — да, они позволяют мыслить и заниматься инженерной деятельностью и развиваться.
Суть в том, чтобы спросить чему обучиться тяжело и долго…
Но если ваша компания не занимается инженерной деятельностью (ну не Яндекс вы, не Майкрософт, не Оракл) вы не занимаетесь инженерной деятельностью с большой буквы — то нет ничего страшного и паниковать не надо.
Такие люди всё равно смогут вас чему-то научить лично и вы будете более умный с течением времени, если будете с ними общаться. Вы сами будете рости. Вам самому польза будет и вы побольше о мире узнаете. Только не стесняйтесь спрашивать их. Когда я когда-то кого-то выбирал я ориентировался на своё впечатление и всё.
Это тема как говориться очень «холиварная».
Мне незнакомы конструкции когда в from стоит не имя таблицы, и не select-expression, но я и работать к вам идти не собираюсь.Тут из контекста понятно, даже есть не знать эту конструкцию.
В данной задаче, это всего лишь компактный способ задать таблицу с тестовыми данными. Подвох — не в VALUES.
Я бы сказал, что эта статья любопытная, но теперь получается, что когда у меня стоит вопрос посчитать число записей в таблице, то я должен заморачиваться поиском нужной системной таблицы (ведь у каждого сервера она своя), потому что это быстрей?
Просто все подсказки, что я в своё время находил в интернете — люди просто использовали COUNT(*) и всё.
Просто все подсказки, что я в своё время находил в интернете — люди просто использовали COUNT(*) и всё.
Если в ваших таблицах меньше десятка миллионов записей — используйте COUNT(*) и не заморачивайтесь.
Я не разрабатывал СУБД. А работал только с ansi sql только на малых таблицах 8 лет назад.
count(*) выдаёт кол-во строк в таблице влючая дубликаты и null значения — СУБД может понять запрос на кол-во строк в таблице и взять её из метафинмормации… Более того это официальная ручка для получение кол-во строчек в таблице, всякие каталоги (как я знаю) не стандартизированы.
И всё же если это не так — поменяйтся СУБД на новую версию или на совершенно другую....(правда в последнем случае возможно придётся переписывать код)
count(*) выдаёт кол-во строк в таблице влючая дубликаты и null значения — СУБД может понять запрос на кол-во строк в таблице и взять её из метафинмормации… Более того это официальная ручка для получение кол-во строчек в таблице, всякие каталоги (как я знаю) не стандартизированы.
И всё же если это не так — поменяйтся СУБД на новую версию или на совершенно другую....(правда в последнем случае возможно придётся переписывать код)
Еще я знаю людей, которые любят использовать SUM вместо COUNT:
Не верю. Вот реально есть люди которые количество элементов через сумму считают?
джеки_чан.жепег
SELECT
E2WP.DepID
, [total] = SUM(1)
, [boss_male] = SUM(CASE WHEN ET.Code LIKE N'Boss' AND E2WP.Code LIKE N'M' THEN 1 ELSE 0 END)
, [boss_female] = SUM(CASE WHEN ET.Code LIKE N'Boss' AND E2WP.Code LIKE N'F' THEN 1 ELSE 0 END)
FROM dbo.tbl_Emp2WP E2WP
JOIN dbo.tbl_EmpType ET ON E2WP.EmpTypeID = ET.EmpTypeID
GROUP BY E2WP.DepID
далеко ходить не надо :) пример из жизни… с проекта, который когда рефакторил.
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
COUNT(*)