Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Индекс по "куску" строки  [new]
NewBie123
Guest
Добрый день, есть таблица с 3мя полями, NVARCHAR(100), int, и image.
На поле nvarchar есть кластерный индекс, таблица размером 55гб. В основном, к этой таблице идет запросы на поиск записи по куска строки nvarchar, а именно, WHERE SUBSTRING(<nvarchar строка>,3,31) = <VALUE> . Оптимизатор использует Clustered index scan (что естественно), и это работает долго. Некластерный индекс на это же поле смысла вешать не вижу, будет то же самое, если только не построить как-нибудь индекс на этом куске строки SUBSTRING(<nvarchar строка>,3,31), можно ли это как-нибудь реализовать?
Приложение, которое так обращается к базе переписать не можем, оно строннее, доступа к коду нет. В дальнейшем будем переходить на другое..
Что можно предпринять, подскажите?
28 авг 14, 13:37    [16506783]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
Glory
Member

Откуда:
Сообщений: 104751
NewBie123
Что можно предпринять, подскажите?

Индекс по вычисляемому полю.
Если редакция сервера позволяет
28 авг 14, 13:41    [16506808]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
NewBie123
Некластерный индекс на это же поле смысла вешать не вижу, будет то же самое, если только не построить как-нибудь индекс на этом куске строки SUBSTRING(<nvarchar строка>,3,31), можно ли это как-нибудь реализовать?
Во первых, всё таки индекс может и иметь смысл, поскольку его может быть сканить проще, чем кластерный индекс (зависит от ширины записи).

Во вторых, можно сделать индекс по части строки, сделав вычисляемое поле и индекс по нему.
28 авг 14, 13:42    [16506818]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
NewBie123
Guest
Glory,

Редакция позволяет, но приложение обращается только к данному полю, я не могу менять структуру таблицы, вставив вычисляемое поле, а потом перенаправив запрос на него
28 авг 14, 13:44    [16506832]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
Glory
Member

Откуда:
Сообщений: 104751
NewBie123
но приложение обращается только к данному полю,

В смысле ? запрос WHERE SUBSTRING(<nvarchar строка>,3,31) = <VALUE> формирует приложение ?
28 авг 14, 13:47    [16506849]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
Если вы не можете изменить исходный запрос, то вряд ли что-то принципиальное можно сделать. Индекс по этой строке может немного ускорить. Можно пробовать со статистикой поиграться, но это тоже полумера.
28 авг 14, 13:47    [16506851]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
NewBie123
Glory,

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

таблицу переименовать а под старым именем сделать view в котором подменить это поле на вычисляемое
28 авг 14, 13:48    [16506862]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
LexusR
таблицу переименовать а под старым именем сделать view в котором подменить это поле на вычисляемое


Как это может помочь при конструкции WHERE SUBSTRING(<nvarchar строка>,3,31) = <VALUE> ?
28 авг 14, 13:51    [16506872]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
NewBie123
я не могу менять структуру таблицы, вставив вычисляемое поле, а потом перенаправив запрос на него
Запрос не нужно переписывать. Главное чтобы выражение в запросе совпадало с выражением вычисляемого столбца - тогда оптимизатор сможет использовать индекс по этому столбцу.
28 авг 14, 13:54    [16506898]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
invm
Главное чтобы выражение в запросе совпадало с выражением вычисляемого столбца - тогда оптимизатор сможет использовать индекс по этому столбцу.


Если выражения совпадут, то да. Но я так понимаю 3,31 это тоже не фиксированные значения. Сколько там вариантов?
28 авг 14, 13:56    [16506915]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
NewBie123
Guest
a_voronin,

в таблице 128689 уникальных значений строки NVARCHAR(100), со статистикой я игрался, не особо помогло
28 авг 14, 13:59    [16506932]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
NewBie123
a_voronin,

в таблице 128689 уникальных значений строки NVARCHAR(100), со статистикой я игрался, не особо помогло


А сколько всего строк. Если всего >1000000 млн строк и 128689 уникальных, то можно положить их в отдельную таблицу и соединить JOIN. Покрыть всё вьюхой. Придётся сделать INSETED OF INSERT UPDATE DELETE триггеры, если есть такие операции на таблице.
28 авг 14, 14:03    [16506955]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
NewBie123
Guest
Столько же и строк в таблице , т.е. 128689
28 авг 14, 14:13    [16507027]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
NewBie123
Guest
Glory,

Да, формирует приложение, я нашел в нем очень много плохого кода
28 авг 14, 14:38    [16507191]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
Glory
Member

Откуда:
Сообщений: 104751
NewBie123
Glory,

Да, формирует приложение, я нашел в нем очень много плохого кода

Плохой код нельяз ускорить хорошим индексом.
28 авг 14, 14:40    [16507209]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
NewBie123
Guest
Glory, вот это доказывает мои слова, используется чуть ли не на каждое действие пользователя


CREATE SYNONYM [dbo].[lookup_locale]
FOR [eudf_lookup_default_locale];

-- Referenced Create Script
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
GO
CREATE FUNCTION eudf_lookup_default_locale ( @p_ProcedureName NVARCHAR(31),
@p_LanguageName VARCHAR(7),
@p_CountryCode VARCHAR(3) )
RETURNS VARCHAR(12)
-- WITH ENCRYPTION
AS
BEGIN

