Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Оптимизировать запрос  [new]
Demer
Member

Откуда:
Сообщений: 82
Здравствуйте!

Есть такая схема данных (опишу только необходимые поля):
Картинка с другого сайта.
В tbl_W два поля, которые обращаются к tbl_L : LID и OLID
Все таблицы большие по объему (1 млн строк и больше)
Имеется куча индексов (не мной проставленные)

Необходимо найти строки (первые 15 хотя бы) удовлетворяющие условию:
ActID like '%' + @val + '%'
OR Name like '%' + @val + '%'
OR Code like '%' + @val + '%'


Результат должен выглядеть так:
AID,   'Name',   'Code1,code2,code3'


Я написал запрос, который выполняется 3 сек., а надо меньше 1
Посмотрите - можно как-то по другому составить?

SELECT  TOP 15 a.AID, a.Name, l.Code
FROM tbl_A a
	OUTER APPLY (
		Select replace(replace(replace( (
					Select loc.Code as t
					 From tbl_WA wa
						join tbl_W w on w.WID = wa.WID	
						join tbl_L l on l.LID in (w.LID, w.OLID) 
					 Where wa.AID = a.AID
					 Group By l.Code
					 For xml PATH('')
					), '</t><t>',', '), '</t>',''), '<t>','') as Code
	) l	

WHERE	a.AID like '%'+@Value+'%'
		OR a.Name like '%'+@Value+'%'
		OR l.Code like '%'+@Value+'%'
26 фев 16, 15:08    [18867852]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Будьте добры план выполнения в формате sqlplan. Вдруг у Вас со статистикой траблы, а быть может что другое.
И вообще то такая фильтрация в FullIndexScan приводит...
26 фев 16, 15:23    [18867960]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
Demer
Member

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

Для примера названия таблиц и полей были изменнены

К сообщению приложен файл (11.sqlplan - 88Kb) cкачать
26 фев 16, 15:35    [18868049]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Demer,

Вот это
автор
a.AID like '%'+@Value+'%'
OR a.Name like '%'+@Value+'%'
OR l.Code like '%'+@Value+'%'

порождает только fullscan и ускорять тут нечего.
26 фев 16, 15:37    [18868060]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Demer
Для примера названия таблиц и полей были изменнены

Честно... не сильно хочется догадываться что на плане отвечает за tbl_WA и tbl_A. Проблема основная в фильтрации... FTS можно предложить. Ну и запрос с человеческими именами таблиц :)
26 фев 16, 15:40    [18868093]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
Demer
Member

Откуда:
Сообщений: 82
Ну в принципе я понял.

1. Запрос не оптимизировать - составлен нормально
2. Использовать FTS

Пошёл читать про FTS
26 фев 16, 15:48    [18868149]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Demer
1. Запрос не оптимизировать - составлен нормально

Кто сказал что нельзя? Предоставьте нормальный текст запроса. Без шифрования :)
26 фев 16, 16:19    [18868381]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Пока ждем запрос... Вот вариант правильной конкатенации. а не того что Вы REPLACE-ом делали:

SELECT STUFF((
	SELECT DISTINCT ', ' + l.Code
    FROM tbl_WA wa
    JOIN tbl_W w ON w.WID = wa.WID
    JOIN tbl_L l ON l.LID IN (w.LID, w.OLID)
    WHERE wa.AID = a.AID
	FOR XML PATH('')), 1, 2, '')

Почитать можно тут: String aggregation in the SQL Server world
26 фев 16, 16:22    [18868401]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
Demer
Ну в принципе я понял.

1. Запрос не оптимизировать - составлен нормально
2. Использовать FTS

Пошёл читать про FTS



Если вы будете коды в строку соединять, а потом по ней LIKE -ом лазить , то далеко не уедете. А FTS тут вообще как помочь должен?

WHERE	a.AID like '%'+@Value+'%'
		OR a.Name like '%'+@Value+'%'
		OR l.Code like '%'+@Value+'%'


У вас коды должны быть подстрокой или они совпадать должны? Такое впечатление, что вы не понимаете чего хотите? Задача в чем состоит?
26 фев 16, 17:43    [18868926]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
Demer
Member

Откуда:
Сообщений: 82
AlanDenton
Предоставьте нормальный текст запроса. Без шифрования :)
DECLARE @Branch varchar(max) = '1642,1643',
        @Template varchar(max) = '630',
		@Value varchar(250) = '890'


--	dbo.fn_IntListToTable - функция, распарсивает строку в таблицу с колонкой INT
DECLARE	@tblBranch TABLE (BranchID int not null primary key)
INSERT INTO @tblBranch (BranchID)
	SELECT z.number FROM dbo.fn_IntListToTable(REPLACE(@Branch, ',', '.')) z

