ImmortalCAT
@ImmortalCAT
C# loving

Что эффективнее работает Join Composite Key или Primary Key?

Что эффективнее работает для выборки и Join'ов быстродействия Composite Key или Primary Key для SqlServer? Например: 1) 1 PK - Int, 2) 1 PK - Datetime, 3) Year, Month - int Composite Key, 4) Year, Month, Day - int Composite Key. Чем можно замерить perfomance? Что лучше Select + Where или для Select + Join с такой же по типу ключей таблице?
  • Вопрос задан
  • 105 просмотров
Решения вопроса 2
tsklab
@tsklab Куратор тега SQL Server
Здесь отвечаю на вопросы.
Точного ответа нет. Даже если в какой-то момент что-то будет быстрее, в зависимости от использования, будут построены кэши и статистики и следующий запрос будет работать по-другому (включите статистику клиента: он покажет изменения от запроса к запросу +/- красный/зелёный).
Это MS SQL, вы никогда не сможет сделать лучше его оптимизатора. Помочь можно: есть "Помощник по настройке ядра СУБД". Или самый первый уровень: включите действительный план выполнения и статистику активных запросов— если у оптимизатора будет предложение, оно будет зелёным цветом в виде сценария CREATE (только подставите названия объектов).

Да и использование datetime в качестве первичного ключа (только сейчас дошло) — не допустимо:
тестируем

CREATE TABLE [dbo].[Table_5](
	[ID] [datetime] NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Table_5] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table_5] ADD  CONSTRAINT [DF_Table_5_ID]  DEFAULT (getdate()) FOR [ID]
GO

Попробуем добавить:
INSERT INTO Table_5 (Name)
  SELECT TOP(5) 'TEST' + CAST(ROW_NUMBER() OVER(ORDER BY id) AS VARCHAR)
         FROM sysobjects

Не получается:
Нарушено "PK_Table_5" ограничения PRIMARY KEY. 
Не удается вставить повторяющийся ключ в объект"dbo.Table_5". 
Повторяющееся значение ключа: (ноя 12 2021  3:06PM).


Более подробно: системный таймер Windows, по-умолчанию, срабатывает 1000/64 раз в секунду, а точность datetime 1000/300, что должно было бы обеспечить уникальность последовательных значений. Но MS SQL, с помощью timeBeginPeriod, увеличивает частоту срабатывания таймера до 1000/1000. То есть при записи в datetime и округления до его точности, значения потеряют уникальность. Это известная коллизия этого типа данных не позволяет использовать его, как первичный ключ. В 1С есть специальная обработка для построения хронологических цепочек (например, что бы различать оплату и аванс относительно отгрузки), которая устанавливает на документах несовпадающее время.
1) 1 PK - Int, 2) 1 PK - Datetime, 3) Year, Month - int Composite Key, 4) Year, Month, Day - int Composite Key
datetime - это int+int. В первом — дата, во втором — время, которое, исходя из 3 и 4 пункта использовано не будет (и эти пункты излишни, естественно). Если вы хотите использовать дату как первичный ключ — используйте тип данных date (3 байта).
Ответ написан
Mikhail_E
@Mikhail_E
1С, SQL
Если смотреть по размеру :
1) Int = 4 байта,
2) Datetime = 8 байт,
3) Int + Int = 8 байт,
4) Int + Int + Int = 12 байт.
Чтение данных происходит по 8 кб (по умолчанию, т.к. это размер страницы данных по умолчанию.)
На Одной странице Int поместится в 2 раза больше чем DateTime (Если таблицы имеют одинаковое количество строк и индекс не фрагментирован) поэтому количество страниц данных Datetime в 2 раза больше, следовательно чтений будет в 2 раза больше.

Т.е. по нагрузке на диск получается
Рейтинг эффективности :
1) 1 PK - Int
2/3) 1 PK - Datetime / Year, Month - int Composite Key
4) Year, Month, Day - int Composite Key

Другой вопрос что поиск по одному значению - быстрее чем поиск по 2-м значениям.
а среди 1 и 2, разница в количестве страниц. Получается 1 лучше чем 2, а 2 лучше чем 3 и 4.
Чем можно замерить perfomance?

Через Profiler можно замерить время выполнения, количество чтений с диска и процессорное время.
События :
ShowplanXMLStatisticsProfile– графический план выполнения запроса
RPC:Completed– текст запроса, если он выполняется как процедура
SQL:BatchCompleted– текст запроса, если он выполняется как обычный запрос
Поля: Reads, CPU, Duration

Что лучше будет для Select + Where и для Select + Join

1 или 2-е, в зависимости от использования (Чтобы избежать лишних преобразований типов при запросах)
Ответ написан
Пригласить эксперта
Ответы на вопрос 1
vabka
@vabka
Токсичный шарпист
Чем можно замерить perfomance?
Как это сделать правильно?

Sql server profiler + query plan
Обычный PK будет быстрее, чем композитный. int будет быстрее, чем DateTime
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы