Индексная производительность SQL Server - длинный столбец

Метод setHours может принимать необязательные аргументы minutes, seconds и ms, например:

var d = new Date();
d.setHours(0,0,0,0);

, который установит время на 00:00:00.000 из вашего текущего часового пояса , если вы хотите работать в UTC, вы можете использовать метод setUTCHours .

7
задан Ian Elliott 14 July 2009 в 01:59
поделиться

5 ответов

You're thinking about this from the wrong direction:

  • Do create indexes you need to meet performance goals
  • Do NOT create indexes you don't need

Whether a column is a binary(16) or nvarchar(2000) makes little difference there, because you don't just go add indexes willy nilly.

Don't let index choice dictate your column types. If you need to index an nvarchar(2000) consider a fulltext index or adding a hash value for the column and index that.


Based on your update, I would probably create either a checksum column or a computed column using the HashBytes() function and index that. Note that a checksum isn't the same as a cryptographic hash and so you are somewhat more likely have collisions, but you can also match the entire contents of the text and it will filter with the index first. HashBytes() is less likely to have collisions, but it is still possible and so you still need to compare the actual column. HashBytes is also more expensive to compute the hash for each query and each change.

6
ответ дан 6 December 2019 в 11:51
поделиться

КОНЕЧНО двоичный (16) будет НАМНОГО быстрее - просто выполните самые быстрые вычисления:

  • страница SQL Server всегда 8 КБ
  • , если у вас 16 байтов на запись, вы можете сохранить 500 записей на странице
  • с 4000 байтами на запись (nvarchar), вы получите 2 записи на страницу (в худшем случае, если ваш NVARCHAR (2000) полностью заполнен)

Если у вас есть таблица со 100000 записей, вам понадобится 200 страниц для индекса с двоичным (16) ключом, а вам понадобится 50 000 страниц для того же индекса с nvarchar (2000)

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

Marc

ОБНОВЛЕНИЕ:
Для моих обычных индексов я стараюсь по возможности избегать составных индексов - ссылки на них из других таблиц становятся довольно беспорядочными (предложения WHERE с несколькими сравнениями на равенство).

Кроме того, регулярно проверяйте и поддерживайте свои индексы - если у вас есть более 30% фрагментации, rebuild - если у вас фрагментация 5-30%, реорганизуйте. Посмотрите автоматический, хорошо протестированный сценарий обслуживания индекса БД на http://sqlfool.com/2009/06/index-defrag-script-v30/

. Для кластерного ключа на В таблице SQL Server старайтесь избегать идентификаторов GUID, поскольку они случайны по своей природе и, таким образом, вызывают потенциально массивную фрагментацию индекса и, следовательно, снижают производительность. Кроме того, хотя это и не является жестким требованием, постарайтесь убедиться, что ваш кластерный ключ уникален - если это не так, SQL Server добавит к нему четырехбайтовый уникальный определитель. Также,

6
ответ дан 6 December 2019 в 11:51
поделиться

У вас может быть не более 900 байт на запись индекса, поэтому ваш nvarchar (2000) не будет работать. Самая большая разница будет заключаться в глубине индекса - количестве страниц, которые нужно пройти от корневой до конечной. Итак, если вам нужно выполнить поиск, вы можете проиндексировать контрольную сумму, например:

alter table recipe add text_checksum as checksum(recipe_text)
create index text_checksum_ind on recipe(text_checksum)

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

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

Еще одна идея - закрепить ваш nvarchar в меньшее двоичное значение и индекс для него, но можете ли вы гарантировать, что каждое значение всегда будет заархивировано до 900 байт или меньше?

3
ответ дан 6 December 2019 в 11:51
поделиться

На самом деле лучше протестировать и убедиться в этом. Например, следующий сценарий сравнивает поиск по индексу с помощью 4-байтового целого числа и поиск по 50-байтовому символу. Это 3 чтения для int (глубина B-дерева, построенного на столбце INT) и 4 чтения для char (глубина B-дерева, построенного на столбце CHAR).

CREATE TABLE dbo.NarrowKey(n INT NOT NULL PRIMARY KEY, m INT NOT NULL)
GO
DECLARE @i INT;
SET @i = 1;
INSERT INTO dbo.NarrowKey(n,m) SELECT 1,1;
WHILE @i<1024000 BEGIN
  INSERT INTO dbo.NarrowKey(n,m)
    SELECT n + @i, n + @i FROM dbo.NarrowKey;
  SET @i = @i * 2;
END;
GO
DROP TABLE dbo.WideKey
GO
CREATE TABLE dbo.WideKey(n CHAR(50) NOT NULL PRIMARY KEY, m INT NOT NULL)
GO
DECLARE @i INT;
SET @i = 1;
INSERT INTO dbo.WideKey(n,m) SELECT '1',1;
WHILE @i<1024000 BEGIN
  INSERT INTO dbo.WideKey(n,m)
    SELECT CAST((m + @i) AS CHAR(50)), n + @i FROM dbo.WideKey;
  SET @i = @i * 2;
END;
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT * FROM dbo.NarrowKey WHERE n=123456
SELECT * FROM dbo.WideKey WHERE n='123456'

Поиск индекса выполняется на 33% медленнее для более широкого ключа, но таблица в 4 раза больше:

EXEC sp_spaceused 'dbo.NarrowKey';
-- 32K
EXEC sp_spaceused 'dbo.WideKey';
-- 136K
0
ответ дан 6 December 2019 в 11:51
поделиться

In index max length is 900 bytes anyway, so you cannot index NVARCHAR(2000).

A larger index key means fewer keys fit in the index pages so it creates a larger tree, more disk used, more I/O, more buffer pull, less caching. For clustered keys this is far worse because the clustered key value is used as the lookup value on all other non-clustered, indexes, so it increases the size of all indexes.

Ultimately the single most prevalent performance driving metric in a query is the number of pages scanned/seek-ed. This translates into physical reads (=I/O wait time) or logical reads (=cache pollution).

Other than space considerations, data types make little to no difference in a query behavior. char/varchar/nchar/nvarchar have collations that needs to be taken into account on comparisons, but the cost of collation order lookup is usually not a deciding factor.

And last but not least, probably the most important factor, is your application access pattern. Index the columns that make queries SARGable, there is absolutely no benefit in having to maintain an index that is not used by the optimizer.

And sometimes you have to consider concurrency issues, like when you have to eliminate deadlocks caused by distinct update access path to the same record.

Update after post edit

Use a persisted MD5 hash column:

create table foo (
    bar nvarchar(2000) not null, 
    [hash] as hashbytes('MD5', bar) persisted not null,
    constraint pk_hash unique ([hash]));
go


insert into foo (bar) values (N'Some text');
insert into foo (bar) values (N'Other text');
go

select * from foo
    where [hash] = hashbytes('MD5', N'Some text');
go

You have to be very careful with your seeks, the hash will differ wildly for any difference in input, ie. if you seek Ascii parameter instead of Unicode one...

You'll have a decent collision chance if your table grows big.

2
ответ дан 6 December 2019 в 11:51
поделиться
Другие вопросы по тегам:

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