DECLARE @l_LocaleID VARCHAR(12)
SET @l_LocaleID = 'DEFAULT'

RETURN @l_LocaleID

END
28 авг 14, 14:46    [16507267]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Glory
Плохой код нельяз ускорить хорошим индексом.


Как знать, как знать.
Небольшой тест.

Создаём таблицу.
USE tempdb
GO
CREATE TABLE dbo.TestCalculatedColumns(
	name sysname NOT NULL,
	object_id int NOT NULL)
GO

Заполняем и создаём индекс.
INSERT dbo.TestCalculatedColumns(name, object_id)
SELECT name, OBJECT_ID FROM sys.objects
GO

CREATE NONCLUSTERED INDEX IX_TestCalculatedColumns ON dbo.TestCalculatedColumns
(name) INCLUDE ([object_id])
GO
Запускаем запрос.
SELECT name, object_id
FROM dbo.TestCalculatedColumns
WHERE substring(name,4,126) = N'allocunits'

Как и следовало ожидать, получаем сканирование по индексу.
  |--Index Scan(OBJECT:([tempdb].[dbo].[TestCalculatedColumns].[IX_TestCalculatedColumns]),  WHERE:(substring([tempdb].[dbo].[TestCalculatedColumns].[name],(4),(126))=N'allocunits'))

Переименовываем таблицу, добавляем вычисляемое поле, индекс на него, и создаём синоним.
EXEC sp_rename 'dbo.TestCalculatedColumns', 'TestCalculatedColumnsOld';
GO

ALTER TABLE dbo.TestCalculatedColumnsOld
	ADD substring_name AS (substring(name,4,126))
GO

CREATE NONCLUSTERED INDEX IX_TestCalculatedColumns2 ON dbo.TestCalculatedColumnsOld
(substring_name) INCLUDE (name,	[object_id])
GO

CREATE SYNONYM dbo.TestCalculatedColumns FOR dbo.TestCalculatedColumnsOld
GO

Опять запускаем наш запрос.
SELECT name, object_id
FROM dbo.TestCalculatedColumns
WHERE substring(name,4,126) = N'allocunits'


и получаем поиск по индексу.
  |--Index Seek(OBJECT:([tempdb].[dbo].[TestCalculatedColumnsOld].[IX_TestCalculatedColumns2]), SEEK:([tempdb].[dbo].[TestCalculatedColumnsOld].[substring_name]=N'allocunits') ORDERED FORWARD)

Так что если вы властны над базой, то многое можно исправить.
28 авг 14, 18:23    [16508944]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
Bator
Member

Откуда: Порт пяти морей
Сообщений: 439
после того как таблицу на вьюху замените
с приложением можно так поступить:
берем hex редактор, ищем где там
WHERE SUBSTRING(some,3,31) = value

меняем на что-то вроде
WHERE           some       = value

чтобы байты не сместились и все заработает, забегает
добавлять что-то сложнее, а убрать легко
28 авг 14, 23:59    [16510037]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
Jovanny
Так что если вы властны над базой, то многое можно исправить.
Писали уже, что так не получится, ведь наверняка 4,126 не константы.

Так что я согласен с
Jovanny
Glory
Плохой код нельзя ускорить хорошим индексом

хотя всегда нужно оставлять шанс для исключений.
29 авг 14, 01:01    [16510178]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
a_voronin
А сколько всего строк. Если всего >1000000 млн строк и 128689 уникальных, то можно положить их в отдельную таблицу и соединить JOIN. Покрыть всё вьюхой. Придётся сделать INSETED OF INSERT UPDATE DELETE триггеры, если есть такие операции на таблице.
Я не всегда понимаю ваши мотивы. То ли - мы не ищем легких путей, то ли - сделаю как можно сложнее, чтобы никто не разобрался и меня не смогли уволить.

Ваша отдельная таблица это будет по сути тот же индекс, но созданный вручную, который надо вручную же поддерживать с вытекающим геморроем. Работы дофига, а бенефитов ноль.
29 авг 14, 02:59    [16510267]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Jovanny
Переименовываем таблицу, добавляем вычисляемое поле, индекс на него, и создаём синоним.
Я что-то мысль потерял, а зачем нужен синоним?
29 авг 14, 03:02    [16510268]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Mind,

Это я типа показать, что можно вообще исходную таблицу подменить произвольной таблицей, даже размещённой в другой базе.
29 авг 14, 09:42    [16510727]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
NewBie123
Guest
Jovanny,

Нельзя ничего менять жесткого, в эту таблицу происходит вставка, а т.к. документации по программе - кот наплакал, то жестко изменять ничего не следует. А сидеть три дня с профайлером и отлавливать изменения по каждому чиху пользователя не хочется
29 авг 14, 14:16    [16512859]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
NewBie123
Jovanny,

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

Тогда следует сесть и сложить руки. Как же Вы хотите решить проблему, не внося изменений в базу? Волшебной кнопочки "Работать быстро" нет.
29 авг 14, 14:45    [16513078]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по "куску" строки  [new]
NewBie123
Guest
Есть кнопочка, вернее, команда, я хинт RECOMPILE использовал, все тип-топ стало
29 авг 14, 14:51    [16513137]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить