Daniel Jones Ответов: 1

Как быстро получить результат

У нас есть 2 крора данных и нам нужно 3 столбца в select на основе условия where используя like
Это занимает много времени.
Я использовал полнотекстовый поиск, но его ограничение мы можем применить только к одному столбцу
Один столбец * один индекс
Что я могу сделать, чтобы сделать это быстро
Есть идеи?

Что я уже пробовал:

Я использовал полнотекстовый поиск, но его ограничение мы можем применить только к одному колуму

Wendelius

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

Daniel Jones

Выберите DISTINCT TOP (@SearchLimit) fl_st_id в качестве [ключа],
LTRIM(RTRIM(fl_st_id))+' - ' + fl_st_name AS [Description] FROM FCLEGS WHERE fl_st_id like @SearchTer

Patrice T

Воспользуйся Улучшить вопрос чтобы обновить ваш вопрос.
Чтобы каждый мог обратить внимание на эту информацию.

Daniel Jones

fl_st_id некластеризованный расположен на первичном

1 Ответов

Рейтинг:
7

Wendelius

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

Во-первых, вы можете индексировать несколько столбцов в одной таблице с помощью полнотекстового индекса.

Другое дело,что при использовании полнотекстовых индексов используйте функцию CONTAINS.

И в-третьих, количество строк, удовлетворяющих условию, должно быть относительно небольшим по сравнению с общим количеством строк.

Рассмотрим следующий пример:

Создайте тестовую таблицу

CREATE TABLE FullTextTest (
  id int identity(1,1) NOT NULL,
  textcol1 varchar(max) NOT NULL,
  textcol2 varchar(max) NOT NULL
);

ALTER TABLE FullTextTest 
ADD CONSTRAINT pk_FullTextTest 
PRIMARY KEY (id);

Добавьте большой набор строк
INSERT INTO FullTextTest (textcol1, textcol2)
SELECT 
   'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam at elit enim. Mauris et sapien vitae nulla convallis maximus non sit amet nunc. Sed quis consequat eros, nec suscipit nulla. Suspendisse potenti. Duis a posuere dui. Donec sollicitudin eget enim sit amet hendrerit. Nullam neque nisl, sodales ac magna suscipit, iaculis interdum lectus. Integer vehicula egestas augue, finibus commodo urna feugiat et. Integer cursus, lorem ut convallis fringilla, dui elit posuere turpis, nec porta erat ligula quis tortor. Mauris imperdiet ipsum diam, sed eleifend elit blandit sit amet. Fusce eu porttitor elit. Cras tempor orci quis vestibulum tincidunt. Maecenas auctor metus eget diam pulvinar, eget commodo augue pellentesque. Integer congue vitae tellus ut sollicitudin. Nulla non augue ligula.' as data1,
   'Nulla ullamcorper, justo quis malesuada imperdiet, arcu metus consectetur diam, quis consectetur arcu tellus nec magna. Ut faucibus risus ut ex placerat, et scelerisque dui vehicula. Nullam in aliquet lorem. Quisque lobortis quam at dui bibendum gravida. Ut in sodales sapien, quis pretium neque. Mauris feugiat urna dolor, sit amet fringilla mi accumsan sit amet. Vivamus sit amet dictum quam, vel condimentum lorem. Cras elementum eu massa lobortis pharetra. Quisque fringilla volutpat mauris, sed feugiat velit accumsan sed. Nam venenatis nec justo eget consequat. Donec vitae lorem hendrerit nulla pulvinar eleifend. Curabitur ut odio posuere ipsum faucibus commodo sed a est. Quisque tincidunt augue nec mollis volutpat. Maecenas elementum tempus velit vitae condimentum. Integer fermentum mi sapien.' as data2
FROM sysobjects s1, sysobjects s2, sysobjects s3;

INSERT INTO FullTextTest (textcol1, textcol2)
SELECT 
   'Nulla ullamcorper, justo quis malesuada imperdiet, arcu metus consectetur diam, quis consectetur arcu tellus nec magna. Ut faucibus risus ut ex placerat, et scelerisque dui vehicula. Nullam in aliquet lorem. Quisque lobortis quam at dui bibendum gravida. Ut in sodales sapien, quis pretium neque. Mauris feugiat urna dolor, sit amet fringilla mi accumsan sit amet. Vivamus sit amet dictum quam, vel condimentum lorem. Cras elementum eu massa lobortis pharetra. Quisque fringilla volutpat mauris, sed feugiat velit accumsan sed. Nam venenatis nec justo eget consequat. Donec vitae lorem hendrerit nulla pulvinar eleifend. Curabitur ut odio posuere ipsum faucibus commodo sed a est. Quisque tincidunt augue nec mollis volutpat. Maecenas elementum tempus velit vitae condimentum. Integer fermentum mi sapien.' as data1,
   'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam at elit enim. Mauris et sapien vitae nulla convallis maximus non sit amet nunc. Sed quis consequat eros, nec suscipit nulla. Suspendisse potenti. Duis a posuere dui. Donec sollicitudin eget enim sit amet hendrerit. Nullam neque nisl, sodales ac magna suscipit, iaculis interdum lectus. Integer vehicula egestas augue, finibus commodo urna feugiat et. Integer cursus, lorem ut convallis fringilla, dui elit posuere turpis, nec porta erat ligula quis tortor. Mauris imperdiet ipsum diam, sed eleifend elit blandit sit amet. Fusce eu porttitor elit. Cras tempor orci quis vestibulum tincidunt. Maecenas auctor metus eget diam pulvinar, eget commodo augue pellentesque. Integer congue vitae tellus ut sollicitudin. Nulla non augue ligula.' as data2
FROM sysobjects s1, sysobjects s2, sysobjects s3;

Добавьте небольшой набор строк для поиска
INSERT INTO FullTextTest (textcol1, textcol2)
SELECT 
   'Fusce et ligula vel dolor maximus maximus non eu urna. Aenean condimentum sem diam. Morbi cursus metus sed nulla ornare blandit. Fusce ut faucibus quam. Etiam fringilla convallis mi, hendrerit hendrerit sapien volutpat quis. Suspendisse tempor viverra congue. Sed eu velit maximus, maximus sapien id, fermentum felis. Vestibulum rutrum leo felis, ut sagittis risus efficitur sit amet. Phasellus tempor tellus vel placerat scelerisque. In hac habitasse platea dictumst. Nunc tincidunt elementum justo, vel dapibus felis pellentesque non. Nulla mattis lorem eget metus dictum, sed condimentum ipsum sodales. Nunc pretium efficitur suscipit. Proin facilisis molestie erat nec gravida. Suspendisse potenti. Ut nunc nisi, vulputate venenatis enim sit amet, consequat elementum tortor.' as data1,
   'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam at elit enim. Mauris et sapien vitae nulla convallis maximus non sit amet nunc. Sed quis consequat eros, nec suscipit nulla. Suspendisse potenti. Duis a posuere dui. Donec sollicitudin eget enim sit amet hendrerit. Nullam neque nisl, sodales ac magna suscipit, iaculis interdum lectus. Integer vehicula egestas augue, finibus commodo urna feugiat et. Integer cursus, lorem ut convallis fringilla, dui elit posuere turpis, nec porta erat ligula quis tortor. Mauris imperdiet ipsum diam, sed eleifend elit blandit sit amet. Fusce eu porttitor elit. Cras tempor orci quis vestibulum tincidunt. Maecenas auctor metus eget diam pulvinar, eget commodo augue pellentesque. Integer congue vitae tellus ut sollicitudin. Nulla non augue ligula.' as data2
FROM sysobjects s1;
-- 119 additional rows added

Обновите статистику и посмотрите общий счет
UPDATE STATISTICS FullTextTest;

SELECT COUNT(*) FROM FullTextTest
-- total 3'370'437 rows

Создание полнотекстовых индексов
CREATE FULLTEXT CATALOG FullTextTestCatalog AS DEFAULT;  

CREATE FULLTEXT INDEX 
ON FullTextTest (textcol1, textcol2)   
KEY INDEX pk_FullTextTest;  

SELECT
   c.Name,
   CASE FULLTEXTCATALOGPROPERTY(c.name,'PopulateStatus')
	   WHEN 0 THEN 'Idle'
	   WHEN 1 THEN 'Full population in progress'
	   WHEN 2 THEN 'Paused'
	   WHEN 3 THEN 'Throttled'
	   WHEN 4 THEN 'Recovering'
	   WHEN 5 THEN 'Shutdown'
	   WHEN 6 THEN 'Incremental population in progress'
	   WHEN 7 THEN 'Building index'
	   WHEN 8 THEN 'Disk is full. Paused.'
	   WHEN 9 THEN 'Change tracking'
	END AS Status
FROM sys.fulltext_catalogs AS c

-- WAIT UNTIL POPULATION FINISHED
-- since this is initial population
-- this takes awhile, like hours

Тест как запрос
-- LIKE
BEGIN
   DECLARE @word varchar(100);
   SET @word = '%rutrum%';

   SELECT ftt.* 
   FROM FullTextTest ftt
   WHERE ftt.textcol1 LIKE @word
   OR    ftt.textcol2 LIKE @word;
END
-- Result is 119 rows
---- time 1 hour 8 minutes
--Table 'FullTextTest'. Scan count 1, logical reads 676596, physical reads 3, read-ahead reads 676591, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times:
--   CPU time = 3162688 ms,  elapsed time = 4079922 ms.

Таким образом, для завершения запроса требуется чуть больше 1 часа, а размер результирующего набора составляет 119 строк.

Теперь тест содержит запрос
-- CONTAINS
BEGIN
   DECLARE @word varchar(100);
   SET @word = 'rutrum';

   SELECT ftt.* 
   FROM FullTextTest ftt
   WHERE CONTAINS(ftt.textcol1, @word)
   OR    CONTAINS(ftt.textcol2, @word);
END

-- Result is 119 rows
-- time 2 seconds
--Table 'FullTextTest'. Scan count 0, logical reads 737, physical reads 1, read-ahead reads 27, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times:
--   CPU time = 0 ms,  elapsed time = 1739 ms.

Гораздо лучше, 2 секунды, тот же результат.

Надеюсь, это прояснит ситуацию.