DECLARE	@tblTemplate TABLE (TemplateID int not null primary key)
INSERT INTO @tblTemplate (TemplateID)
	SELECT z.number FROM dbo.fn_IntListToTable(REPLACE(@Template, ',', '.')) z
	
	
SELECT  TOP 15 bsa.ActivityID, bsa.Name, l.Code
FROM Planning.BSActivity bsa
	OUTER APPLY (
		Select replace(replace(replace( (
					Select loc.Code as t
					 From Planning.BSActivityWorkObjectsLink wol 
						join Planning.WorkObject wo on wo.WorkObjectID = wol.WorkObjectID		
                        join Planning.Location loc on loc.LocationID in (wo.LocationID, wo.OtherLocationID) 
					 Where wol.ActivityID = bsa.ActivityID
                     Group By loc.Code
					 For xml PATH('')
					), '</t><t>',', '), '</t>',''), '<t>','') as Code
	) l
	JOIN @tblBranch b ON b.BranchID = bsa.BranchID
	JOIN @tblTemplate t ON t.TemplateID = bsa.TemplateID
WHERE  (bsa.ActivityID like '%'+@Value+'%'
		OR bsa.Name like '%'+@Value+'%'
		OR l.Code like '%'+@Value+'%'
		)


AlanDenton
Вот вариант правильной конкатенации.
Спасибо.

a_voronin
У вас коды должны быть подстрокой или они совпадать должны? Такое впечатление, что вы не понимаете чего хотите? Задача в чем состоит?
Что я хочу я понимаю, а вот как это сделать правильно, вот тут мне кажется я не понимаю :-)

Задача такая - пишу контрол выпадающего списка с тремя колонками: ID, Название, Код
Кодов может быть сколько угодно и они могут повторятся, поэтому выводятся все через запятую и без повторов.

Когда пользователь вводит в контрол какое-то значение, то выпадающий список должен тут же выдавать подходящие строки. Поиск должен проходить по всем трем полям.
29 фев 16, 10:42    [18876004]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
DECLARE  @Branch VARCHAR(MAX) = '1642,1643'
       , @Template VARCHAR(MAX) = '630'
       , @Value VARCHAR(250) = '890'

DECLARE @tblBranch TABLE (BranchID INT PRIMARY KEY)
INSERT INTO @tblBranch
SELECT number
FROM dbo.fn_IntListToTable(REPLACE(@Branch, ',', '.'))

DECLARE @tblTemplate TABLE (TemplateID INT PRIMARY KEY)
INSERT INTO @tblTemplate
SELECT number
FROM dbo.fn_IntListToTable(REPLACE(@Template, ',', '.'))

SELECT TOP(15) bsa.ActivityID, bsa.name, l.Code
FROM Planning.BSActivity bsa
OUTER APPLY (
    SELECT Code = STUFF((
        SELECT DISTINCT ', ' + loc.Code
        FROM Planning.BSActivityWorkObjectsLink wol
        JOIN Planning.WorkObject wo ON wo.WorkObjectID = wol.WorkObjectID
        JOIN Planning.Location loc ON loc.LocationID IN (wo.LocationID, wo.OtherLocationID)
        WHERE wol.ActivityID = bsa.ActivityID
	    FOR XML PATH('')), 1, 2, '')
) l
WHERE (
            bsa.ActivityID LIKE '%' + @Value + '%'
        OR
            bsa.name LIKE '%' + @Value + '%'
        OR
            l.Code LIKE '%' + @Value + '%'
    )
    AND bsa.BranchID IN (SELECT t.BranchID FROM @tblBranch t)
    AND bsa.TemplateID IN (SELECT t.TemplateID FROM @tblTemplate t)
OPTION(RECOMPILE)

Как мне показалось основная проблема в табличный переменных. OPTION(RECOMPILE) должен решить проблему...
29 фев 16, 11:26    [18876218]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Забыл еще про один нюанс упомянуть. Табличные переменные и параллельные планы не сильно хорошо дружат, поэтому как вариант можно так написать:

DECLARE  @Branch VARCHAR(MAX) = '1642,1643'
       , @Template VARCHAR(MAX) = '630'
       , @Value VARCHAR(250) = '890'

IF OBJECT_ID('tempdb.dbo.#b') IS NOT NULL
	DROP TABLE #b
CREATE TABLE #b (BranchID INT PRIMARY KEY)

INSERT INTO #b
SELECT number
FROM dbo.fn_IntListToTable(REPLACE(@Branch, ',', '.'))

IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL
	DROP TABLE #t
CREATE TABLE #t (TemplateID INT PRIMARY KEY)

INSERT INTO #t
SELECT number
FROM dbo.fn_IntListToTable(REPLACE(@Template, ',', '.'))

SELECT TOP(15) bsa.ActivityID, bsa.name, l.Code
FROM Planning.BSActivity bsa
OUTER APPLY (
    SELECT Code = STUFF((
	    SELECT DISTINCT ', ' + loc.Code
        FROM Planning.BSActivityWorkObjectsLink wol
        JOIN Planning.WorkObject wo ON wo.WorkObjectID = wol.WorkObjectID
        JOIN Planning.Location loc ON loc.LocationID IN (wo.LocationID, wo.OtherLocationID)
        WHERE wol.ActivityID = bsa.ActivityID
	    FOR XML PATH('')), 1, 2, '')
) l
WHERE (
            bsa.ActivityID LIKE '%' + @Value + '%'
        OR
            bsa.name LIKE '%' + @Value + '%'
        OR
            l.Code LIKE '%' + @Value + '%'
    )
    AND bsa.BranchID IN (SELECT t.BranchID FROM #b t)
    AND bsa.TemplateID IN (SELECT t.TemplateID FROM #t t)
29 фев 16, 11:38    [18876277]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
Demer
Member

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

Разница во времени не заметна - выполняется столько же сколько и мой запрос (с заменой replace на STUFF)

=(
29 фев 16, 12:01    [18876414]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
План выполнения для запроса там где OPTION(RECOMPILE) просьба предоставить... sqlplan
29 фев 16, 12:02    [18876421]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
Demer
Member

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

Это, видимо, из-за имеющихся таблиц

К сообщению приложен файл (22.sqlplan - 83Kb) cкачать
29 фев 16, 12:16    [18876522]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
В @Value могут быть несколько значений через запятую? Или только одно?
29 фев 16, 12:39    [18876646]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
Demer
Member

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

Только одно
29 фев 16, 12:45    [18876687]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
1. Тогда зачем делать конкатенацию? Если у Вас в поле кодов ожидается все одна строка?
2. ActivityID у Вас INT зачем делать поиск по нему?
29 фев 16, 12:53    [18876761]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
Demer
Member

Откуда:
Сообщений: 82
Demer
Задача такая - пишу контрол выпадающего списка с тремя колонками: ID, Название, Код
Кодов может быть сколько угодно и они могут повторятся, поэтому выводятся все через запятую и без повторов.

Когда пользователь вводит в контрол какое-то значение, то выпадающий список должен тут же выдавать подходящие строки. Поиск должен проходить по всем трем полям.
29 фев 16, 12:59    [18876821]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
С учетом, того что Вы хотите получить... быстрым оно априори не будет. Можно, конечно, поизвращаться над кодом:

CREATE NONCLUSTERED INDEX ix ON Planning.BSActivity (BranchID, TemplateID)
GO

DECLARE  @Branch VARCHAR(MAX) = '1642,1643'
       , @Template VARCHAR(MAX) = '630'
       , @Value VARCHAR(250) = '890'

DECLARE @tblBranch TABLE (BranchID INT PRIMARY KEY)
INSERT INTO @tblBranch
SELECT number
FROM dbo.fn_IntListToTable(REPLACE(@Branch, ',', '.'))

DECLARE @tblTemplate TABLE (TemplateID INT PRIMARY KEY)
INSERT INTO @tblTemplate
SELECT number
FROM dbo.fn_IntListToTable(REPLACE(@Template, ',', '.'))

DECLARE @t TABLE (ActivityID INT PRIMARY KEY)
INSERT INTO @t
SELECT a.ActivityID
FROM Planning.BSActivity a
WHERE a.BranchID IN (SELECT t.BranchID FROM @tblBranch t)
    AND a.TemplateID IN (SELECT t.TemplateID FROM @tblTemplate t)

IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL
	DROP TABLE #t

SELECT wol.ActivityID, loc.Code
INTO #t
FROM Planning.BSActivityWorkObjectsLink wol
JOIN Planning.WorkObject wo ON wo.WorkObjectID = wol.WorkObjectID
JOIN Planning.Location loc ON loc.LocationID IN (wo.LocationID, wo.OtherLocationID)
WHERE wol.ActivityID IN (SELECT * FROM @t)
GROUP BY wol.ActivityID, loc.Code
HAVING COUNT(CASE WHEN loc.Code LIKE '%' + @Value + '%' THEN 1 END) = 1
OPTION(RECOMPILE)

SELECT TOP(15) bsa.ActivityID, bsa.name, l.Code
FROM Planning.BSActivity bsa
OUTER APPLY (
    SELECT Code = STUFF((
        SELECT ', ' + tt.Code
        FROM #t tt
        WHERE tt.ActivityID = bsa.ActivityID
	    FOR XML PATH('')), 1, 2, '')
) l
WHERE (
            bsa.ActivityID LIKE '%' + @Value + '%'
        OR
            bsa.name LIKE '%' + @Value + '%'
        OR
            l.Code IS NOT NULL
    )
    AND bsa.ActivityID IN (SELECT * FROM @t)
OPTION(RECOMPILE)

Поможет или нет... Тут ванговать бесполезно.
29 фев 16, 13:03    [18876855]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить